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!