MySQL Query on multiple tables

[cjacks]

I have 2 tables linked by a common field.

users
------
id
....more columns
 
users_profiles
--------------
users_id
...more columns
 

The user id in table users matches the users_id in table users_profile.

I need to querry from users, get it's match in users_profile and display the reults in php page.


This is what I got so far, but the result page is blank.

// Make a MySQL Connection
// Construct our join query
$query = "SELECT users.id, users_profile.users_id FROM users, users_profile WHERE users.id = users_profile.id";
    // SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1; 
$result = mysql_query($query) or die(mysql_error());
 
 
// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
    echo $row['id']. " - ". $row['users_id'];
    echo "<br />";
}
 

Thanks for the help, I am a novice at this and it's driving me crazy!

[Atli]

Hi. Welcome to TSDN!

In your SQL query you used multiple tables in your FROM clause. That's the most basic and simple version of a table JOIN you can do.

I'd recommend a slightly more complex version of a JOIN, which would clarify the query (imo anyways :P) and could possibly reduce the load this query would put on the server.

Joining tables the way you do in your query, the MySQL server simply compiles a table of every possible combination of every column in the table, which can get extremely large!, and then uses your WHERE clause to filter out all the rows that do not have matching user id's.

If you were to use a INNER JOIN, you could specify how MySQL compiles the table by giving it a list columns on which to combine. This results in a far smaller table and is in many ways easier to use.

Consider this:

SELECT users.id, users_profiles.users_id FROM users
INNER JOIN users_profiles
  ON users.id = users_profiles.users_id
 

[cjacks]

Here is the result of your code. Getting closer.
2 - 2 -
20 - 20 -
37 - 37 -
41 - 41 -
53 - 53 -
56 - 56 -
57 - 57 -
60 - 60 -
69 - 69 -
71 - 71 -
73 - 73 -


Something wrong in my results statement:

[PHP] $result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['id']. " - ". $row['users_id']. " - $real_name ";
echo "$real_name";
echo "<br />";[/PHP]

[cjacks]

Hi. Welcome to TSDN!

In your SQL query you used multiple tables in your FROM clause. That's the most basic and simple version of a table JOIN you can do.

I'd recommend a slightly more complex version of a JOIN, which would clarify the query (imo anyways :P) and could possibly reduce the load this query would put on the server.

Joining tables the way you do in your query, the MySQL server simply compiles a table of every possible combination of every column in the table, which can get extremely large!, and then uses your WHERE clause to filter out all the rows that do not have matching user id's.

If you were to use a INNER JOIN, you could specify how MySQL compiles the table by giving it a list columns on which to combine. This results in a far smaller table and is in many ways easier to use.

Consider this:

SELECT users.id, users_profiles.users_id FROM users
INNER JOIN users_profiles
  ON users.id = users_profiles.users_id
 

I figured it out. Had to place all the fields I wanted to view in the query statement. Thanks for the help!!!

[Atli]

Glad you got it working! :)