joining 3 tables php+mysql, display images

OK, slowly progressing on learning php and mysql and get stuck on every step. I hope it is simple enough that you can help!

I have 3 tables: themes(id, name), albums(id, title, theme_id) and images(id,name, album_id, image_url).

i have:

function find_themes()
{
  db_connect();

  $query = sprintf("SELECT * from themes order by id DESC",
                         mysql_real_escape_string($theme_id));

$result = mysql_query($query);
if(!$result)
  {
    return false;
  }

  $result = db_result_to_array($result);

  return $result;

}

function find_images_by_album($album_id)
{
  db_connect();

  $query = sprintf("SELECT images.id,
                           images.name,
                           images.url,
                           images.album_id,
                           albums.id,
                           themes.name as theme
                  FROM
                        开发者_开发知识库 images, themes, albums
                  WHERE
                         albums.theme_id = themes.id and images.album_id = albums.id
                         ",
                         mysql_real_escape_string($album_id));

$result = mysql_query($query);
if(!$result)
  {
    return false;
  }

  $result = db_result_to_array($result);

  return $result;

}

function find_albums_by_theme($theme_id)
{
  db_connect();

  $query = sprintf("SELECT albums.id,
                         albums.title,
                         albums.theme_id,
                         themes.name as theme
                  FROM
                         albums, themes
                  WHERE
                         theme_id = '%s' and albums.theme_id = themes.id
                         ",
                         mysql_real_escape_string($theme_id));

$result = mysql_query($query);
if(!$result)
  {
    return false;
  }

  $result = db_result_to_array($result);

  return $result;

}

I am struggling to display correct images in themes and albums. The info however works.

    $theme = find_theme($_GET['theme_id']);
    $albums = find_albums_by_theme($_GET['theme_id']);
    $images = find_images_by_album($image['album_id']);
    $themes = find_themes();

    <?php foreach($albums as $album):?>
    <a href="index.php?view=show&id=<?php echo $album['id']; ?>" class="medium-pic"><img src="photos/<?php echo $image['url']; ?>/medium/<?php echo $image['name']; ?>.jpg" alt="<?php echo safe_output($album['title']); ?>" /></a>
</div>
<div class="medium-photo-info">              
<span class="title"><a href="index.php?view=show&id=<?php echo $album['id']; ?>"><?php echo safe_output($album['title']); ?></a> </span> |
</div>
    <?php endforeach; ?>

    <?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>

progress note: thank you all, so I defined

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 = '%s' 
                         ",
                         mysql_real_escape_string($album_id));

$result = mysql_query($query);
if(!$result)
  {
    return false;
  }

  $result = db_result_to_array($result);

  return $result;

}

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;

}

and

    $image = find_image($_GET['id']);
$images = find_images_by_album($image['album_id']);
    $album = find_album($_GET['id']);
$albums = find_albums_by_theme($album['theme_id']);
    $theme = find_theme($_GET['theme_id']);
$themes = find_themes();

SO now, if I run

 <?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; ?>

it returns images from first album only?

---

My two cents... Aside what Ryan Mitchell wrote:

1) move db_connect() call out of functions and put it before functions calls. MySQL connect doesn't need to be called every time you want to perform a query:

db_connect();
$theme = find_theme($_GET['theme_id']);
$albums = find_albums_by_theme($_GET['theme_id']);

2) for queries, my preference is to use following technique rather than sprintf. I found it more readable and in case you need to do some more logic before performing query, you see it always on the same place in function. (subjective manner)

function find_theme($id)
{
    $id = (int) $id;
    $query =
        "SELECT
            *
        FROM
            `themes`
        WHERE
            `id` = $id
        ORDER BY
            `id` DESC";
    $result = mysql_query($query);
    // ...
}

---

find_images_by_album() gets an id parameter but never uses it. Otherwise the queries seem OK (though you should find a safer solution than manual query parameter escaping, preferably parametrized queries).

---

This looks a bit odd:

$query = sprintf("SELECT * from themes order by id DESC", mysql_real_escape_string($theme_id));

For a start, where is the value of $theme_id coming from? And what are you trying to do here? (s)printf is used to supply parameters to a format string. You have a straight string here (no '%' variables).

The naming is a bit odd - is $theme_id an id number? If so, you don't need to escape it - that's for a string.

---

$theme = find_theme($_GET['theme_id']);
$albums = find_albums_by_theme($_GET['theme_id']);
$images = find_images_by_album($image['album_id']);
$themes = find_themes();

For one thing, $image is undefined here. If you have notices turned off in PHP you won't get an error, but find_images_by_album will be called with an empty string as its parameter and will certainly not return what you want.

For another thing, even if $image['album_id'] WAS defined, as dopey points out, you're not including any type specifiers in some of your sprintf format strings.

$query = sprintf("SELECT images.id,
                           images.name,
                           images.url,
                           images.album_id,
                           albums.id,
                           themes.name as theme
                  FROM
                         images, themes, albums
                  WHERE
                         albums.theme_id = themes.id and images.album_id = albums.id
                         ",
                         mysql_real_escape_string($album_id));

This will always return the same $query. You likely want something like:

$query = sprintf("SELECT images.id,
                           images.name,
                           images.url,
                           images.album_id,
                           albums.id,
                           themes.name as theme
                  FROM
                         images, themes, albums
                  WHERE
                         albums.theme_id = themes.id and images.album_id = albums.id
                         and albums.id = %d
                         ",
                         mysql_real_escape_string($album_id));