[PHP-DB] database for books question
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 like
%{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
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
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 iD8DBQFD0s5Hj60GAoLuoDkRAlu2AJ4wqmitcKOpSwxiosJIrxHRr4swEACfUy4N biTma4v6Kw5ZWps879Xa9Jg= =D0Nk -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 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
[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
Re: [PHP-DB] 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 [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 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 iD8DBQFD0s5Hj60GAoLuoDkRAlu2AJ4wqmitcKOpSwxiosJIrxHRr4swEACfUy4N 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
[PHP-DB] auto_increment and INSERT INTO
I have various tables where a column is set to auto_increment in my
table structure. I have been using the following INSERT query format:
INSERT INTO table VALUES ('$auto_increment_variable','$variable_1',
'$variable_2')
and then in applications where I have needed to know the value of the
$auto_increment_variable I have immediately queried the table for
$variable_1 and $variable_2 and used
$reference = mysql_result($result,$i,reference);
to determine what numeric value was assigned.
Is there a way I may find out what value was assigned to the
$auto_increment_variable when the INSERT INTO query is issued?
Thanks.
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment and INSERT INTO
On Sun, 22 Jan 2006 15:08, Ron Piggott (PHP) wrote:
I have various tables where a column is set to auto_increment in my
table structure. I have been using the following INSERT query format:
INSERT INTO table VALUES ('$auto_increment_variable','$variable_1',
'$variable_2')
and then in applications where I have needed to know the value of the
$auto_increment_variable I have immediately queried the table for
$variable_1 and $variable_2 and used
$reference = mysql_result($result,$i,reference);
to determine what numeric value was assigned.
Is there a way I may find out what value was assigned to the
$auto_increment_variable when the INSERT INTO query is issued?
Yes. Again, it's in the PHP docs if you look in http://www.php.net/mysql
for Get the ID generated from the previous INSERT operation
Really, you should try the docs first instead of falling back on the soft
option of asking the list.
Also, just as a side note, you don't need to provide a value for the
auto-increment field; that's why it is called auto as it does the
incrementing automatically. If your response to that is I need the
values in a specific order then you have the wrong idea of what the
auto-increment field is for.
Cheers
--
David Robley
Set laser printers to stun.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] auto_increment and INSERT INTO
That is the function I was looking for. Thanks. Ron
On Sat, 2006-01-21 at 23:47 -0500, [EMAIL PROTECTED] wrote:
Try:
?php
mysql_query(INSERT INTO table VALUES ('$variable_1', '$variable_2'););
$auto_increment_variable = mysql_insert_id();
?
-TG
= = = Original message = = =
I have various tables where a column is set to auto_increment in my
table structure. I have been using the following INSERT query format:
INSERT INTO table VALUES ('$auto_increment_variable','$variable_1',
'$variable_2')
and then in applications where I have needed to know the value of the
$auto_increment_variable I have immediately queried the table for
$variable_1 and $variable_2 and used
$reference = mysql_result($result,$i,reference);
to determine what numeric value was assigned.
Is there a way I may find out what value was assigned to the
$auto_increment_variable when the INSERT INTO query is issued?
Thanks.
Ron
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
