Home arrow MySQL arrow Page 2 - Displaying Multiple Records Per Row in a MySQL Query Result Set

Getting Connected - MySQL

Ever wonder how you can query a database and display the result set in something other than a one record per row layout? Keeping in mind that the simple answer is always the best one, I found a solution that keeps to that premise and solves an issue that seemed impossible not long ago. In a word, the answer, lies in the loop. An additional one, that is. But first, lets define and explain the general look and functionality of our project.

TABLE OF CONTENTS:
  1. Displaying Multiple Records Per Row in a MySQL Query Result Set
  2. Getting Connected
  3. Digging Deeper
  4. Getting the Query Results to Display Horizontally
  5. The Look we want and the Secret that gets you there
By: Peter Cole
Rating: starstarstarstarstar / 47
May 19, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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. 

Records in MySQL

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'.

Records in MySQL

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>




 
 
>>> More MySQL Articles          >>> More By Peter Cole
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: