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

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



 
 
>>> 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: