How to link Multi mysql tables

[j5646w]

Okay I have 3 tables

 

1) Images - "image_id" "image_url" "image_thumb" "image_date"

2) User - "user_id" "user_name" "user_surname"

3) Other - "other_id" "other_note"

 

The image_id , user_id, other_id have the same number (to link them) my question is how will I go about linking the three and echoing the information. Keep in mind that some users don't have images

 

<?php

include('config.php');

$result = mysql_query("SELECT * FROM other, images, user WHERE other.other_uid = 1 AND images.image_id = 1 AND user.user_uid = 1 ORDER BY images.date DESC limit 5");

$url='http://localhost/petonline.co.za/data/';

while($row = mysql_fetch_array($result)){

echo "<B>".$row['pet_depo']."</b><br/>, Photo: <img src='".$url.$row['thumb']."'>, Name: ".$row['user_uid']."<br/><br/><br/>";

}

[ManiacDan]

You're looking for the JOIN syntax

 

-Dan

[j5646w]

Hi ther ManiacDan, thanks for the reply

I tried the JOIN syntax

 

 

But if I echo now it shows the results twice?

 

$result = mysql_query("SELECT * FROM other INNER JOIN images ON other.other_id=images.id WHERE other_note = 'Lost' ORDER BY images.date DESC limit 20");

[Muddy_Funster]

Your table structure is all wrong.  each table should have it's own unique key field with another field used for linking accross the tables.

 

set your tables up like this:

users - can stay the same, as it will be your core refference ( although make sure that the user_id filed is an auto_inc Primary Key).

in each of the other two tables change the image_id and other_id to also be auto_inc Primary Keys and add to each of these tables a user_id column that will contain the user_id that the image and other tables will refference.

 

Once you have done this you should be able to pull the information out properly using a JOIN:

SELECT user_name, user_surname, image_thumb, image_url, image_date, other_note
FROM users RIGHT JOIN images 
ON (users.user_id = images.user_id)
RIGHT JOIN other
ON (users.user_id = images.user_id)
WHERE users.user_id = <xxx>

where <xxx> the user id of the account that you want to look up.