Re: database for books question

Yes you're on the right track Easiest practical way is as follows: Your book listing table has a field named say: "subject_id" Which is just a SMALLINT UNSIGNED column or something, that allows up to 2^16 sized integers, more than I think should be sufficient for a huge list of subjects, an TINYINT UNSIGNED that allows up to 255 would possibly even be more than sufficient. And then create a table named say: "subject_listing" or something like that And it would simply have fields as such: subject_id SMALLINT UNSIGNED not null AUTO_INCREMENT, title varchar(24) NOT NULL and your index would be a PRIMARY KEY on subject_id or a UNIQUE constraint at least Therefore you could construct this table with this query: CREATE TABLE subject_listing (subject_id SMALLINT UNSIGNED not null AUTO_INCREMENT, title varchar(24) NOT NULL, PRIMARY KEY (subject_id)); Finally, getting a listing on a PHP page as you mentioned wouldn't take more than just running something like: <? $DB=mysql_connect("mysqlserver","user","pass"); mysql_select_db("yourdb",$DB); $sql=mysql_query("SELECT sl.title AS 'title', count(book_id) as 'count' FROM subject_listing sl LEFT JOIN book_listing bl USING (subject_id) group by subject_id",$DB); if(mysql_num_rows($sql)>0) { while($row=mysql_fetch_array($sql)) { echo $row["title"]." (".$row["count"].")<BR>"; } } else { //safe assumption that each book must have a subject_id which exists in subject_listing echo "No books were found"; } ?> Should work, mostly tested, the SQL will work for sure.. Have fun!! ---oOo--- Allowing users to execute CGI scripts in any directory should only be considered if: ... a.. You have no users, and nobody ever visits your server. ... Extracted Quote: Security Tips - Apache HTTP Server ---oOo--- ------oOo---------------oOo------ Julien Bonastre [The_RadiX] The-Spectrum Network CEO ABN: 64 235 749 494 julien@the-spectrum.org www.the-spectrum.org ------oOo---------------oOo------ ----- Original Message ----- From: "John Meyer" <john.l.meyer@gmail.com> To: "Flint Million" <fmillion@gmail.com>; <php-db@lists.php.net> Sent: Sunday, January 22, 2006 9:02 AM Subject: Re: [PHP-DB] database for books question

Flint Million wrote:

Now I want to be able to do a subject listing as well as a search. For example, if I asked for a lsit of all subjects, I want something like: Nonfiction (2) PHP (1) Technology (2) Computers (1) Programming (1) History (1) Linux (1)

In database design, I'd almost do this as a subject table, and then link the books together through a subject-book linking table. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date: 20/01/2006

-- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.375 / Virus Database: 267.14.21/236 - Release Date: 20/01/2006