Retrieving from multiple tables using PHP and MySQL

mpar612

I am a newbie to MySQL and I'm trying to learn how to do this right.

I have two tables, one for artist info and another for pictures.

Here are the contents for the 10spot_artists table: artist_id, artist_name, add_date, headline, bio
Here are the contents for the 10spot_pictures table: picture_id, artist_id, path

I want to select all of the pictures from the 10spot_pictures table and print those on a page using php. Underneath each picture I want the artists name to appear. The artists names are retrieved from the 10spot_artists table where the artist_id from both tables match.

I have tried the following code without much luck. I pieced it together using a book and this forum.

Could anyone help send me in the right direction?

Thanks in advance!

     <?php
      $sql = "SELECT p.picture_id, p.artist_id, p.location, a.artist_name FROM 10spot_pictures p
            INNER JOIN 10spot_artists a
             ON a.artist_id = p.artist_id
            ORDER BY a.artist_name ASC";     

      $rows = $db->getAll($sql);

  foreach ($rows as $row) {
  print "<tr><td><img src=\"$row[p.location]\" /></td></tr>
    <tr><td>$row[a.artist_name]</td></tr>
    <tr><td>&nbsp;</td></tr>";
    }            
?>

Piranha

I can see two problems with your code. The first is that you said that the pictures table had a path column, but in the query you get location. The second one is how you get the value, it should be $row['location']. I have changed it in your code.

     <?php
      $sql = "SELECT p.picture_id, p.artist_id, p.location, a.artist_name FROM 10spot_pictures p
            INNER JOIN 10spot_artists a
             ON a.artist_id = p.artist_id
            ORDER BY a.artist_name ASC";     

      $rows = $db->getAll($sql);

  foreach ($rows as $row) {
  print "<tr><td><img src=\"$row['location']\" /></td></tr> // Changed this row
    <tr><td>$row[a.artist_name]</td></tr>
    <tr><td>&nbsp;</td></tr>";
    }            
?>

Notice that I have no idea what happens in your database class, it is possible that there is an error in that or that it returns a result rather than an array.

mpar612

Thanks! I made your changes and now I get this error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

For this line of code:

print "<tr><td><img src=\"$row['location']\" /></td></tr>

Any thoughts? Thank you so much!

Piranha

As I already said:

Piranha wrote:
Notice that I have no idea what happens in your database class, it is possible that there is an error in that or that it returns a result rather than an array.

For me to be able to help you out you have to post the code from the database class.