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