Re: Making a Variable from different tables with Matching Db fields?

On Wed, Jan 28, 2009 at 3:18 PM, Terion Miller <webdev.terion@gmail.com> wrote: > Not sure if I'm wording this right, what I am trying to do is look in two > tables, match the ID to use to pull information.... > > Here's my code but it's not right, although it is picking up the user from > the session, I will also post what my variable debugging lists: > > $query = "SELECT admin.AdminID, workorders.AdminID FROM admin, > workorders WHERE admin.UserName = '".$_SESSION['user']."' > "; > $result = mysql_query ($query); > $row = mysql_fetch_assoc ($result); > > echo $row['AdminID']; > > if ($row['ViewMyOrders'] == "NO") { > header ("Location: Welcome.php?AdminID=$AdminID&msg=Sorry, you do > not have access to that page."); > } > > *Also tried this to pull just this persons orders:* > > $sql = "SELECT workorders.WorkOrderID , workorders.AdminID, > admin.AdminID FROM workorders, admin WHERE workorders.AdminID = > admin.AdminID "; > $result = mysql_query ($sql); > > Thanks for looking, t. > Your first version gives you a Cartesian product containing more rows than you are expecting. (All rows from the workorders table joined with the row in the admin table where the username matches.) The second version returns all rows where the AdminIDs match, but for all users. You need to combine them: $sql = "SELECT workorders.WorkOrderID , workorders.AdminID, admin.AdminID FROM workorders, admin WHERE workorders.AdminID = admin.AdminID AND admin.UserName = '" . mysql_real_escape_string($username) . "'"; Although I believe the preferred syntax (at least, I think it's the preferred) is $sql = "SELECT workorders.WorkOrderID , workorders.AdminID, admin.AdminID FROM workorders INNER JOIN admin ON workorders.AdminID = admin.AdminID WHERE admin.UserName = '" . mysql_real_escape_string($username) . "'"; Andrew