Welcome to WebmasterWorld Guest from 54.196.175.173

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

JOIN statement?

Can anyone help please

     

wruk999

10:39 am on Apr 18, 2003 (gmt 0)

10+ Year Member



Hi,

I have a PHP page which queies a table in a MySQL database.
This table 'stock', contains a 'user' column.

There is a second table 'users' which has a column, 'id' and another 'name'

All the items in the first table have a value for user, and this value relates to the id column of the user table.

On pages, where there is just one product being displayed, it is easy to display the 'name' from the user table, because I just use a SELECT statement, for the one user id.

Now, here is my problem:
I have a page which is displaying 30 records. I need this page to have the 'name' column of the user table displayed next to each product. I can have the stock.user column value displayed, but instead, I need it to take this id, and query the user table, and return the name of the user - not their ID.

I think that a JOIN is going to be the way, but I have not used these before. I have read the join page of the PHP manual, but it did not help me. :(

Is there anyone that have used joins and know of a select statement that would be able to do what I need it do

Regard,
wruk999

grahamstewart

10:55 am on Apr 18, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Sounds like you want something like this..


select user.name, stock.product from user,stock
where stock.user = user.id;

wruk999

11:56 am on Apr 18, 2003 (gmt 0)

10+ Year Member



grahamstewart,

Thank you for your help. The query you told me, works a treat.

I just jave one more question: The results that I return, display perfectly, and when I use

".$row['dealer']." in my echo statement
it prints out the id which is actually in the stock table..
('dealer' is the actual 'user' column in the stock table),

How do I get it to print out the data from the other table, the name column, (which unfortunately is actually 'dealer', so both columns are the same name)?

I have tried:
".$row['users.dealer']."
but it didn't work.

I hope I am making sense, and I hope that you can help.
Regards,
wruk999

Birdman

12:18 pm on Apr 18, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I would change the name of the dealer in stock to dealer_id and the dealer in users to dealer_name.

I try to stay away from naming rows the same in different tables.

Then you can:
SELECT [perldoc.com] dealer_name,and_more_stuff FROM stock,users WHERE stock.dealer_id = users.user_id

wruk999

12:49 pm on Apr 18, 2003 (gmt 0)

10+ Year Member



Hi Birdman,

Thanks for the advice. I updated the table structure, and everything is working fine now!

Thank You :)

Regards,
wruk999

hpche

1:09 pm on Apr 18, 2003 (gmt 0)

10+ Year Member



You could also do it using 'as' I think, without needing to change the table structure.

SELECT table1.dealer, table2.dealer AS otherdealer FROM table1, table2 WHERE....

And then access them like

$row['dealer']
$row['otherdealer']

jatar_k

1:12 pm on Apr 18, 2003 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



or you can access them by their index

$row[0]
$row[1]

grifter

4:17 pm on Apr 18, 2003 (gmt 0)

10+ Year Member



I think "AS" is good practice, for when you encounter situations where changing the column names might break other stuff.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month