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

On Wed, Jan 28, 2009 at 3:43 PM, Shawn McKenzie <nospam@mckenzies.net>wrote: > 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 > I have to get only the work orders associated with the adminID, I get the pages but no orders. and if I print my variables I am grabbing the right adminID but it's not then going and grabbing the work orders with it. I'm not up on the correct phrasing, been doing this about 2 months.