Calling each id from mysql

[bigheadedd]

Hi!

 

I'm trying to figure out how I can query my database to show a selection of images.

I have a database, which contains the photo id, caption and filename, which are grouped by a category.

 

$result = mysql_query( "SELECT photo_id,photo_caption,photo_filename FROM art_photos WHERE photo_category='".addslashes($cat)."' ORDER BY photo_id DESC" );

 

This can bring in the photos of when $cat equals a number (say 4). I however, need to bring in all the photos from all the categories. Now I can remove the WHERE statement, but I need to group them.

 

SO rather than..

 

Image 1(Cat1), Image 2(Cat1), Image 3(Cat2)

 

etc

 

I need:

 

Image 1(Cat1), Image2(Cat1)

Image 3(Cat2)

 

(So be able to put them into seperate divs for example.

I've been trying to do it with a simple loop, but to no avail. I thought about putting a counter and +1 each time, but I need to obviously skip making a new div if the cat doesn't exist (So If I have Cat1, Cat2, Cat5, Cat6) etc. It would need to skip 3 and 4.

 

Any help would be hugely grateful!

 

Thanks

E

[Muddy_Funster]

got your table structure there?

[siric]

You would need to use an array for the categories.

 

Find out how many categories you have and define an array -

 

$cat = array();

for ($count = 1; $count < $max_number_of_cats+1; $count++) {

      $result = mysql_query( "SELECT photo_id,photo_caption,photo_filename FROM art_photos WHERE photo_category='".addslashes($cat[$count])."' ORDER BY photo_id DESC" );
      //cat[$count] here would equate to cat1 in the first instance, cat2, etc.



      if ($result) {   //If there is a result for the cat search, then display the divs
          ...
           //make div and display
         ...
     }
}

 

 

[bigheadedd]

Yep:

 

photo_id  bigint(20)  No     

photo_filename varchar(25) Yes  NULL 

photo_caption text Yes  NULL 

photo_category bigint(20) No  0 

photo_colour varchar(50) No

 

Thanks

 

E

[bigheadedd]

Hi Siric,

 

thanks for the reply! I can see what your trying, and I think thats what I need, however I'm a little stuck on how to really implement it (Sorry, bit of a noob here!).

 

I used to have this:

/*while( $row = mysql_fetch_array( $result ) )
		{
			$result_array[] = "<img src='../".$artwork_dir."/tb_".$row[2]."' 
			border='0' alt='".$row[1]."' />";
		}
		mysql_free_result( $result );	

		$result_final = "<li>\n";

		foreach($result_array as $thumbnail_link)
		{
			if($counter == $number_of_thumbs_in_row)
			{	
				$counter = 1;
				$result_final .= "\n</li>\n<li>\n";
				$cat = 1;
			}
			else
			$counter++;

			$result_final .= "\t".$thumbnail_link."\n";
		}

		if($counter)
		{
			if($number_of_photos_in_row-$counter)
		$result_final .= "\t<td colspan='".($number_of_photos_in_row-$counter)."'> </td>\n";

			$result_final .= "</li>";
		}
	}

 

To display images..

 

I've tried your code..

 

			

	$resultcheck = mysql_query( "SELECT photo_id,photo_caption,photo_filename FROM art_photos" );
	$nr = mysql_num_rows( $resultcheck );

	if( empty( $nr ) )
	{
		$result_final = "\t<tr><td>No Photos Found!</td></tr>\n";
	}
	else
	{
	$cat = array();
	for ($count = 1; $count < $max_number_of_cats+1; $count++) {
	$result = mysql_query( "SELECT photo_id,photo_caption,photo_filename FROM art_photos WHERE photo_category='".addslashes($cat[$count])."' ORDER BY photo_id DESC" );
	  //cat[$count] here would equate to cat1 in the first instance, cat2, etc.
	  if ($result) {   //If there is a result for the cat search, then display the divs
		  while( $row = mysql_fetch_array( $result ) )
			{
			echo "<div>";
			echo "<img src=\"../".$artwork_dir."/tb_".$row[2]."\"/>";
			echo "</div>";
			}
	}
	}
	}

 

However, it doesn't seem to be working.

Thanks in advance

E

[Muddy_Funster]

What's not working about it? is it not selecting anything from the database? 'Cause it may be that you need to put some spaces after your commas in the SELECTs.  Also, at the end of each "mysql_query()" line, before the " ; " include the following:

or die (mysql_error())

[bigheadedd]

Its really strange!

Its not even outputting a simple echo statement within the while clause.

Theres no mysql error either, the page functions properly, but just not this section!

 

Any ideas?

 

Thanks

[Muddy_Funster]

you could try and change the if statement to [php

if(!empty($result)){

[/code] and see if that works any better (or even comment it out for testing.

[bigheadedd]

Nope, still doesn't work!

I've tried removing the ! too and still nothing.

Anything within the for statement doesn't seem to work?

[bigheadedd]

Just had a bit more of a play around, and its now anything within the while statement doesn't seem to work?

Does it have something to do with the $max_number_of_cats?

I've set it to 5 for a test, and if I put an echo statement within the if, it works, but the while statement doesn't.

[bigheadedd]

Right.. I've had a bit more luck, but I'm still pretty stuck..

$max_number_of_cats = 10;
	for ($count = 1; $count < $max_number_of_cats+1; $count++) 
		{
		$result = mysql_query( "SELECT photo_id, photo_caption, photo_filename FROM art_photos WHERE photo_category='".addslashes($cat[$count])."'" ) or die(mysql_error());
		  //cat[$count] here would equate to cat1 in the first instance, cat2, etc.
		  if (!empty($result)) 
			{   //If there is a result for the cat search, then display the divs
			while( $row = mysql_fetch_array( $result ) )
				{
				$result_array[] = "<a href='manage_art.php?cid=$cid&pid=".$row[0]."'><img src='../".$artwork_dir."/tb_".$row[2]."' 
				border='0' alt='".$row[1]."' /></br></a></br></br>";
				}
			mysql_free_result( $result );			
			foreach($result_array as $thumbnail_link)
			{
				echo $thumbnail_link;
			}
		}	}

	}

 

Thats what i've got so far. At the moment, it doesn't seem like it translate the data down?

Am I missing something really simple here?

 

Thanks!

 

E

[siric]

Ok,

 

Went through it in detail and this works.  Don't even need arrays - my bad.

 

$max_number_of_cats = 10;

for ($count = 1; $count < $max_number_of_cats+1; $count++) {  //start number for count is the first cat number

    $sql = "SELECT * FROM photo WHERE photo_category='$count' ORDER BY photo_id DESC";
    $result= mysql_query($sql);
$num_rows = mysql_num_rows($result);
    	
	if ($num_rows > 0) {   //If there is a result for the cat search, then display the divs
	//Start div here

	for ($i=0; $i<$num_rows;$i++) {
		$photo_id=mysql_result($result, $i, "photo_id");
		$photo_caption=mysql_result($result, $i, "photo_caption");
		$photo_filename=mysql_result($result, $i, "photo_filename");
		$photo_category=mysql_result($result, $i, "photo_category");
		$photo_colour=mysql_result($result, $i, "photo_colour");

		print "Photo - $photo_id - $photo_caption - $photo_filename - $photo_color - $photo_category<br/>";
         
	}
print "Cat Change<br/><br/>";  //end your div here
}
}

 

Try that and let me me know.

[bigheadedd]

Thats awsome! Thankyou so much. It works perfectly!

I am however, now stuck on a really small thing.

I forgot that the category_name, is from the table art_category, which I also need to pull up.

I played around with the code you provided and managed to get a semi working solution!

 

$max_number_of_cats = 1000;

	for ($count = 1; $count < $max_number_of_cats+1; $count++) 
		{  //start number for count is the first cat number
		$sql = "SELECT * FROM art_photos WHERE photo_category='$count' ORDER BY photo_id ASC";
		$sql2 = "SELECT * FROM art_category WHERE category_id='$count'";

		$result= mysql_query($sql);
		$result2= mysql_query($sql2);

		$num_rows = mysql_num_rows($result);

		if ($num_rows > 0) 
		{   //If there is a result for the cat search, then display the divs
		echo "<div>";
			for ($i=0; $i<$num_rows;$i++) 
			{
			$photo_id=mysql_result($result, $i, "photo_id");
			$photo_caption=mysql_result($result, $i, "photo_caption");
			$photo_filename=mysql_result($result, $i, "photo_filename");
			$photo_category=mysql_result($result, $i, "photo_category");
			$photo_colour=mysql_result($result, $i, "photo_colour");
			$category_name=mysql_result($result2, $i, "category_name");
			echo "<a href=\"".$category_name."/".$photo_category."/".$photo_id.".html\"><img src=\"../".$artwork_dir."/tb_".$photo_filename."\"></a>";
			}
		echo "</div>";
		}

 

The really strange thing is though, is that the first picture has category_name in the link, but all of the images following that don't..

I think its due to the $count, going past it, however I'm not quite sure what I could do for a fix.

 

Again, thanks a lot for helping me out on this one!

 

E

[bigheadedd]

Nevermind.. I've just managed to figure it out.

I just had to put the

$category_name=mysql_result($result2, $i, "category_name");

line, outside of the second for statement.

 

Again, thankyou so much for your help. Your a star!

 

Many many thanks.

 

E