CODEWITHSUNDEEP
phpmysqlsqldatabase-relations
Fabian
Fabian

Reputation: 107

PHP / MySQL relation between 2 tables

I am using the following tables:

  1. artists
  2. related_artists

The idea is when I enter an artist's page with an artist_id I can use that id to load related artists. The following code works, but how do I put it in a single query? I can't figure it out.

To connect related_artists with artists I created the following code:

$sql = "SELECT related_artist_id FROM related_artists WHERE artist_id = 1";
$res = mysqli_query($db, $sql);             
if (!$res) {
    echo "Er is een fout opgetreden."; 
    exit;
} else {
    while ($row = mysqli_fetch_array($res)) {
    $query = 'SELECT * FROM artists WHERE  artist_id = '.$row["related_artist_id"];
    print_r($query."<br />\n");
    $result = mysqli_query($db, $query);
    if ($result) {
        while ($test = mysqli_fetch_array($result)) {
        echo $test["lastName"]."<br />\n";
        }
    } else { 
        echo "It doesn't work";
        exit;
    }       
  }
}

Upvotes: 1

Views: 10761

Answers (3)

Jonny 5
Jonny 5

Reputation: 12389

You can use a LEFT JOIN, try this:

SELECT b.* 
FROM related_artist a
LEFT JOIN artists b 
  USING(artist_id)
WHERE a.artist_id = 1

Should return * from artists, where I aliased artists as b and related_artist as a.

Didn't test, does it work for you / return the expected result?

Upvotes: 2

dagfr
dagfr

Reputation: 2384

You can just try : 

select * 
from artists 
where artist_id in (
    select related_artist_id 
    from related_artists 
    WHERE artist_id = 1
);

Upvotes: 2

Jake Toolson
Jake Toolson

Reputation: 480

SELECT * FROM artists where artists.arist_id = 1 
INNER JOIN related_artist ON related_artist.artist_id = artists.artist_id

This provides a join on the artist_id columns of both tables, having artist_id = 1. I'm not sure if you need an Inner or a Left join

Upvotes: 0

Related Questions