Re: Making a Variable from different tables with Matching Dbfields?

Shawn McKenzie wrote: > 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); > Well maybe not. Has anyone noticed that all the proposed selects including the OPs are only returning AdminID and WorkOrderID? But in the OPs code he's trying to use $row['ViewMyOrders']! -- Thanks! -Shawn http://www.spidean.com