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

Getting the Query Results to Display Horizontally - 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

So now we get to the crux of our discussion. How do we get the query results to display horizontally?

Good question and one that took a day of tinkering to figure out. There were a few things we did know. First off, we decided how many images per row we wished to see. That value is stored in the $thumbcols variable. By dividing the $num variable that the query returns (number of rows in the result set) by the value of $thumbcols, we get the number of rows the html will display. In this case we just happen to have 11 images in the 'Huntington Ravine' album and since $thumbcols == 5, we need 3 rows. But, because the division returns a number closer to 2 than 3, the PHP round function rounds down to 2. By adjusting the variable definition to add 1 to the round results, we get around the closer to 2 than 3 problem.

But you say, what if the round function returns a whole number or a number closer to 3, won't there be an extra row added to the display? Not if we use a conditional to write out a table cell only if there is actually an image to display. This simple bit of code prints only what is needed to display all the images in the result set.

if(!empty($image)) {
// echo the actual data to the screen
print
 "<a href="enlarge_image.php?ID=$ID&CAT=$catID&AID=$albumID">
 <img src="../../$path/$location/$image" border="0" alt="">
 </a>
 <br clear="all">
 $caption";
 }
else {
 print '&nbsp;';
 }

Simple enough. Then at last we come to the code needed to put a result set row into a table cell and then write out five table cells per html row. We'll start with a bit of fail safe code in case we find ourselves arriving at http://localhost/gallery/show_pictures_h.php without a query string attached to the URL.

<table width="680" border="0" cellpadding="3" cellspacing="0">
<tr>
<td valign="top" align="center">
<!-- gallery logo -->
<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; line-height:120%;">
<div align="center">
<strong>

<?php
// just in case no query string is part of the URL request, the page will display an alert to pick a category
// which is always displayed as part of the page header
print
 (isset($NAME) ? $NAME. ' - (Click to Enlarge)' : 'Please select a category to view');
?>
   

Records in MySQL

</strong>
</div>
<?php
print '<table width="650" border="0" cellpadding="5" cellspacing="0">';
if(!empty($num)) {
 // if the query is successful print out a row of table beta
 print '
  <tr><td colspan="5" bgcolor="#cdcdcd">
   <strong>DATABASE RETURNED => # of Rows:'.$thumbrows. ' and # of Images:'.$num.'</strong>
   }
  </td>
 </tr>';
// function to display multiple table cells before starting a new row
function display_table() {
 // make variables available outside of the function
 global $num, $result, $thumbrows, $thumbcols;
 // format the number of rows to be printed using a loop
 for($r=1;$r<=$thumbrows;$r++) { 
 print '<tr>';
 // format the number of columns to be printed in each row using a 2nd for loop
 for($c=1;$c<=$thumbcols;$c++) {
 print '<td bgcolor="#999999" align="center" valign="top">';
 $row = @mysql_fetch_array($result); // break out the array of values from the returned query
  $ID = $row['imgID'];
  $catID = $row['catID'];
  $albumID = $row['albumID'];
  $location = $row['locID'];
  $albumNAME = $row['albumNAME'];
  $caption = stripslashes($row['imgTITLE']);
  $path = $row['thumbPATH'];
  $image = $row['thumbNAME'];
  $copyright = $row['copyright'];

if(!empty($image)) {
 // echo the actual data to the screen
 print"
  <a href="enlarge_image.php?ID=$ID&CAT=$catID&AID=$albumID">
   <img src="../../$path/$location/$image" border="0" alt="">
  </a>
  <br clear="all">
  $caption";
  }
 else {
  print '&nbsp;';
 }
  print '</td>';
  }
 print '</tr>';
 }
}
display_table(); // call function to do this wonderful thing
print '</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: