database for books question

------=_Part_5329_26543098.1137879132513
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,
I'm designing a PHP/MySQL site to manage a collection of books. I have so
far created the table and inserted some of the data, and created a web
interface to access that data. I have a couple of things I'd like to do
though, if anyone has ideas.

Firstly, I want to do subject searches. For example, let's say we have two
books. For each book there is a field named "subjects". That field contains
space-delimited subject names. For example, the first book might say
"Nonfiction PHP Technology Computers Programming" and the second book might
say "Nonfiction Technology History Linux".

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)

Can this be done simply by using MySQL queries and PHP functions or will
there need to be a constantly updated SQL table of these subject keywords?

Also when I am performing searches via subject, can I do "token" searches?
Like if I search for "fiction" books the query might look like "select
book_id from library where subjects like '%fiction%'" - Only problem with
that, is it would also pick up "nonfiction" since the word fiction is still
a wildcard match.

I'm also tryin to figure out the best way to do searches. Right now I have =
a
simple search that simply does "select book_id from library where title lik=
e
"%{whatever the user entered}%" or author like "%{whatever the user
entered]%"". This works for basic simple searching, but I'd like to
implement advanced searches.

THanks for any advice.
Flint M

------=_Part_5329_26543098.1137879132513--

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

Re: database for books question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Flint Million wrote:
> Good idea. OK so I'll create a new database with subject ID's. Now, back in
> the primary database, how can I have multiple subjects assigned to one book?
> Let's say I repeat my above example and we assign "Nonfiction" to 1,
> "Technology" to 4, and "Linux" to 5. I know I might be getting off PHP topic
> here, but what's the best way then to have the database be able to reference
> multiple subjects per record? Or, would I have to do like "subject0 subject1
> subject2 etc." fields considering the maximum number of subjects per book? I
> still want to be ao search by subject and any book that has that particualr
> subject assigned to it would be returned.
>


Here's how
Table:

BOOKS
BOOK_ID
BOOK_NAME
.....
PK: BOOK_ID

SUBJECTS
SUBJECT_ID
SUBJECT_NAME
PK: SUBJECT_ID

BOOK_SUBJECT
BOOK_ID (references BOOKS)
SUBJECT_ID (references SUBJECTS)
PK: (BOOK_ID,SUBJECT_ID)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFD0s5Hj60GAoLuoDkRAlu2AJ4wqmitcKOpSwxiosJIrxHRr4swEACf Uy4N
biTma4v6Kw5ZWps879Xa9Jg=
=D0Nk
-----END PGP SIGNATURE-----

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

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"].")
";
}
} 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"
To: "Flint Million" ;
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

Re: database for books question

Flint Million wrote:
> Good idea. OK so I'll create a new database with subject ID's. Now,
> back in
> the primary database, how can I have multiple subjects assigned to one
> book?
> Let's say I repeat my above example and we assign "Nonfiction" to 1,
> "Technology" to 4, and "Linux" to 5. I know I might be getting off PHP
> topic
> here, but what's the best way then to have the database be able to
> reference
> multiple subjects per record? Or, would I have to do like "subject0
> subject1
> subject2 etc." fields considering the maximum number of subjects per
> book? I
> still want to be ao search by subject and any book that has that
> particualr
> subject assigned to it would be returned.
>

???

Sorry I didn't seem to get this email? Was it posted to the list?


In that case you WILL need an intermediatery table then as John
proposed. My method works similiar but its a one-to-one relationship per
subject


Apart from that the rest should follow suite the same..

tata



---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"
To: "Flint Million" ;
Sent: Sunday, January 22, 2006 10:14 AM
Subject: Re: [PHP-DB] database for books question


> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Flint Million wrote:
>> Good idea. OK so I'll create a new database with subject ID's. Now,
>> back in
>> the primary database, how can I have multiple subjects assigned to
>> one book?
>> Let's say I repeat my above example and we assign "Nonfiction" to 1,
>> "Technology" to 4, and "Linux" to 5. I know I might be getting off
>> PHP topic
>> here, but what's the best way then to have the database be able to
>> reference
>> multiple subjects per record? Or, would I have to do like "subject0
>> subject1
>> subject2 etc." fields considering the maximum number of subjects per
>> book? I
>> still want to be ao search by subject and any book that has that
>> particualr
>> subject assigned to it would be returned.
>>
>
>
> Here's how
> Table:
>
> BOOKS
> BOOK_ID
> BOOK_NAME
> ....
> PK: BOOK_ID
>
> SUBJECTS
> SUBJECT_ID
> SUBJECT_NAME
> PK: SUBJECT_ID
>
> BOOK_SUBJECT
> BOOK_ID (references BOOKS)
> SUBJECT_ID (references SUBJECTS)
> PK: (BOOK_ID,SUBJECT_ID)
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFD0s5Hj60GAoLuoDkRAlu2AJ4wqmitcKOpSwxiosJIrxHRr4swEACf Uy4N
> biTma4v6Kw5ZWps879Xa9Jg=
> =D0Nk
> -----END PGP SIGNATURE-----
>
> --
> 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

Re: database for books question

Hi,

you may wish to know that the kind of tools you are asking about are
already well developed by members of the Typo3 community.

Typo3 is a powerful open source, community developed, GPL licensed, php
+mysql based CSM. See at http://www.typo3.org

If you look at their Extension Repository (search word: book) you may
find at least a couple of interesting things.

Indeed, if you some spare time and like to do some programming, it would
be nice to have more people who help to improve them.

Typo3 is somehow difficult but truly rewarding.
regards
mario

On Sat, 2006-01-21 at 15:32 -0600, Flint Million wrote:
> Hi,
> I'm designing a PHP/MySQL site to manage a collection of books. I have so
> far created the table and inserted some of the data, and created a web
> interface to access that data. I have a couple of things I'd like to do
> though, if anyone has ideas.
.........

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