JOIN statement?

Can anyone help please

[wruk999]

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]

Sounds like you want something like this..

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

[wruk999]

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]

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]

Hi Birdman,

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

Thank You :)

Regards,
wruk999

[hpche]

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]

or you can access them by their index

$row[0]
$row[1]

[grifter]

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