Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Hi Guys! Well I tried the INNER JOIN and still can not get it to echo the AdminID so I know it isn't working, (what kind of things should I think about that could make it not work) so far the only query that did work and return the AdminID was my original I believe it was referred to as hosed query, yet then it was explained to me that query while picking up the AdminID was then returning all the rows from workorders anyways because I needed the INNER JOIN, so since that isn't working, I'm thinking my best and fastest route ( I have until Monday on this project and this is just one bit of it OUCH) is to use my hosed query then somehow use the resulting AdminID to fetch the orders from the workorders table, question is , would that be a sub query, or do I just make the query results for AdminID a variable to use in another query? Thanks guys and ps. I'm a she not a he, funny that coders are primarily always assumed to be guys...lol Facebook Me: http://www.facebook.com/profile.php?id=1542024891ref=name Terion On Wed, Jan 28, 2009 at 7:39 PM, Shawn McKenzie nos...@mckenzies.netwrote: Chris wrote: The main problem is that you've never explained what you want to get from the query. The replies have used your code as an example and I'm pretty sure that's not what you want. Unless I totally mis-understand what you want, you have 2 options: 1. Use the 2 queries that I gave you in a previous post. 2. Use a subquery: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); 3 - fix the join ;) Yes, however, I'm going out on a limb here because we don't really know what he wants - he is only getting admin.AdminID, workorders.AdminID returned in all of the queries I've seen. I'm assuming that he wants some of the workorder details. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Terion Miller wrote: Hi Guys! Well I tried the INNER JOIN and still can not get it to echo the AdminID so I know it isn't working, (what kind of things should I think about that could make it not work) so far the only query that did work and return the AdminID was my original I believe it was referred to as hosed query, yet then it was explained to me that query while picking up the AdminID was then returning all the rows from workorders anyways because I needed the INNER JOIN, so since that isn't working, I'm thinking my best and fastest route ( I have until Monday on this project and this is just one bit of it OUCH) is to use my hosed query then somehow use the resulting AdminID to fetch the orders from the workorders table, question is , would that be a sub query, or do I just make the query results for AdminID a variable to use in another query? Thanks guys and ps. I'm a she not a he, funny that coders are primarily always assumed to be guys...lol Facebook Me: http://www.facebook.com/profile.php?id=1542024891ref=name http://www.facebook.com/profile.php?id=1542024891ref=name So the subquery that I gave you doesn't work? Run it and then do a print_r($row); and post what you get. -Shawn Terion On Wed, Jan 28, 2009 at 7:39 PM, Shawn McKenzie nos...@mckenzies.net mailto:nos...@mckenzies.net wrote: Chris wrote: The main problem is that you've never explained what you want to get from the query. The replies have used your code as an example and I'm pretty sure that's not what you want. Unless I totally mis-understand what you want, you have 2 options: 1. Use the 2 queries that I gave you in a previous post. 2. Use a subquery: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); 3 - fix the join ;) Yes, however, I'm going out on a limb here because we don't really know what he wants - he is only getting admin.AdminID, workorders.AdminID returned in all of the queries I've seen. I'm assuming that he wants some of the workorder details. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Hi Again Here is the query and code I tried: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); $result2 = mysql_query ($sql); $row2 = mysql_fetch_assoc ($result2); $printrow = print_r($row2); Here is my print variables--- Nothing printed with the print_r: $sqlSELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = 'tmiller')$result2Resource id #6$row2$printrow1 So the subquery that I gave you doesn't work? Run it and then do a print_r($row); and post what you get. -Shawn Terion On Wed, Jan 28, 2009 at 7:39 PM, Shawn McKenzie nos...@mckenzies.net mailto:nos...@mckenzies.net wrote: Chris wrote: The main problem is that you've never explained what you want to get from the query. The replies have used your code as an example and I'm pretty sure that's not what you want. Unless I totally mis-understand what you want, you have 2 options: 1. Use the 2 queries that I gave you in a previous post. 2. Use a subquery: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); 3 - fix the join ;) Yes, however, I'm going out on a limb here because we don't really know what he wants - he is only getting admin.AdminID, workorders.AdminID returned in all of the queries I've seen. I'm assuming that he wants some of the workorder details. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Terion Miller wrote: Hi Again Here is the query and code I tried: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); $result2 = mysql_query ($sql); $row2 = mysql_fetch_assoc ($result2); $printrow = print_r($row2); Here is my print variables--- Nothing printed with the print_r: $sql SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = 'tmiller') $result2 Resource id #6 $row2 $printrow 1 O.K., easy: 1. There is no UserName in admin that = $_SESSION['user'] 2. Or, the AdminID that is returned for $_SESSION['user'] is not present in workorders That's why I suggested the 2 query approach the first time so that you could echo out the AdminID after the first query and then go into the db and make sure it exists in workorders. Or, if none were returned, then echo out $_SESSION['user'] and make sure it exists in admin. -Shawn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Well upon looking it was number 2, I had no orders in the workorder table, but here is an oddity I wonder if someone can explain if I run this simple query: $query = SELECT * FROM admin, workorders WHERE admin.UserName = '.$_SESSION['user'].' ; It returns adminID = 7 (my number is 20) username= tmiller (this is correct) it's the only query I can get to return anything but it's the wrong adminID can someone explain what can be making that happen On Thu, Jan 29, 2009 at 2:48 PM, Shawn McKenzie nos...@mckenzies.netwrote: Terion Miller wrote: Hi Again Here is the query and code I tried: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); $result2 = mysql_query ($sql); $row2 = mysql_fetch_assoc ($result2); $printrow = print_r($row2); Here is my print variables--- Nothing printed with the print_r: $sql SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = 'tmiller') $result2 Resource id #6 $row2 $printrow 1 O.K., easy: 1. There is no UserName in admin that = $_SESSION['user'] 2. Or, the AdminID that is returned for $_SESSION['user'] is not present in workorders That's why I suggested the 2 query approach the first time so that you could echo out the AdminID after the first query and then go into the db and make sure it exists in workorders. Or, if none were returned, then echo out $_SESSION['user'] and make sure it exists in admin. -Shawn
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Terion Miller wrote: Well upon looking it was number 2, I had no orders in the workorder table, but here is an oddity I wonder if someone can explain if I run this simple query: $query = SELECT * FROM admin, workorders WHERE admin.UserName = '.$_SESSION['user'].' ; It returns adminID = 7 (my number is 20) username= tmiller (this is correct) it's the only query I can get to return anything but it's the wrong adminID can someone explain what can be making that happen You cannot return related values from 2 tables without joining the tables in the query which we've shown is not going to work because you need to get AdminID from admin before you can join it to workorders. What your query says is, give me all fields for all rows from admin, where UserName = the session user, and give me the same number of rows from workorders arbitrarily or just the first however many rows. I would be willing to bet that the first record in workorders has AdminID = 7. This works, obviously: $query = SELECT * FROM admin WHERE admin.UserName = '.$_SESSION['user'].' ; -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Shawn McKenzie wrote: Terion Miller wrote: Well upon looking it was number 2, I had no orders in the workorder table, but here is an oddity I wonder if someone can explain if I run this simple query: $query = SELECT * FROM admin, workorders WHERE admin.UserName = '.$_SESSION['user'].' ; It returns adminID = 7 (my number is 20) username= tmiller (this is correct) it's the only query I can get to return anything but it's the wrong adminID can someone explain what can be making that happen You cannot return related values from 2 tables without joining the tables in the query which we've shown is not going to work because you need to get AdminID from admin before you can join it to workorders. What your query says is, give me all fields for all rows from admin, where UserName = the session user, and give me the same number of rows from workorders arbitrarily or just the first however many rows. I would be willing to bet that the first record in workorders has AdminID = 7. This works, obviously: $query = SELECT * FROM admin WHERE admin.UserName = '.$_SESSION['user'].' ; I also bet that now that you know there are no valid records for your query in the workorders table, that the join queries that others have provided will work great! -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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 aball...@gmail.com wrote:
On Wed, Jan 28, 2009 at 3:18 PM, Terion Miller webdev.ter...@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=$AdminIDmsg=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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
On Wed, Jan 28, 2009 at 3:43 PM, Shawn McKenzie nos...@mckenzies.netwrote:
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 aball...@gmail.com
wrote:
On Wed, Jan 28, 2009 at 3:18 PM, Terion Miller
webdev.ter...@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=$AdminIDmsg=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.
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Terion Miller wrote:
On Wed, Jan 28, 2009 at 3:43 PM, Shawn McKenzie nos...@mckenzies.netwrote:
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 aball...@gmail.com
wrote:
On Wed, Jan 28, 2009 at 3:18 PM, Terion Miller
webdev.ter...@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=$AdminIDmsg=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
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
On Wed, Jan 28, 2009 at 4:00 PM, Shawn McKenzie nos...@mckenzies.netwrote:
Terion Miller wrote:
On Wed, Jan 28, 2009 at 3:43 PM, Shawn McKenzie nos...@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 aball...@gmail.com
wrote:
On Wed, Jan 28, 2009 at 3:18 PM, Terion Miller
webdev.ter...@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=$AdminIDmsg=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
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
On Wed, Jan 28, 2009 at 4:12 PM, Terion Miller webdev.ter...@gmail.comwrote:
On Wed, Jan 28, 2009 at 4:00 PM, Shawn McKenzie nos...@mckenzies.netwrote:
Terion Miller wrote:
On Wed, Jan 28, 2009 at 3:43 PM, Shawn McKenzie nos...@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 aball...@gmail.com
wrote:
On Wed, Jan 28, 2009 at 3:18 PM, Terion Miller
webdev.ter...@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=$AdminIDmsg=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
Here are my variables when I reveal them, I am picking up the right adminID
I can't figure out why it's returning random orders though:
$querySELECT admin.AdminID, workorders.AdminID FROM admin, workorders WHERE
admin.UserName = 'tmiller' $resultResource id #5$rowkeyvalue[WorkOrderID]
44[AdminID]7$SortByWorkOrderID DESC$Page2$PerPage30$StartPage30
$sqlSELECT workorders.WorkOrderID, workorders.AdminID, admin.AdminID FROM
workorders, admin WHERE workorders.AdminID = admin.AdminID $Total3
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Here are my variables when I reveal them, I am picking up the right adminID I can't figure out why it's returning random orders though: $querySELECT admin.AdminID, workorders.AdminID FROM admin, workorders WHERE admin.UserName = 'tmiller' $result Resource id #5 $row key value [WorkOrderID] 44 [AdminID] 7 $SortBy WorkOrderID DESC $Page 2 $PerPage 30 $StartPage30 $sql SELECT workorders.WorkOrderID, workorders.AdminID, admin.AdminID FROM workorders, admin WHERE workorders.AdminID = admin.AdminID $Total3 Because your queries are hosed. You want to populate $row with what? If you want all the fields in workorders, then this works great: SELECT * FROM workorders WHERE AdminID = 7 -Shawn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Here are my variables when I reveal them, I am picking up the right adminID I can't figure out why it's returning random orders though: $querySELECT admin.AdminID, workorders.AdminID FROM admin, workorders WHERE admin.UserName = 'tmiller' Please please please trim your posts to relevant stuff. I had to go through 4 pages of previous attempts to try and find what you posted. Trim it down to what you need to post. It's picking random stuff because you're not joining the tables properly. You have: select admin.AdminID, workorers.AdminID from admin, workorders WHERE admin.userName='tmiller'; You haven't told the db how to join the two tables together, so it's doing a cross or cartesian join (useful in some cases, but not here). What that means is for each row in 'admin', it will show every row in 'workorders' and vice versa. What you want is to only show rows that match up: select admin.AdminID, workorers.AdminID from admin inner join workorders using (AdminID) WHERE admin.userName='tmiller'; which means for every row in admin, make sure there is a matching row (based on the adminid) in workorders. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
I'm not sure what you mean by trim the posts, please explain so I can spare folks from redundant text. Your post made perfect sense to me about the INNER JOIN , I looked it up but it is not returning the AdminID, maybe my syntax is wrong? $query = SELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders ON AdminID(admin, workorders) WHERE admin.UserName = '.$_SESSION['user'].' ; $result = mysql_query ($query); $row = mysql_fetch_assoc ($result); echo $row['AdminID']; thanks again!! terion On Wed, Jan 28, 2009 at 4:28 PM, Chris dmag...@gmail.com wrote: Here are my variables when I reveal them, I am picking up the right adminID I can't figure out why it's returning random orders though: $querySELECT admin.AdminID, workorders.AdminID FROM admin, workorders WHERE admin.UserName = 'tmiller' Please please please trim your posts to relevant stuff. I had to go through 4 pages of previous attempts to try and find what you posted. Trim it down to what you need to post. It's picking random stuff because you're not joining the tables properly. You have: select admin.AdminID, workorers.AdminID from admin, workorders WHERE admin.userName='tmiller'; You haven't told the db how to join the two tables together, so it's doing a cross or cartesian join (useful in some cases, but not here). What that means is for each row in 'admin', it will show every row in 'workorders' and vice versa. What you want is to only show rows that match up: select admin.AdminID, workorers.AdminID from admin inner join workorders using (AdminID) WHERE admin.userName='tmiller'; which means for every row in admin, make sure there is a matching row (based on the adminid) in workorders. -- Postgresql php tutorials http://www.designmagick.com/
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Terion Miller wrote: I'm not sure what you mean by trim the posts, please explain so I can spare folks from redundant text. Before you post, remove any text that you're not referencing (in this case I removed my suggestion). Your post made perfect sense to me about the INNER JOIN , I looked it up but it is not returning the AdminID, maybe my syntax is wrong? $query = SELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders ON AdminID(admin, workorders) WHERE admin.UserName = '.$_SESSION['user'].' ; The syntax is wrong. inner join workorders using (adminid) ^^ only works if both tables are using the same field name, the db will expand it into the syntax below. Here you just need to specify the fieldname to join on (ie the common one). or inner join workorders on (admin.adminID=workorders.adminID) ^^ if the field names are not named the same, eg: select * from comments inner join users on (comments.user_id=users.id) You have to use the full tablename (or alias) and the field name. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Your post made perfect sense to me about the INNER JOIN , I looked it up but it is not returning the AdminID, maybe my syntax is wrong? $query = SELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders ON AdminID(admin, workorders) WHERE admin.UserName = '.$_SESSION['user'].' ; The syntax is wrong. inner join workorders using (adminid) ^^ only works if both tables are using the same field name, the db will expand it into the syntax below. Here you just need to specify the fieldname to join on (ie the common one). or inner join workorders on (admin.adminID=workorders.adminID) ^^ if the field names are not named the same, eg: select * from comments inner join users on (comments.user_id=users.id) Well I tried both ways and still cannot get it to pick up the AdminID, $querySELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders on AdminID WHERE admin.UserName = 'tmiller' $result$row $SortByWorkOrderID DESC$Page1$PerPage30$StartPage0$sqlSELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders ON AdminID(admin, workorders) WHERE admin.UserName = 'tmiller' $Total0
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Well I tried both ways and still cannot get it to pick up the AdminID, $query SELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders on AdminID WHERE admin.UserName = 'tmiller' $result $row $SortBy WorkOrderID DESC $Page 1 $PerPage30 $StartPage 0 $sql SELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders ON AdminID(admin, workorders) WHERE admin.UserName = 'tmiller' $Total 0 That is not the right syntax still. It needs to be: select admin.AdminID, workorers.AdminID from admin inner join workorders using (AdminID) WHERE admin.userName='tmiller'; or select admin.AdminID, workorers.AdminID from admin inner join workorders ON (admin.AdminID=workorders.AdminID) WHERE admin.userName='tmiller'; -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Terion Miller wrote: Your post made perfect sense to me about the INNER JOIN , I looked it up but it is not returning the AdminID, maybe my syntax is wrong? $query = SELECT admin.AdminID , workorders.AdminID FROM admin INNER JOIN workorders ON AdminID(admin, workorders) WHERE admin.UserName = '.$_SESSION['user'].' ; The syntax is wrong. inner join workorders using (adminid) ^^ only works if both tables are using the same field name, the db will expand it into the syntax below. Here you just need to specify the fieldname to join on (ie the common one). or inner join workorders on (admin.adminID=workorders.adminID) ^^ if the field names are not named the same, eg: select * from comments inner join users on (comments.user_id=users.id http://users.id) Well I tried both ways and still cannot get it to pick up the AdminID, The main problem is that you've never explained what you want to get from the query. The replies have used your code as an example and I'm pretty sure that's not what you want. Unless I totally mis-understand what you want, you have 2 options: 1. Use the 2 queries that I gave you in a previous post. 2. Use a subquery: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); -Shawn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
The main problem is that you've never explained what you want to get from the query. The replies have used your code as an example and I'm pretty sure that's not what you want. Unless I totally mis-understand what you want, you have 2 options: 1. Use the 2 queries that I gave you in a previous post. 2. Use a subquery: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); 3 - fix the join ;) -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Making a Variable from different tables with Matching Dbfields?
Chris wrote: The main problem is that you've never explained what you want to get from the query. The replies have used your code as an example and I'm pretty sure that's not what you want. Unless I totally mis-understand what you want, you have 2 options: 1. Use the 2 queries that I gave you in a previous post. 2. Use a subquery: $sql = SELECT * FROM workorders WHERE AdminID = (SELECT AdminID FROM admin WHERE UserName = ' . mysql_real_escape_string($_SESSION['user']) . '); 3 - fix the join ;) Yes, however, I'm going out on a limb here because we don't really know what he wants - he is only getting admin.AdminID, workorders.AdminID returned in all of the queries I've seen. I'm assuming that he wants some of the workorder details. -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
