Select from 2 tables

[perky416]

Hi everyone,

 

Im trying to run a select query on 2 different tables to save me running 2 queries. Last night i found exactly what i needed on google, however like an idiot i didnt bookmark it and now i cant find it again.

 

Please could somoebody help me out with selecting fields from table1 where id='$id' and in the same query selecting fields from table2 where username='table1.username'? If that makes sense lol.

 

The latest attempt iv tried is as follows:

 

$query = mysql_query("SELECT username, password, email, title, first_name, last_name, phone, street, city, state, zip, country FROM users WHERE id='$id' JOIN SELECT payment_method FROM payment_info WHERE username='users.username'");

 

Thanks

[mikosiko]

here you go:

 

SELECT username, password, email, title, first_name, last_name, phone, street, city, state, zip, country, payment_method 
  FROM users JOIN payment_info ON payment_info.username = users.username
  WHERE  id = '$id';

 

Note: Pay attention in case you have same fields names in both tables, in such case you can prefix the fields names either with the table name or aliases.

 

and this is better that google

[perky416]

Thanks mate worked like a charm. I had a read through the SELECT syntax page on the mysql website but couldn't find exactly what i thought i needed. Ill have a read through the JOIN page.

 

Thanks again.

[perky416]

Hi mikosiko,

 

Iv just ran into a problem. When i copied the code you gave i missed out the 3rd line "WHERE id='$id'". Now that iv added this in im getting a Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource error message come up.

 

Do you know why this is happening?

 

Thanks

[perky416]

Never mind i figured it out.

 

I changed id to users.id.

 

Thanks