need join query to join three tables

[vizz]

I have following tables for photo album script. How to display Album title and album cover inside anchor tag?
(Display Albums cover on homepage and redirect to gallery page, when clicked, to see images inside that album)

• get id of album
• get album title

• get album cover of each album

  while($row = mysql_fetch_assoc($result)) {
  
      <a  href="gallery.php?id=$id"><img src=""><span><?php echo $title?></span></a>
   }
  

Database

CREATE TABLE IF NOT EXISTS `albums` (
  `album_id` mediumint(12) NOT NULL AUTO_INCREMENT,
  `album_name` varchar(255) NOT NULL,
  PRIMARY KEY (`album_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;    

CREATE TABLE IF NOT EXISTS `album_cover` (
  `cover_id` mediumint(12) NOT NULL AUTO_INCREMENT,
  `album_id` mediumint(12) NOT NULL,
  `photo_id` mediumint(12) NOT NULL,
  PRIMARY KEY (`cover_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `images` (
  `photo_id` mediumint(12) NOT NULL AUTO_INCREMENT,
  `photo_title` varchar(255) NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `album_id` mediumint(12) NOT NULL,
  PRIMARY KEY (`photo_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

[diafol]

If the relationship between cover art and album is 1:1, is there a need for a separate table for cover art?

Also, can a photo be in more than one album. If so, you'll need a link table like album_images: album_id | image_id (compound PK) - possibly with other fields if you want to provide unique titles or descriptions for the same images in different albums.

Your click on image suggests that you are sending a request to the server to get more info (the images), so I don't see why you need to link anything (via traditional join). Just a WHERE clause should suffice.

Get the WHERE id from the url querystring:

$default_album = 1; // or provide an error message 
$album_id = (isset($_GET['id']) && is_int($_GET['id']) && $_GET['id'] > 0) ? (int) $_GET['id'] : $default_album; 

[vizz]

Can you help with query to join table?

[diafol]

For your links...

SELECT a.album_id, a.album_name, i.title, i.thumbnail FROM albums AS a INNER JOIN images AS i ON a.id = i.album_id

Of course you can add a filter WHERE clause and/or LIMIT clause if you wish...

SELECT a.album_id, a.album_name, i.title, i.thumbnail FROM albums AS a INNER JOIN images AS i ON a.id = i.album_id WHERE album_name LIKE '%" . $term . "%'";

or useful for "pagination":

SELECT a.album_id, a.album_name, i.title, i.thumbnail FROM albums AS a INNER JOIN images AS i ON a.id = i.album_id LIMIT ($start, $end)";