|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
May 19th, 2004, 10:57 AM
|
|||
|
|||
|
Article Discussion: Displaying Multiple Records Per Row in a MySQL Query Result Set
Ever wonder how you can query a database and display the result set in something other than a one record per row layout? I've been trying to figure that out for some time and to date had not been able to find the answer courtesy of someone else's hard work. 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. The project I was working on for a client dealt with a photo database that involved four related tables, the schema for which follows in a dump from the popular open source mysql manager phpMyAdmin: I've added a bit of extra comment to each table to give you an idea what each table is designed to do.
Read the full article here: Displaying Multiple Records Per Row in a MySQL Query Result Set
|
|
#2
May 19th, 2004, 11:08 AM
|
||||
|
||||
|
nicely written article
i don't do php so i can't comment on that part of it (except to note in passing that nested looping in coldfusion is trivial compared to the code in this article) the sql looks fine i really have to wonder about the use of an html TABLE to display the results the article ends with ".... and remember, keep it simple" i totally agree with this use css to float the images and you can ditch the entire TABLE not only will that allow the page to be liquid, but it's way simpler html, to say nothing of the effect it would have on the php...
|
|
#3
May 24th, 2004, 11:54 AM
|
|||
|
|||
|
Few days ago I've been facing a similar chalange from wife. She had a task to create a simple web photo album with categories and ultimately she wanted her categories and thumbnails displayed in two columns.
Took me an hour or so to find a solution by using modulus (%) operator combined with If statement. Solution was much simpler than this one - if mysql_query row divided by number of columns gives modules different than 0 I would simply add HTML code for new column and insert record data, otherwise I would do the same but this time I would also add HTML row close tag and start a new row. Anyway, though a little bit complicated your solution seems nice to me only that it was achieved with different means.
|
|
#4
May 26th, 2004, 07:29 AM
|
|||
|
|||
|
Hum and now!
I have made 5 files and the db_config.php
index.php, index2.php, image_gallery.php, show_picture_h.php and show_picture_v.php index.php = with the show_picture_h and _v index2.php = with the enlarge_image.php image_gallery = Code:
<?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);
}
?>
show_picture_h.php = with the htmlspecialchars show_picture_v.php = little code Code:
<?
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 ' ';
}
?>
But I mis the enlarge_image.php version! Or am I making a mistake? And what file must be loaded first (The gallery version)? Where does the images go (/images/thumb, images/gallery). Thanxx Bjorn  neutcomp@hotmail.com (MSN) If thats easier
|
|
#5
May 27th, 2004, 03:51 PM
|
|||
|
|||
Another way
Uhm, I didn't look at this article too closely, but for what I saw, I felt that there's an easier way to do it, namely:
Code:
<?php
$pics = array('some kind of picture array' => 'filename.jpg', [etc...]);
echo '<table><tr>';
foreach ($pics as $label => $file) {
echo '<td>'.$label.'<br/><img src="'.$file.'"/></td>';
if ($i++%5 == 4 && count($pics) > $i) $output[] = '</tr><tr>';
}
echo '</tr></table>';
?>
... where the 5 in %5 is how many columns you want. You'd have to equip this with bells and whistles (and the database-to-array stuff, but that's not more than half a dozen additional rows of PHP code), but the columns thingy (if I've understood the topic of the article correctly) shouldn't be harder than this (and putting a colspan in the last td/filling with empty tds). Did I miss something? :) If so, please excuse a first-time poster. Last edited by Bergius : May 27th, 2004 at 03:56 PM. Reason: A bug or two ;)
|
|
#6
June 15th, 2004, 01:13 PM
|
|||
|
|||
|
Quote:
Sorry to not have included the necessary code for the enlarge_image.php, the nature of the article was the the multiple records per row. Either way, here's the code for the popup page. Code:
<?php
require("../../includes/db_config.php");
if(isset($_GET['CAT']) && ($_GET['AID'])) {
$CAT = htmlspecialchars($_GET['CAT']); // clean up query string
$AID = htmlspecialchars($_GET['AID']); // clean up query string
$imgID = htmlspecialchars($_GET['ID']); // clean up query string
$sqlP = "
SELECT images.imgID, images.locID, images.imgTITLE, images.popPATH, images.popNAME, images.copyright, albums.albumNAME
FROM albums, images
WHERE images.imgID = '$_GET[ID]' AND albums.albumID = images.albumID ";
$result = @mysql_query($sqlP, $connection) or die ("Could not execute your query");
$check_num=@mysql_num_rows($result);
?>
that's your header before the html and body tags. Then using your layout you'll need this code to display the enlarged image. Code:
<?php
if(!$check_num) {
echo "The image you requested is unavailable";
}
else {
while ($row = mysql_fetch_array($result)) {
$imgID = $row['imgID'];
$location = $row['locID'];
$albumNAME = stripslashes($row['albumNAME']);
$caption = stripslashes($row['imgTITLE']);
$path = $row['popPATH'];
$image = $row['popNAME'];
$copyright = $row['copyright'];
echo "<tr><td align=\"center\" bgcolor=\"#CFC9B4\"><a href=\"javascript:history.go(-1);\" class=\"smtext\">Back</a> <strong>$albumNAME</strong></td></tr>";
echo "<tr><td align=\"center\" bgcolor=\"#000000\" bgcolor=\"#000000\"><img src=\"../../$path/$location/$image\" border=\"0\" vspace=\"5\" hspace=\"5\"></td></tr>";
echo "<tr><td align=\"center\" bgcolor=\"#CFC9B4\">$caption</td></tr>";
echo "<tr><td class=\"smtext\" align=\"center\" bgcolor=\"#CFC9B4\">Copyright © $copyright ~ Your Name ~ All Rights Reserved</td></tr>";
}
}
}
?>
Then comes the rest of your layout and the closing body and html tags. Hope that helps Peter
|
|
| Viewing: Dev Shed Forums > Other > Development Articles > Article Discussion: Displaying Multiple Records Per Row in a MySQL Query Result Set |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
