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@xxxxxxxxxxxxxxxx
www.the-spectrum.org ------oOo---------------oOo------
----- Original Message -----
From: "John Meyer" <john.l.meyer@xxxxxxxxx>
To: "Flint Million" <fmillion@xxxxxxxxx>; <php-db@xxxxxxxxxxxxx>
Sent: Sunday, January 22, 2006 9:02 AM
Subject: Re: 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
