homepage Welcome to WebmasterWorld Guest from 54.227.160.102
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
JOIN statement?
Can anyone help please
wruk999

10+ Year Member



 
Msg#: 477 posted 10:39 am on Apr 18, 2003 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 477 posted 10:55 am on Apr 18, 2003 (gmt 0)

Sounds like you want something like this..


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

wruk999

10+ Year Member



 
Msg#: 477 posted 11:56 am on Apr 18, 2003 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 477 posted 12:18 pm on Apr 18, 2003 (gmt 0)

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

10+ Year Member



 
Msg#: 477 posted 12:49 pm on Apr 18, 2003 (gmt 0)

Hi Birdman,

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

Thank You :)

Regards,
wruk999

hpche

10+ Year Member



 
Msg#: 477 posted 1:09 pm on Apr 18, 2003 (gmt 0)

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

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



 
Msg#: 477 posted 1:12 pm on Apr 18, 2003 (gmt 0)

or you can access them by their index

$row[0]
$row[1]

grifter

10+ Year Member



 
Msg#: 477 posted 4:17 pm on Apr 18, 2003 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved