PHP MySQL Help

[mdluna77]

Hello, I have a question about a project I am working on. I have two table in MySQL artists and songs and I am trying to display an HTML table with the artist name in the first cell, then all songs that belong to that artist in the second cell, and in the third cell it has a description of the artist. The way I have it set up is working just how I want it to but I'm sure it is not the right way, because one of the MySQL query's is happening inside a while loop, so I am guessing it executes the query each time it goes through the artists. I am new to PHP and MySQL so I am sorry if it doesn't make sense. Here is a sample of the code, I'm just wondering what the right way would be to do this? Thank you.

 

        $query = "SELECT artist_id, artist, description FROM artists WHERE active = 1 ORDER BY artist";

$result = mysql_query($query);

 

 

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

 

$query2 = "SELECT * FROM songs WHERE artist_id = {$artist['artist_id']} AND active = 1";

$result2 = mysql_query($query2);

 

?>

 

<table cellpadding="0" cellspacing="2" border="0" class="details">

<tr>

<th>Artist</th>

<th>Songs</th>

<th>Description</th>

</tr>

<tr>

<td>

      <?php echo $artist['artist']; ?>

</td>

<td>

 

<?php

while ($song = mysql_fetch_array($result2)) {

echo $song['song'];

}

?>

 

</td>

<td>

<?php echo $artist['description']; ?>

</td>

</tr>

</table>

 

 

Thank you for your help.

[mdluna77]

Anyone have any advice?????

[mdluna77]

anyone??

[DarkWater]

Yeah, you'd use a JOIN.

 

SELECT a.artist, a.description, s.song FROM artists AS a LEFT JOIN songs AS s USING (artist_id);

[mdluna77]

Great, Thank you. I will go read up on LEFT JOIN. How would you display the results when using LEFT JOIN? Would you still use it in a while loop, and does it just return a regular array? Thanks.