PHP / MySQL relation between 2 tables

Question

I am using the following tables:

artists
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;
    }       
  }
}

"I can't figure it out." What have you tried? LEFT JOIN?

Popnoodles
– Popnoodles

2013-12-27 21:06:44 +00:00
Commented Dec 27, 2013 at 21:06 — Popnoodles
– Popnoodles, Commented Dec 27, 2013 at 21:06

dagfr · Accepted Answer · 2013-12-27 21:33:56Z

2

You can just try :

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

answered Dec 27, 2013 at 21:33

dagfr

2,3841 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav Bertram · Accepted Answer · 2013-12-27 21:39:31Z

2

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?

edited Dec 27, 2013 at 21:39

Gustav Bertram

15k3 gold badges46 silver badges68 bronze badges

answered Dec 27, 2013 at 21:24

Jonny 5

12.4k2 gold badges28 silver badges42 bronze badges

2 Comments

Gustav Bertram Over a year ago

+1 for USING. I haven't seen that before, and looking it up was educational.

Jonny 5 Over a year ago

@GustavBertram thanks! yea it's useful sometimes :-)

Jake Toolson · Accepted Answer · 2013-12-27 21:30:32Z

0

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

edited Dec 27, 2013 at 21:30

answered Dec 27, 2013 at 21:10

Jake Toolson

4803 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP / MySQL relation between 2 tables

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related