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

Terion Miller wrote: > Well I'm stuck I have the AdminID but now I can't seem to use it to pull > workorders with that AdminID . I couldn't get your block to work Andrew :( > > I think I'm just not using it right now that I have it...lol > > > On Wed, Jan 28, 2009 at 2:26 PM, Andrew Ballard <aballard@gmail.com> wrote: > >> 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 >> > I think I see what you're trying to do: $query = "SELECT AdminID FROM admin WHERE UserName = '" . mysql_real_escape_string($_SESSION['user']) . "'"; $result = mysql_query($query); $admins = mysql_fetch_assoc($result); $query = "SELECT * FROM workorders WHERE AdminID = '" . $admins['AdminID'] . "'"; $result = mysql_query($query); $workorders = mysql_fetch_assoc($result); -- Thanks! -Shawn http://www.spidean.com