[thelist] mySQL join query newb

ben morrison ben at benjer.demon.co.uk
Fri Sep 3 05:56:29 CDT 2004

this is my first attempt at this, so ive been mangling some code.

im trying to write an XML file with all the categorys and photos.

for the moment im trying to output all the photos within their category

two tables
gallery_category holds category_id and category_name

gallery_photos holds photo_id, photo_filename and photo_category.

all works fine, the photo_category is the category_id.

im sure there must be a better inner join or something as mine isnt 
working correctly:

	$result_array = array();
	$thumb_array = array();
		$result = mysql_query( "SELECT 
						FROM gallery_category as c
						LEFT JOIN gallery_photos as p ON p.photo_category = c.category_id
						GROUP BY c.category_id" );
		while( $row = mysql_fetch_array( $result ) )
			$result_array[] = $row[1].$row[2];
		mysql_free_result( $result );	

		foreach($result_array as $categorylink)
			$resultthumb = mysql_query( "SELECT 
photo_id,photo_caption,photo_filename FROM gallery_photos WHERE 
photo_category='".$counter."'" );
					while( $rows = mysql_fetch_array( $resultthumb ) )
					$thumb_array[] = $rows[2];
				mysql_free_result( $resultthumb );
		foreach($thumb_array as $thumbnail_link)
				$thumb_final .= $thumbnail_link."<br>";
				$thumb_final.=$counter." counter";
			$result_final .= $categorylink;

out of interest is  foreach() similar to a for (i=0;i<value;i++) loop?


More information about the thelist mailing list