is there a way to bring back school details once but all the images

[deansaddigh]

I have this code but because of the way it has been done i cant put the images above the description.

 

// select school details based on school id					
			 $query = "SELECT image_school.image_id, image_school.school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.school_facts, school.general_info, school.school_facilities, image.image_id, image.path, image.name AS image_name
            FROM image_school
            JOIN school
            ON image_school.school_id = school.school_id
            JOIN image
            ON image_school.image_id = image.image_id
		WHERE school.school_id =" .$school_id;

			$result = mysql_query($query, $conn)
               or die ("Unable to perform query: " . mysql_error());   

			$i = 0;

			while($row = mysql_fetch_array($result))
			{
				if($i == 0)
				{

					echo '<h2>'. $row['school_name'].' '. $row['town'] .'</h2><br />';



					echo '<p class=important> Location:'. $row['town'].'</p>';

					echo '<p class=important> County:'. $row['county'].'</p>';

					echo '<p class=important> Region:'. $row['region'].'</p>';

					echo '<div id="schooldetailbox">';
					echo '<p class="important">General Information :</p>';
					echo '<p>'. $row['general_info'].'</p>';
					echo '</div>';

					echo '<div id="schooldetailbox">';
					echo '<p class="important">School Facilities:</p><hr />';
					echo '<p>'. $row['school_facilities'].'</p>';
					echo '</div>';

					echo '<div id="schooldetailbox">';
					echo '<p class="important">School Facts :</p><hr />';
					echo '<p>'. $row['school_facts'].'</p>';
					echo '</div>';



					$i ++;
					echo "<div id='schoolimage'>";

				}

				$imagename = $row ['image_name'];
				 $image = 'Admin/'.$row['path'] . '/' . $row ['image_name'];
				echo '<img class="schoolimage" src="'.$image.'"/>';


			}					
			echo '</div>';

            ?>    

is there away in the sql query to bring back the school details only once but all the images

[gizmola]

No.  When you join 2 tables you get a row for every time the tables intersect.  So if that means that you have a school with 10 images, you're going to get 1 x 10 rows, where all that will be different is the columns you pull from images.  You also have the image_school table, but I don't understand how that table figures in, although relationally the rules are the same.

 

So you have 2 choices:

 

-Make your loop code more sophisticated, so that you only emit a new school markup when the school_id changes.  This is fairly standard and easy enough to do

-Inside your loop do a 2nd query for images for that school and fetch those seperately.

 

 

 

[deansaddigh]

hi, i understand what you mean, and thanks.

 

Just to let you know to school_images table is a linker table and holds the image_id and the school_id