help with a join statement

sks1024

I have 2 tables for an images gallery

albums

id
name
create_date
album_id (int)

images

id
album_id (int, same as the album_id above for which ever album it belongs)
paths

I have in my mind that the tables are linked together by the album_id field

How do I form a select statment to select a certian album_id and the corresponding images from the second table with the same album_id with one query.

I have this so far

select * from albums a left join images i on a.album_id = i.album_id

Someone lead me in the right direction

$query = "select * from albums a right join images i on a.album_id = i.album_id";
$result = mysql_db_query ($DBName, $query, $Link);
$col = 1;
?>
</head>

<body>

<table width="50%"  border="0" align="center" cellpadding="0" cellspacing="0">
  <tr align="center" valign="middle">
    <td>
<table width=100% height=80 border=0 cellspacing=0 cellpadding=0 align=center class=category_style>
	<tbody>
<tr>
<td>&nbsp;</td>
</tr>
<tr>

<?php

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

$images = array($row['paths']);




$output .= "
<td>
<div class=recentalbum>

<span class=albumlink-span>
<a class=albumlink href=view_photos.php?album_id=$row[album_id]>
<img class=shim src=albumshim.gif alt= /><img src=./thumb.php?src=/test/images/$images[0]&w=100&q=99 border=0></a>
</span>
<a href=view_photos.php?album_id=$row[album_id]>$row[name]</a>
$row[create_date]
</div>
</td>"; 

if ($col % 5 == 0)    { 
	$output .= "</tr><tr>";
    $col = 0; 
} 

$col++; 

}

echo $output;


?>

<td align=center>

  </td>
</tr>
<tr>
	<td align=center>&nbsp;</td>
</tr>
</tbody>
</table>
</td>

</body>
</html>

tomhath

You want in INNER JOIN so you only get images in the same album.

select a.name, a.create_date, i.paths  

from albums a 
inner join images i on a.album_id = i.album_id

sks1024

i think i need some help on the databse structure also. What is primary key and how to link them together

tomhath

The primary key is the first UNIQUE index MySQL finds.

http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

The INNER JOIN will find matching id's when the query is run. You don't have to do anything to "link" them, the database handles all that for you.

sks1024

I think i have it now. one more question. how can I get only the first image to show for that album number in the show albums page

tomhath

Assuming the lowest id is the first, maybe a subselect (untested). I'm not sure if this will work with MySql. If not we can try something else.

 select a.name, a.create_date, 
         (select paths from images i 
          where  a.album_id = i.album_id order by i.id limit 1) as paths
from albums a