Displaying Multiple Records Per Row in a MySQL Query Result Set - Getting Connected

(Page 2 of 5 )

The first bit of code necessary to get going is a connection script. It is a good idea to configure MySQL to grant the connection user a minimum set of rights. In this case we only need the right to query the database, so the user 'connect' has no rights in the mysql.user table and select rights only in the mysql.db table record that refers to the database 'photogalleryDB'. If someone sniffs out your password, what can they do? Not much with select rights. Take a look around like the rest of us... that's all.

   
<?php
// saved as the include file db_config.php
$db_name="photogalleryDB";
$connection = @mysql_connect("localhost", "connect", "yourpassword")
  or die ("Could not connect to mySQL server");
$db = @mysql_select_db($db_name, $connection)
  or die("Could not select your database.");
?>

Albums and Categories

Next up is the query that will select the available albums in a particular category. In this case we are interested in seeing gallery albums from the 'mountaineering' category. In our project, the CAT link is hard coded into the web page that deals with mountaineering and is just one of the menu links on that page. 

The URL to query and display the result might look something like this
http://localhost/gallery/image_gallery.php?CAT=3

The code needed to find the available albums in the chosen category is as follows.

<?php
require("../../includes/db_config.php");
if(isset($_GET['CAT'])) {
 // clean up query string
 $CAT = htmlspecialchars($_GET['CAT']); 
 // get image count in each album in the chosen category
 $sql ="
 SELECT
  categories.catNAME,
  albums.catID,
  albums.locID,
  albums.albumID,
  albums.albumNAME,
  albums.albumPIC,
  images.thumbPATH,
  location.locNAME,
 COUNT(images.albumID) AS NUM
 FROM categories, albums, images, location
 WHERE images.catID = '$CAT'
  AND images.albumID = albums.albumID
  AND albums.locID = location.locID AND categories.catID = '$CAT'
 GROUP BY albums.albumNAME
 ORDER BY albums.albumNAME ASC";  
  
$result = @mysql_query($sql, $connection);
}
? >

The results of the above query will select the available albums in a particular category. In this case we have asked the database to tell us what albums are available in the 'mountaineering' category where $CAT==3 The value of 3 represents a record in the 'categories' table called 'mountaineering'.

The resulting page gives us a choice of both vertical and horizontal display of the images of the 'Huntington Ravine' album. In our case there is only 1 album in which $CAT == 3. If there were more, the page would show additional rows or 'albums' to choose from. The html and php code to display this table looks like this:

<table width="680" border="0" cellpadding="3" cellspacing="0">
 <tr>
  <td valign="top">
   <img src="../../images/gallery/image_gallery_300x40.png" width="300" height="40" alt="">
   <br clear="all">
   <div style="margin-right:15px; margin-left:15px; padding:8px;">    
   <table width="650" border="0" cellpadding="3" cellspacing="0">
<?php
// some logic to deal with what ifs and people with bad intentions
  if(!isset($num)) {
   print '<tr><td align="center" colspan="4">Your query did not return any results</td></tr>';
 }
  if(!empty($num)) { 
  // build header row for dispay of images
?>
    <tr>
     <th>&nbsp;</th>
     <th>Gallery Name</th>
     <th>Category / Location</th>
     <th>Image Count</th>
    </tr>

<?php
 while ($row = @mysql_fetch_array($result)) {
  $albumNAME =stripslashes($row['albumNAME']);
  $AID =$row['albumID'];
  $CAT =$row['catID'];
  $LOC =$row['locID'];
  $count = $row['NUM'];
  $category = $row['catNAME'];
  $location = $row['locNAME'];
  $path = $row['thumbPATH'];
  $intropic = $row['albumPIC'];
  // solitary picture seen in available albums in each category
 
if(!empty($row)) {      
 $display_block = "
  <tr>
   <td><img src="../../$path/$LOC/$intropic" border="0"></td>
   <td width="335" align="left">
    <strong>$albumNAME<strong><br>
    <div style="margin-left:15px;">
     <a href="show_pictures_v.php?CAT=$CAT&AID=$AID&LOC=$LOC">Vertical Display</a>
    </div>
    <div style="margin-left:15px;">
     <a href="show_pictures_h.php?CAT=$CAT&AID=$AID&LOC=$LOC">Horizontal Display</a>
    </div>
   </td>
   <td width="165" align="center">$category / $location</td>
   <td width="150" align="center">Contains: <strong>$count<strong> images</td>
  </tr>";

print $display_block;  
  }
 }
}

?>
   </table>
   </div>
   <br clear="all">
  </td>
 </tr>
</table>

Next: Digging Deeper >>
 

More MySQL Articles
More By Peter Cole

Comments On: Displaying Multiple Records Per Row in a MySQL Query Result Set

>>> Be the FIRST to comment on this article!