DaniWeb Forum Index > Web Development > PHP

Display random thumbnail for it's category

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: May 2008

Posts: 23

Reputation: Besherek is an unknown quantity at this point

Solved Threads: 0

Besherek

Offline

Newbie Poster

Display random thumbnail for it's category

May 3rd, 2009

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.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?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;
      }
    }
  }
?>
<?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!

Last edited by Besherek; May 3rd, 2009 at 12:24 am.

Besherek Design