Hiya,
I'm writing some code for a web site I'm working on at the moment, and I'm
wondering if I can simplify a task:
I've got a series of tables: items, books, video, data & music (it's a
simply library web site).
Every book, dvd, etc has an entry in it's selected table, and a record for
each copy held in items (therefore each item can have more than one copy).
The items table has a field called 'type' (enum('book', 'video', 'data',
'music')) which tells which table the data about the item is held.
At the moment I do a general SELECT statement on the items table, then in a
for loop, go though each record and run another SELECT statement to get the
data out of the items type table. What I want to know, is there a way to
get the data from all the tables in one go by selecting which table the
SELECT statement should look up dynamically for each row? Or should I just
keep doing what I'm doing now?
Database:
CREATE TABLE `items` (
`code` mediumint(7) unsigned zerofill NOT NULL auto_increment,
`type` enum('books','video','data','music') NOT NULL default 'books',
`id` mediumint(7) unsigned zerofill NOT NULL default '0000000',
`length` tinyint(3) unsigned zerofill NOT NULL default '000',
`status` enum('avaliable','onloan','reserved','unavaliable') NOT NULL
default 'avaliable',
`date` date NOT NULL default '0000-00-00',
`user` mediumint(6) unsigned zerofill NOT NULL default '000000',
`charge` decimal(4,2) NOT NULL default '0.00',
`fine` decimal(4,2) NOT NULL default '0.00',
`finerate` tinyint(3) unsigned zerofill NOT NULL default '000',
PRIMARY KEY (`code`),
UNIQUE KEY `code` (`code`),
KEY `code_2` (`code`,`type`,`id`,`status`)
) TYPE=MyISAM AUTO_INCREMENT=1000000;
CREATE TABLE `books` (
`id` mediumint(7) unsigned zerofill NOT NULL auto_increment,
`title` varchar(150) NOT NULL default '',
`authors` varchar(150) NOT NULL default '',
`publisher` varchar(75) NOT NULL default '',
`classmark` varchar(15) NOT NULL default '',
`type` enum('fiction','non-fiction','audio') NOT NULL default 'non-fiction',
`edition` int(2) unsigned NOT NULL default '0',
`published` date NOT NULL default '0000-00-00',
`isbn` varchar(13) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`,`title`,`authors`,`classmark`)
) TYPE=MyISAM AUTO_INCREMENT=1000000;
CREATE TABLE `data` (
`id` mediumint(7) unsigned zerofill NOT NULL auto_increment,
`title` varchar(150) NOT NULL default '',
`type` enum('dvd-rom','cd-rom') NOT NULL default 'cd-rom',
`released` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`,`title`)
) TYPE=MyISAM AUTO_INCREMENT=1000000;
CREATE TABLE `music` (
`id` mediumint(7) unsigned zerofill NOT NULL auto_increment,
`title` varchar(150) NOT NULL default '',
`artist` varchar(150) NOT NULL default '',
`type` enum('mini-disc','cd','cassette') NOT NULL default 'cd',
`released` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`,`title`,`artist`)
) TYPE=MyISAM AUTO_INCREMENT=1000000;
CREATE TABLE `video` (
`id` mediumint(7) unsigned zerofill NOT NULL auto_increment,
`title` varchar(150) NOT NULL default '',
`actors` varchar(250) NOT NULL default '',
`type` enum('dvd','vhs') NOT NULL default 'vhs',
`released` date NOT NULL default '0000-00-00',
`classification` enum('E','U','PG','12','15','18','R18') NOT NULL
default 'U',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`,`title`,`actors`,`type`)
) TYPE=MyISAM AUTO_INCREMENT=1000000;
Code (PHP):
$query = $db->query("SELECT * FROM items WHERE user='".$_GET["user"]."'");
if ($db->num_rows($query) > 0) {
//Go though each item and display details
for ($j=0;$j<$db->num_rows($query);$j++) {
//Get Item data from Query
$i = $db->results($j, $query);
//Get remaining information about the item from it's type table
$item = $db->query("SELECT * FROM ".$i["type"]." WHERE
id='".$i["id"]."'");
$t = $db->results(0, $item);
$db->free($item);
//Output Data
}
}
Thanks for your time!
--
Jonathan Wright
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
