Sorting Pictures By Category.

[n00bert]

Well what I Have right now just displays all my thumbnails. I want it to group the thumbnails By their category and display the category name above that group. Any help is appreciated.

 

My Database looks like so.

 

+-----------------+-----------------+------------------+---------------+------------------+

|Table Name         | Fields-------------------------------------------------------------------

+-----------------+-----------------+------------------+---------------+------------------+

|gallery_category  | category_id        | category_name    |

+-----------------+-----------------+------------------+---------------+----------------- +

|gallery_photos     | photo_id            | photo_filename     | photo_caption | photo_category   |

+-----------------+-----------------+------------------+---------------+------------------+

 

Here is my code.

^<?php

^{include("includes/gallerysettings.php");}

 

^{// initialization}

^{$result_array = array();}

^{$counter = 0;}

 

 

^{// Thumbnail Listing}

 

 

^{$result = mysql_query( "SELECT photo_id,photo_caption,photo_filename FROM gallery_photos" );}

^{$nr = mysql_num_rows( $result );}

 

^{if( empty( $nr ) )}

^{

^{$result_final = "\t<tr><td>No Category found</td></tr>\n";}

^}

^else

^{

^{while( $row = mysql_fetch_array( $result ) )}

^{

^{$result_array[] = "<a href='".$images_dir."/".$row[2]."' rel='lightbox[gallery]' title='".$row[1]."'><img src='".$images_dir."/tb_".$row[2]."' border='0' alt='".$row[1]."' /></a>";}

^}

^{mysql_free_result( $result );}

 

^{$result_final = "<tr>\n";}

 

^{foreach($result_array as $thumbnail_link)}

^{

^{if($counter == $number_of_thumbs_in_row)}

^{

^{$counter = 1;}

^{$result_final .= "\n</tr>\n<tr>\n";}

^}

^else

^$counter++;

 

^{$result_final .= "\t<td>".$thumbnail_link."</td>\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 .= "</tr>";}

^}

^}

 

^{// Final Output}

^{echo <<<__HTML_END}

 

^{<table width='100%' border='0' align='center' style='width: 100%;'>}

^{$result_final}

^</table>

 

^__HTML_END;

^?>

 

 

and keep in mind...i'm still not too hot with this stuff. Just about everything in this i've learned from a tutorial.

[cooldude832]

are you linking gallery_photos to gallery_categories using full text

 

what are values of gallery_photots.phoot_category look like?

[n00bert]

Well I haven't linked them....the code up there is everything I have. I don't know where to go from there. The values are auto_increment. So like 1 2 3 4. You know.

[cooldude832]

well you need to JOIN in categories you query looks like

SELECT
gallery_photos.photo_id as photo_id,
gallery_photos.photo_filename as filename,
gallery_photos.photo_caption as caption,
gallery_photos.photo_cateogry as photo_cat,
gallery_category.category_id,
gallery_category.category_name as category

FROM
`gallery_photos`

LEFT JOIN `gallery_category` ON(gallery_category.category_id = gallery_photos.photo_category)

GROUP BY gallery_photos.photo_id

ORDER BY gallery_category.category_name

 

[n00bert]

That causes me to get this.

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

[cooldude832]

error check your queries

<?php
$q = "
SELECT
gallery_photos.photo_id as photo_id,
gallery_photos.photo_filename as filename,
gallery_photos.photo_caption as caption,
gallery_photos.photo_cateogry as photo_cat,
gallery_category.category_id,
gallery_category.category_name as category

FROM
`gallery_photos`

LEFT JOIN `gallery_category` ON(gallery_category.category_id = gallery_photos.photo_category)

GROUP BY gallery_photos.photo_id

ORDER BY gallery_category.category_name

";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);

[n00bert]

ok now that just outputs the $q variable.

 

SELECT gallery_photos.photo_id as photo_id, gallery_photos.photo_filename as filename, gallery_photos.photo_caption as caption, gallery_photos.photo_cateogry as photo_cat, gallery_category.category_id, gallery_category.category_name as category FROM `gallery_photos` LEFT JOIN `gallery_category` ON(gallery_category.category_id = gallery_photos.photo_category) GROUP BY gallery_photos.photo_id ORDER BY gallery_category.category_name

 

I think i'm doing something wrong here.

 

I'm still kinda dumb to this stuff.