Using data from one table to retrieve data from another

[JazzyB]

Hi,

This is quite long and very complicated (at least for me). I have two tables in a database and want to use all the information from one table to pull out information from the other table.

First table(favourites) records a list of users favourite artists the field names are user_id, artist_id, and artist_name.
Second table(artists) contains artist information and also has the fields artist_id and artist_name.

Here is my code that clearly doesn't do the above:

[code]<?php

if($_SESSION['first_name']){

$id = $_SESSION['id'];


//CHECKING USER HAS ANY ARTISTS ADDED AS FAVOURITES
$query = "SELECT * FROM favourites WHERE user_id = '$id'";
$result = mysql_query($query) or die (mysql_error()."<br />Couldn't execute query: $query");  //then get your results onto your page.
$num_rows = mysql_num_rows($result);
$info = mysql_fetch_array($result);

$artist_id = $info['artist_id'];
$artist_name = $info['artist_name'];


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

$artist_id2 = $row['artist_id'];
$artist_name2 = $row['artist_name'];

}

if($num_rows == TRUE){

//Mysql query to pull out details of artists from artists table

$query2 = "SELECT * FROM artists WHERE artist_id = '$artist_id' AND artist_name = '$artist_name'";
$result2 = mysql_query($query2) or die (mysql_error()."<br />Couldn't execute query: $query2");  //then get your results onto your page.
$num_rows2 = mysql_num_rows($result2);
$info2 = mysql_fetch_array($result2);

//echo 1st result here

echo $info2['artist_name'].'<br />';


$query3 = "SELECT * FROM artists WHERE artist_id = '$artist_id2' AND artist_name = '$artist_name2'";
$result3 = mysql_query($query3) or die (mysql_error()."<br />Couldn't execute query: $query3");  //then get your results onto your page.
$num_rows3 = mysql_num_rows($result3);
$info3 = mysql_fetch_array($result3);

//echo the rest of the results here

echo $info3['artist_name'];

while($row3=mysql_fetch_array($result3)) {

echo $row3['artist_name'];

}


}else{

echo 'You currently have no artists added on your favourites list.';

}

} else {
echo '<p class="leftmargin">You need to be logged in to view this page:</p>';
include 'login_box.php';
}

?>
[/code]

Please can someone help me with this?

Thanks,

Jaz

[Michan]

Hi JazzyB,

I had this problem a few days ago. Here is what I learned: You need to join the relevant ID in the first table with (on) the primary key in the second.

So for example,

$query = "SELECT * FROM favourites LEFT JOIN artists ON favourites.artist_id = artists.artist_id WHERE user_id = '$id'";

This will retrieve all of the relevant data relating to that particular favourite from both tables. (You may need to modify it slightly to fit your script, but the elements are there.)

Hope that helps,

- Mi