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

On Wed, Jan 28, 2009 at 4:00 PM, Shawn McKenzie <nospam@mckenzies.net>wrote: > Terion Miller wrote: > > 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. > > > Well, try what I posted (needs some error checking). Where does > ViewMyOrders come from? admin table? It would be even easier if you > put the AdminID in the SESSION also :-) > > There also seems to be some design flaws. Why query the database for > orders if the user is not allowed to view their orders? > > -- > Thanks! > -Shawn > http://www.spidean.com > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > No the user is allowed to view them, or that is what I'm trying to do exactly , now I have it returning some orders but they don't belong to the correct AdminID , I'm getting closer, I appreciate everyone's help in the right direction!! Terion