Hi guys,

I think I may have complicated matters for myself with this one.

I'm making an image gallery, which is built based on a hierarchy.

I have no problem outputting the images directly in their specific category.

The problem I'm having is I'm trying to call 1 thumbnail from the database that relates to the top level album title (country_title).

I think it's my SQL or perhaps my table structure that's failing me on this one.

My table structure is as follows:

country
country_id
country_title

state
state_id
state_title
country_id

suburb
suburb_id
suburb_title
state_id

description
description_id
description_thumbnail
description_photo
description_date
state_id

So, the first page will have a list of countries with the country title displayed with a random thumbnail called from the description table that relates to that country.

Here is a rough example of the code i'm using, there is no html formatting as im not sure how i'm going to output it.

<?php
  // set up SQL statement to output data
  $main_country_query = mysql_query("	SELECT DISTINCT country_id, country_title FROM photo_country");
  // Main Country Loop
  while( list( $country_id, $country_title ) = mysql_fetch_row( $main_country_query ) )
  {
    // thumbnail query
    if ( mysql_num_rows( $main_country_query ) > 0 )
    {
      $main_thumb_query = mysql_query("SELECT country_title, description_thumbnail FROM country, state, suburb, description WHERE country.country_id = state.country_id AND	 state.state_id = suburb.state_id AND suburb.suburb_id = description.suburb_id AND country.country_id = {$country_id} LIMIT 1");
      while( list( $country_title, $description_thumbnail ) = mysql_fetch_row( $main_thumb_query ) )
      {
        echo $country_title."\n";
        echo "<img src='".$description_thumbnail."' />\n";
           
        // set up SQL statement to count state categories
        $state_count_query = mysql_query("SELECT * FROM country, state WHERE country.country_id = state.country_id AND state.country_id = {$country_id}");
        $state_count = mysql_numrows($state_count_query);
        echo "states ".$state_count;
      }
    }
  }
?>

Can anyone clue me in?

The output is displaying all the thumbnails in relation with the first country_id and only displaying the first country_title repeatedly with the thumbnails.

Thanks in advance!

This question has already been answered. Start a new discussion instead.