How to view first picture in a file using php?

Question

Hi I am trying to figure out how to display the first image in an album that the user uploads. I have a website that lets users upload images to albums, Now I want to display the first picture in the album as the album cover, right now when the user clicks on the albums page all that shows is the album info like name, description, etc. and I used the album name as the link to view the images in the album, what I want is the first image in the album to be displayed as link to that album. I have alot of stuff going on with this but I will try and provide everything so you can understand easier. Here is my albums page code:

$albums = get_albums();

if(empty($albums)){
echo 'No Albums';
}else{

foreach($albums as $album){
    echo '<p><a href="view_album.php?album_id=', $album['id'] ,'">', $album['name'],'</a>(', $album['count'], ' images) <br />
    ', $album['description'], '...<br />
    <a href="edit_album.php?album_id=', $album['id'] , '">Edit</a> / <a href="delete_album.php?album_id=', $album['id'] , '">Delete</a>
    </p>';
}
}

Here is the function from my functions php file that I include in this file:

function get_albums(){
$albums = array();

$albums_query = mysql_query("
SELECT `albums`.`album_id`, `albums`.`timestamp`, `albums`.`name`, LEFT(`albums`.`description`, 50) as `description`, COUNT(`images`.`image_id`) as `image_count`
FROM `albums`
LEFT JOIN `images`
ON `albums`.`album_id` = `images`.`album_id`
WHERE `albums`.`user_id` = " . $_SESSION['user_id'] . "
GROUP BY `albums`.`album_id`
");

while($albums_row = mysql_fetch_assoc($albums_query)){
    $albums[] = array(
        'id'            => $albums_row['album_id'],
        'timestamp'     => $albums_row['timestamp'],
        'name'          => $albums_row['name'],
        'description'   => $albums_row['description'],
        'count'         => $albums_row['image_count']
    );
}

return $albums;

}

Now I am a little lost and confused on what route to take to change the link to open the albums from the album name to the first image in the album. If the albums do not have any pics in them I plan on using a simple if statement to show a generic empty folder image. My database using two table to upload images, the first is the albums table and the second is the images table. I also have a timestamp field that inserts the image upload timestamp, so can i return the images from that album and the image with the lowest timestamp so to return the first image uploaded to the album? Is there a SELECT statement to return this? Also I did create a folder that stores these images in the corresponding albums, can i return the first image in that album? What would be the easiest way to achieve this?

Here is how I get the images path:

function get_images($album_id){
$album_id = (int)$album_id;

$images = array();

$image_query = mysql_query("SELECT `image_id`, `album_id`, `timestamp`, `ext` FROM `images` WHERE `album_id` = $album_id AND `user_id` = ". $_SESSION['user_id']);
while($images_row = mysql_fetch_assoc($image_query)){
    $images[] = array(
        'id'            => $images_row['image_id'],
        'album'         => $images_row['album_id'],
        'timestamp'     => $images_row['timestamp'],
        'ext'           => $images_row['ext']
    );
}
return $images;
}

Here is my view_album.php file:

if(!isset($_GET['album_id']) || empty($_GET['album_id']) || album_check($_GET['album_id']) === false){
header('Location: albums.php');
exit();
}

$album_id = $_GET['album_id'];
$album_data = album_data($album_id, 'name');

echo '<h1 class="logo2">', $album_data['name'], '</h1>';
include 'includes/menu_album.php';

$images = get_images($album_id);

if(empty($images)){
echo 'No images.';
}else{
foreach($images as $image){
    echo '<a href="uploads/', $image['album'], '/', $image['id'], '.',     $image['ext'], '"><img src="uploads/thumbs/', $image['album'], '/', $image['id'], '.', $image['ext'], '" title="Uploaded ', date('D M Y', $image['timestamp']), '"></a> [<a       href="delete_image.php?image_id=', $image['id'], '">x</a>] ';
}
}

Answer 1

You are actually pretty close to getting the first image with your existing query. All you need to do as add the image name or path to the SELECT and the image timestamp to an ORDER BY to make sure you get the earliest one:

   $albums_query = mysql_query("
        SELECT 
          `albums`.`album_id`, 
          `albums`.`timestamp`, 
          `albums`.`name`, 
          LEFT(`albums`.`description`, 50) as `description`, 
          COUNT(`images`.`image_id`) as `image_count`, 
          CONCAT(`images`.`image_id`, '.', `images`.`ext`) AS `first_image`
        FROM `albums`
        LEFT JOIN `images`
        ON `albums`.`album_id` = `images`.`album_id`
        WHERE `albums`.`user_id` = " . $_SESSION['user_id'] . "
        GROUP BY `albums`.`album_id`
        ORDER BY `albums`.`timestamp` ASC, `images`.`timestamp` ASC
    ");

    if (!$albums_query) {
      die('Invalid query: ' . mysql_error());
    }

    while($albums_row = mysql_fetch_assoc($albums_query)){
        $albums[] = array(
            'id'            => $albums_row['album_id'],
            'timestamp'     => $albums_row['timestamp'],
            'name'          => $albums_row['name'],
            'description'   => $albums_row['description'],
            'count'         => $albums_row['image_count'],
            'image'         => $albums_row['first_image']
        );
    }

You can then update your output to include the image:

foreach($albums as $album){
    if (empty($album['image'])) {
        $album['image'] = 'default.jpg';
    }
    echo '<p>
      <a href="view_album.php?album_id=', $album['id'] ,'">
        <img src="uploads/thumbs/', $album['id'] ,'/', $album['image'] ,'" />', $album['name'],'
      </a>(', $album['count'], ' images) <br />
      ', $album['description'], '...<br />
      <a href="edit_album.php?album_id=', $album['id'] , '">Edit</a> / <a href="delete_album.php?album_id=', $album['id'] , '">Delete</a>
    </p>';
}