Displaying Multiple Records Per Row in a MySQL Query Result Set (Page 1 of 5 )
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.
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.
Design and Construct Database Tables
We will assume here you understand how to create a database in MySQL. You can do so from the command line after logging into the mysql server (not a bad idea if you are logged in remotely to a 'NIX' server) by using the CREATE DATABASE command. It is also possible to use the mysqladmin utility to create the database remotely. While its a good thing to get comfortable with the command line, there are numerous mysql management applications now available in both open source and commercial varieties that can create and manage mysql databases with a lot less pain and suffering. My personal favorite is phpmyadmin.
Below is a 'dump' or export of the table structure of the photogalleryDB we will be exploring in this article. Take a look at the table relationships and how the master table 'images' has hooks or match fields to the other tables by way of a key field. With the relationships in place, we can create query statements that display data based on more than one relationship.
In our example, we will be looking for all the images from the 'mountaineering' category that come from the 'huntington ravine' album and are 'located' in new Hampshire. Sound complicated? No ... not really. Read on.
# phpMyAdmin SQL Dump
# version 2.5.6
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Apr 14, 2004 at 08:12 AM
# Server version: 4.0.16
# PHP Version: 4.3.4
#
# Database : `photogalleryDB`
#
# --------------------------------------------------------
#
# Table structure for table `albums`
# Albums are unique to a category,
# for example a specific ice climbing location in New Hampshire, or
# a mountaineering location like the Icefields in the Canadian Rockies, Alberta
CREATE TABLE `albums` (
`albumID` int(5) NOT NULL auto_increment,
`albumNAME` varchar(100) NOT NULL default '',
`albumPIC` varchar(100) NOT NULL default '',
`catID` int(5) NOT NULL default '0',
`locID` int(5) NOT NULL default '0',
PRIMARY KEY (`albumID`),
UNIQUE KEY `albumNAME` (`albumNAME`),
UNIQUE KEY `albumPIC` (`albumPIC`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
# --------------------------------------------------------
#
# Table structure for table `categories`
# Acitivity type ... skiing, rock climbing, ice climbing etc
CREATE TABLE `categories` (
`ID` int(5) NOT NULL auto_increment,
`catID` int(5) NOT NULL default '0',
`catNAME` varchar(100) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `catID` (`catID`)
) TYPE=MyISAM AUTO_INCREMENT=15 ;
# --------------------------------------------------------
#
# Table structure for table `images`
# The guts of the database
CREATE TABLE `images` (
`imgID` int(5) NOT NULL auto_increment,
`catID` int(5) NOT NULL default '1',
`albumID` int(5) NOT NULL default '1',
`locID` int(5) NOT NULL default '1',
`imgTITLE` varchar(200) NOT NULL default '',
`thumbPATH` varchar(150) NOT NULL default '',
`thumbNAME` varchar(100) NOT NULL default '',
`popPATH` varchar(150) NOT NULL default '',
`popNAME` varchar(100) NOT NULL default '',
`copyright` int(4) NOT NULL default '0',
PRIMARY KEY (`imgID`),
UNIQUE KEY `popNAME` (`popNAME`),
UNIQUE KEY `thumbNAME` (`thumbNAME`)
) TYPE=MyISAM AUTO_INCREMENT=27 ;
# --------------------------------------------------------
#
# Table structure for table `location`
# State or Province
CREATE TABLE `location` (
`ID` int(5) NOT NULL auto_increment,
`locID` int(5) NOT NULL default '0',
`locNAME` varchar(100) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `locID` (`locID`)
) TYPE=MyISAM AUTO_INCREMENT=26 ;
The concept involved in the table schema is to try to a certain degree to 'normalize' the database structure. What that means in a nutshell is that we don't want to end up with a database that has redundant data found in more than one place.
The table structure is independent of the logic that is used to display the results of queries necessary to add, edit, view and delete records. In this article, we are only interested in the code that will display the results of a SELECT query.
There has been more than enough conversation in forums and articles past about the advantages or lack there-of of including your images (binary data) in the database tables themselves. It boils down to personal perference. My personal feeling is that your images are best keep out of the database. Use the data in your tables to 'reference' the location of the folder on your server or hard drive where the images can be found.
Next: Getting Connected >>
More MySQL Articles
More By Peter Cole
/ 43 
