I am trying to build a simple image gallery. I am stuck on the mysql query to return images per album.
When I run the following code it returns the correct album but includes only first album’s images, regardless what album I am in.
I have 3 tables: themes(id, name), albums(id, title, theme_id) and images(id,name, album_id, image_url).
[code=php]
$album = find_album($_GET[‘id’]);
$albums = find_albums_by_theme($album[‘theme_id’]);
$image = find_image($_GET[‘id’]);
$images = find_images_by_album($image[‘album_id’]);
$themes = find_themes();
<ul>
<?php foreach($images as $image): ?>
<li><img src=”photos/<?php echo $image[‘url’]; ?>/large/<?php echo $image[‘name’]; ?>.jpg” alt=”<?php echo safe_output($album[‘title’]); ?>” /></li>
<?php endforeach; ?>
</ul>
This is my db_functions, the function that I am struggling with is function find_images_by_album:
[code=php]
function find_albums_by_theme($theme_id)
{
db_connect();
$query = sprintf(“SELECT albums.id,
albums.title,
albums.theme_id,
albums.created_at,
albums.fullname,
albums.vote_cache,
themes.name as theme
FROM
albums, themes
WHERE
themes.id = ‘%s’ and albums.theme_id = themes.id
ORDER by vote_cache DESC
“,
mysql_real_escape_string($theme_id));
$result = mysql_query($query);
if(!$result)
{
return false;
}
$result = db_result_to_array($result);
return $result;
}
/**
* Finds themes
* @return array
*/
function find_themes()
{
db_connect();
$query = “SELECT * from themes order by id DESC”;
$result = mysql_query($query);
if(!$result)
{
return false;
}
$result = db_result_to_array($result);
return $result;
}
/**
* Finds theme by id
* @return array
*/
function find_theme($id)
{
db_connect();
$query = sprintf(“SELECT * FROM themes
WHERE id = ‘%s’
“,
mysql_real_escape_string($id));
$result = mysql_query($query);
if(!$result)
{
return false;
}
$row = mysql_fetch_array($result);
return $row;
}
/**
* Finds album
* @return array
*/
function find_album($id)
{
db_connect();
$query = sprintf(“SELECT
albums.id,
albums.title,
albums.theme_id,
albums.created_at,
albums.fullname,
albums.vote_cache,
albums.discuss_url,
albums.description,
themes.name as theme
FROM
albums, themes
WHERE
albums.theme_id = themes.id and
albums.id = ‘%s’
“,
mysql_real_escape_string($id));
$result = mysql_query($query);
if(!$result)
{
return false;
}
$row = mysql_fetch_array($result);
return $row;
}
/**
* Finds image
* @return array
*/
function find_image($id)
{
db_connect();
$query = sprintf(“SELECT
images.id,
images.album_id,
images.name,
images.url,
albums.id
FROM
images, albums
WHERE
images.album_id = albums.id and
images.id = ‘%s’
“,
mysql_real_escape_string($id));
$result = mysql_query($query);
if(!$result)
{
return false;
}
$row = mysql_fetch_array($result);
return $row;
function find_images_by_album($album_id)
{
db_connect();
$query = sprintf(“SELECT images.id,
images.name,
images.url,
images.album_id,
albums.id,
albums.theme_id,
themes.name as theme,
themes.id
FROM
images, themes, albums
WHERE
images.album_id = albums.id and albums.theme_id = themes.id and
albums.id = ‘%d’
ORDER BY images.id;
“,
mysql_real_escape_string($album_id));
$result = mysql_query($query);
if(!$result)
{
return false;
}
$result = db_result_to_array($result);
return $result;
}