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 [EMAIL PROTECTED] www.the-spectrum.org ------oOo---------------oOo------ ----- Original Message ----- From: "John Meyer" <[EMAIL PROTECTED]>
To: "Flint Million" <[EMAIL PROTECTED]>; <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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to