[ CRC Cards DB ] [ Web DB ] [ Bible Ref DB ] [ Category tree ]
CRC Card SQL
drop database crccards; create database crccards; use crccards; create table cards ( classid int primary key not null, classname varchar(50) not null, superclassid int null, subclassid int null, rclist int null ); create index cardsIdx on cards(classid); create table rclists ( rcid int primary key not null, rclistid int not null, responsibility varchar(30) not null, collaboratorid int null ); create index rclistsIdx on rclists(rcid);
Web counter SQL
/* Mascon Dump Source Host: blinky Source Server Version: 3.23.24-beta-debug Source Database: web Date: 2001/01/18 16:54:41 */ use blinky ; #---------------------------- # Table structure for categories #---------------------------- drop table if exists categories; create table categories ( id int(10) unsigned not null auto_increment, name varchar(50) not null, subcategoryof int(11), securityLevel int(11) not null default '0', url text, unique id (id)) type=MyISAM; #---------------------------- # Records for table categories #---------------------------- lock tables categories write ; insert into categories (id,name,subcategoryof,securityLevel,url) values (1, 'Auctions', null, 0, 0x687474703A2F2F7777772E656261792E636F6D), (2, 'Kids', null, 0, 0x687474703A2F2F7777772E6D61737465726C696E78312E6E65742F6B6964732E68746D6C); unlock tables ; #---------------------------- # Table structure for pagecounters #---------------------------- drop table if exists pagecounters; create table pagecounters ( id int(11) not null default '0', val int(11) not null default '0', unique id_idx (id)) type=MyISAM; #---------------------------- # No records for table pagecounters #---------------------------- #---------------------------- # Table structure for pc2 #---------------------------- drop table if exists pc2; create table pc2 ( id int(11) not null default '0', cnt int(11) not null default '0', unique id_idx (id)) type=MyISAM; #---------------------------- # Records for table pc2 #---------------------------- lock tables pc2 write ; insert into pc2 (id,cnt) values (0, 2), (1, 0); unlock tables ; #---------------------------- # Table structure for webpages #---------------------------- drop table if exists webpages; create table webpages ( category int(11) unsigned, content text not null, id int(10) unsigned not null auto_increment, unique IDX_id (id)) type=MyISAM; #---------------------------- # Records for table webpages #---------------------------- lock tables webpages write 4 insert into webpages (category,content,id) values (0, 0x3C48544D4C3E3...F48544D4C3E, 1), (2, 0x3C21444F43545...C3E0D0A , 2)4 unlock tables 4
Bible Reference DB
This is a Thompson-chain style reference system with categories & category numbers.Build your own personal study notes.
/* Mascon Dump Source Host: blinky Source Server Version: 3.23.24-beta-debug Source Database: bibleref Date: 2001/01/18 16:54:11 */ use bibleref ; #---------------------------- # Table structure for booknames #---------------------------- drop table if exists booknames; create table booknames ( bookid int(11) not null default '0', bookname varchar(15) not null, otnt varchar(50) not null, bookname2 char(3) not null, booknamebibtxt char(3) not null, bookname3b char(3) not null, booknum int(11) not null default '0', booknametc varchar(6) not null, unique booknum_idx (bookid)) type=MyISAM; #---------------------------- # Records for table booknames #---------------------------- lock tables booknames write ; insert into booknames (bookid,bookname,otnt,bookname2,booknamebibtxt,bookname3b,booknum,booknametc) values (0, 'Genesis', 'o', 'Ge', 'GEN', 'Gen', 1, 'Ge.'), (1, 'Exodus', 'o', 'Ex', 'EXO', 'Exo', 2, 'Ex.'), (2, 'Leviticus', 'o', 'Le', 'LEV', 'Lev', 3, 'Le.'), (3, 'Numbers', 'o', 'Nu', 'NUM', 'Num', 4, 'Nu.'), (4, 'Deuteronomy', 'o', 'De', 'DEU', 'Deu', 5, 'De.'), (5, 'Joshua', 'o', 'Jo', 'JOS', 'Jos', 6, 'Jos.'), (6, 'Judges', 'o', 'Jg', 'JDG', 'Jdg', 7, 'Jud.'), (7, 'Ruth', 'o', 'Ru', 'RTH', 'Rut', 8, 'Ru.'), (8, '1 Samuel', 'o', '1S', 'SA1', '1Sa', 9, '1 S.'), (9, '2 Samuel', 'o', '2S', 'SA2', '2Sa', 10, '2 S.'), (10, '1 Kings', 'o', '1K', 'KI1', '1Ki', 11, '1 K.'), (11, '2 Kings', 'o', '2K', 'KI2', '2Ki', 12, '2 K.'), (12, '1 Chronicles', 'o', '1Ch', 'CH1', '1Ch', 13, '1 Chr.'), (13, '2 Chronicles', 'o', '2Ch', 'CH2', '2Ch', 14, '2 Chr.'), (14, 'Ezra', 'o', 'Ez', 'EZR', 'Ezr', 15, 'Ezr.'), (15, 'Nehemiah', 'o', 'Ne', 'NEH', 'Neh', 16, 'Neh.'), (16, 'Esther', 'o', 'Es', 'EST', 'Est', 17, 'Est.'), (17, 'Job', 'o', 'Jb', 'JOB', 'Job', 18, 'Jb.'), (18, 'Psalms', 'o', 'Ps', 'PSA', 'Psa', 19, 'Ps.'), (19, 'Proverbs', 'o', 'Pr', 'PRO', 'Pro', 20, 'Pr.'), (20, 'Ecclesiates', 'o', 'Ec', 'ECC', 'Ecc', 21, 'Ec.'), (21, 'Song of Solomon', 'o', 'So', 'SON', 'Son', 22, 'Song'), (22, 'Isaiah', 'o', 'Is', 'ISA', 'Isa', 23, 'Is.'), (23, 'Jeremiah', 'o', 'Je', 'JER', 'Jer', 24, 'Je.'), (24, 'Lamentations', 'o', 'La', 'LAM', 'Lam', 25, 'Lam.'), (25, 'Ezekiel', 'o', 'Ek', 'EZE', 'Eze', 26, 'Eze.'), (26, 'Daniel', 'o', 'Da', 'DAN', 'Dan', 27, 'Da.'), (27, 'Hosea', 'o', 'Ho', 'HOS', 'Hos', 28, 'Ho.'), (28, 'Joel', 'o', 'Jl', 'JOE', 'Joe', 29, 'Joel'), (29, 'Amos', 'o', 'Am', 'AMO', 'Amo', 30, 'Am.'), (30, 'Obadiah', 'o', 'Ob', 'OBA', 'Oba', 31, 'Obad.'), (31, 'Jonah', 'o', 'Ja', 'JON', 'Jna', 32, 'Jona.'), (32, 'Micah', 'o', 'Mi', 'MIC', 'Mic', 33, 'Mi.'), (33, 'Nahum', 'o', 'Na', 'NAH', 'Nah', 34, 'Na.'), (34, 'Habakkuk', 'o', 'Ha', 'HAB', 'Hab', 35, 'Hab.'), (35, 'Zephaniah', 'o', 'Zp', 'ZEP', 'Zep', 36, 'Zep.'), (36, 'Haggai', 'o', 'Hg', 'HAG', 'Hag', 37, 'Hag.'), (37, 'Zechariah', 'o', 'Zc', 'ZEC', 'Zec', 38, 'Zec.'), (38, 'Malachi', 'o', 'Ma', 'MAL', 'Mal', 39, 'Mal.'), (39, 'Matthew', 'n', 'Mt', 'MAT', 'Mat', 40, 'Mt.'), (40, 'Mark', 'n', 'Mk', 'MAR', 'Mar', 41, 'Mk.'), (41, 'Luke', 'n', 'Lu', 'LUK', 'Luk', 42, 'Lu.'), (42, 'John', 'n', 'Jn', 'JOH', 'Joh', 43, 'Jn.'), (43, 'Acts', 'n', 'Ac', 'ACT', 'Act', 44, 'Ac.'), (44, 'Romans', 'n', 'Ro', 'ROM', 'Rom', 45, 'Rm.'), (45, '1 Corinthians', 'n', '1Co', 'CO1', '1Co', 46, '1 Co.'), (46, '2 Corinthians', 'n', '2Co', 'CO2', '2Co', 47, '2 Co.'), (47, 'Galatians', 'n', 'Ga', 'GAL', 'Gal', 48, 'Ga.'), (48, 'Ephesians', 'n', 'Ep', 'EPH', 'Eph', 49, 'Ep.'), (49, 'Philippians', 'n', 'Ph', 'PHI', 'Phi', 50, 'Ph.'), (50, 'Colossians', 'n', 'Co', 'COL', 'Col', 51, 'Col.'), (51, '1 Thessalonians', 'n', '1Th', 'TH1', '1Th', 52, '1 Th.'), (52, '2 Thessalonians', 'n', '2Th', 'TH2', '1Th', 53, '2 Th.'), (53, '1 Timothy', 'n', '1Ti', 'TI1', '1Ti', 54, '1 Ti.'), (54, '2 Timothy', 'n', '2Ti', 'TI2', '2Ti', 55, '2 Ti.'), (55, 'Titus', 'n', 'Ti', 'TIT', 'Tit', 56, 'Tit.'), (56, 'Philemon', 'n', 'Pm', 'PHM', 'Phm', 57, 'Phm.'), (57, 'Hebrews', 'n', 'He', 'HEB', 'Heb', 58, 'He.'), (58, 'James', 'n', 'Ja', 'JAM', 'Jam', 59, 'Ja.'), (59, '1 Peter', 'n', '1P', 'PE1', '1Pe', 60, '1 Pe.'), (60, '2 Peter', 'n', '2P', 'PE2', '2Pe', 61, '2 Pe.'), (61, '1 John', 'n', '1J', 'JO1', '1Jo', 62, '1 Jo.'), (62, '2 John', 'n', '2J', 'JO2', '2Jo', 63, '2 Jo.'), (63, '3 John', 'n', '3J', 'JO3', '3Jo', 64, '3 Jo.'), (64, 'Jude', 'n', 'Jd', 'JUD', 'Jud', 65, 'Jude'), (65, 'Revelation', 'n', 'Re', 'REV', 'Rev', 66, 'Re.'); unlock tables ; #---------------------------- # Table structure for refs #---------------------------- drop table if exists refs; create table refs ( refid int(11) not null default '0', bookid smallint(6) not null default '0', chapfrom smallint(6) not null default '0', versefrom smallint(6) not null default '0', chapto smallint(6), verseto smallint(6), pplistid int(11), sidereflistid int(11), grp int(11), unique grp_idx (refid, grp)) type=MyISAM; #---------------------------- # Records for table refs #---------------------------- lock tables refs write ; insert into refs (refid,bookid,chapfrom,versefrom,chapto,verseto,pplistid,sidereflistid,grp) values (0, 45, 1, 30, null, null, null, null, 0), (1, 45, 6, 11, null, null, null, null, 0), (2, 51, 0, 0, null, null, null, null, null), (4, 51, 5, 23, null, null, null, null, 0), (5, 52, 2, 13, null, null, null, null, 0), (6, 54, 2, 21, null, null, null, null, 0), (7, 57, 2, 11, null, null, null, null, 0), (8, 57, 8, 2, null, null, null, null, 0), (9, 57, 9, 13, 9, 14, null, null, 0), (10, 57, 10, 10, null, null, null, null, 0), (11, 57, 10, 29, null, null, null, null, 0), (12, 57, 13, 12, null, null, null, null, 0), (13, 59, 1, 2, null, null, null, null, 0), (14, 64, 1, 1, null, null, null, null, 0); unlock tables ; #---------------------------- # Table structure for subjects #---------------------------- drop table if exists subjects; create table subjects ( subjectid int(11) not null auto_increment, subject varchar(100) not null, subsubject int(11), seealsolist int(11), reflist int(11), unique subjectid_idx (subjectid)) type=MyISAM; #---------------------------- # Records for table subjects #---------------------------- lock tables subjects write ; insert into subjects (subjectid,subject,subsubject,seealsolist,reflist) values(1, 'Sanctification', null, null, 0); unlock tables ;
Category Tree
This is a multiple-root category tree. A sample tree has been provided. The PHP code has not been debugged as of 1/30/2006.
/* c f-b g | h | i-c-a j | k | l-d m ----n */ -- MySQL Administrator dump 1.4 -- -- ------------------------------------------------------ -- Server version 5.0.18-nt-max /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Create schema jmichae3 -- use dbo ; -- -- Table structure for table `dbo`.`categories` -- DROP TABLE IF EXISTS `categories`; CREATE TABLE `categories` ( `cat_id` int(10) unsigned NOT NULL auto_increment, `category` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`cat_id`), UNIQUE KEY `ix_ccat_id` (`cat_id`), INDEX `ix_ccat_id` (`category`) ) ENGINE=InnoDB; -- -- Dumping data for table `dbo`.`categories` -- LOCK TABLES `categories` WRITE ; INSERT INTO `categories` (`cat_id`,`category`) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d'), (5,'e'), (6,'f'), (7,'g'), (8,'h'), (9,'i'), (10,'j'), (11,'k'), (12,'l'), (13,'m'), (14,'n'); UNLOCK TABLES ; -- -- Table structure for table `dbo`.`categoryroots` -- DROP TABLE IF EXISTS `categoryroots`; CREATE TABLE `categoryroots` ( `root_id` int(10) NOT NULL auto_increment COMMENT 'ignore', `cat_id` int(10) NOT NULL default '0' COMMENT 'copy of cat_id from categories table', PRIMARY KEY (`root_id`), UNIQUE KEY `ix_crcat_id` (`cat_id`), FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB; -- -- Dumping data for table `dbo`.`categoryroots` -- LOCK TABLES `categoryroots` WRITE ; INSERT INTO `categoryroots` (`cat_id`) VALUES (1), (14); UNLOCK TABLES ; -- -- Table structure for table `dbo`.`subcategories` -- DROP TABLE IF EXISTS `subcategories`; CREATE TABLE `subcategories` ( `sub_id` int(10) NOT NULL auto_increment COMMENT 'used in case we need to delete a specific row', `cat_id` int(10) NOT NULL default '0' COMMENT 'copy of cat_id from categories table', `subcat_id` int(10) NOT NULL default '0' COMMENT 'cat_id of subcategory', PRIMARY KEY (`root_id`), UNIQUE KEY `ix_crcat_id` (`cat_id`), -- FOREIGN KEY (`subcat_id`) REFERENCES `categories` (`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB; -- -- Dumping data for table `dbo`.`subcategories` -- LOCK TABLES `subcategories` WRITE ; INSERT INTO `subcategories` (`cat_id`,`subcat_id`) VALUES( 1,2),( 1,3),( 1,4),(2,5), (2,6), (2,7), (3,8), (3,9), (3,10), (4,11), (4,12), (4,13); UNLOCK TABLES ;
And now for the PHP code that goes with it:
<?php
function traverse_tree($level=1,$cat_id) {
global $link;
$qr2=mysql_query("SELECT c.category,sc.subcat_id FROM categories AS c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id ORDER BY c.category", $link);
//make indenting for categories
$pad=""; for ($i=1; $i<=$level; $i++) {$pad .= " ";}
echo "$pad\[<br>\n"; //start of category set
while ($rowr2=mysql_fetch_assoc($qr2)) {
echo $pad . $rowr2['c.category'] . "<br>\n";
traverse_tree($level+1,$rowr2['sc.subcat_id']);
}
echo "$pad\]<br>\n"; //end of category set
}
function traverse_tree_select($level=1,$cat_id,$text) {
//just surround the call to this function with <select> tags and you're done.
global $link;
$qr2=mysql_query("SELECT c.cat_id,c.category,sc.subcat_id FROM categories AS c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id ORDER BY c.category", $link);
if ($text) {$text .= ":";} //category separator
while ($rowr2=mysql_fetch_assoc($qr2)) {
echo "<option value=$rowr2[c.cat_id]>$text$rowr2[c.category]</option>\n";
traverse_tree($level+1,$rowr2['sc.subcat_id'], $text . $rowr2['c.category']);
}
}
function display_categories() {
global $link;
$q=mysql_query("SELECT cr.cat_id,c.category FROM categoryroots AS cr,categories AS c WHERE c.cat_id=cr.cat_id ORDER BY c.category", $link);
while ($row=mysql_fetch_assoc($q)) {
traverse_tree(1, $row['cr.cat_id']);
}
}
function display_categories_select($name,$id=0) {
global $link;
if (0==$id) {$id="";} else {$id="id=\"$id\"";}
echo "<select size=\"1\" $id name=\"$name\">\n";
$q=mysql_query("SELECT cr.cat_id,c.category FROM categoryroots AS cr,categories AS c WHERE c.cat_id=cr.cat_id ORDER BY c.category", $link);
while ($row=mysql_fetch_assoc($q)) {
traverse_tree_select(1, $row['cr.cat_id'], "");
}
echo "</select>\n";
}
function delete_tree($cat_id) {
global $link;
$qr1=mysql_query("SELECT * FROM subcategories WHERE cat_id=$cat_id", $link);
while ($rowr1=mysql_fetch_assoc($qr1)) {
if ($rowr1['subcat_id']) { //not 0?
delete_tree($rowr1['subcat_id']);
}
mysql_query("DELETE FROM categories WHERE cat_id=$rowr1[cat_id]", $link);
}
mysql_query("DELETE FROM subcategories WHERE cat_id=$cat_id", $link);
}
function zap_categories() {
global $link;
$q=mysql_query("SELECT cat_id FROM categoryroots", $link);
while ($row=mysql_fetch_assoc($q)) {
delete_tree(1, $row['cat_id']);
}
mysql_query("DELETE FROM categoryroots", $link);
}
?>
<body>
<?php
display_categories();
display_categories_select("category");
?>
Copyright © 2001-2007 Jim Michaels, All rights reserved.