RE: [PHP-DB] using Mysql and PHP to store and search for keywords of a record

The best way to store keywords and titles in a relational database is: Keyword lookup table RecordID Keyword -------- --------- 1 title1's_1st_keyword 1 title1's_2nd_keyword 1 title1's_3rd_keyword 2 title2's_1st_keyword ... Title lookup table RecordID Title -------- --------- 1 title1 2 title2 ... That will allow you to use a keyword to look up the record title via a simple join in your query. SELECT Title FROM TitleLookup INNER JOIN KeywordLookup ON TitleLookup.RecordID = KeywordLookup.RecordID WHERE Keyword = UserSuppliedKeyword; (I didn't try that out. I think it is correct for the most recent version of MySQL. Prior to 3.23.16, I think the condition in the ON clause has to go in the WHERE clause.) Your proposed structure below violates 1NF, which is about as fundamental an error as you can make in a relational database. Specifically, it violates the requirement for atomic data; the data in a field should not be divisible. If you think of a relational database as a text file or flat file, or do searches with procedural code, you're going to make life a lot harder for yourself. Check out a book on databases and learn the first three normal forms, along with the basics of SQL. It's not that hard to learn and will repay the effort many times over. The most efficient way to search in PHP is $result = mysql_query($sql); where $sql is a string variable containing an SQL SELECT statement. But I wouldn't worry about speed. 100 records with 5 keywords each is a wee little tiny shrimpy miniscule database. MySQL will process your searches in less time than it takes to blink. Bob Hall -----Original Message----- From: Eager Ip [mailto:ipeager@interchange.ubc.ca] Sent: Thursday, June 29, 2000 3:40 PM To: php-db@lists.php.net; mysql@lists.mysql.com Subject: [PHP-DB] using Mysql and PHP to store and search for keywords of a record Hi, I am developing a database driven website that allows user to search the title and ID of a record by keywords. I want to set up a field called "keyword" in the database table to store all the keywords associated with the records, e.g. record ID record title keywords 1 a title keyword1, keyword2, keyword3, keyword4, etc. and I want to use both Mysql query and PHP to find any matches in the keywords field and return the record ID and title. What is the best data/column type in Mysql for me to store and query the keywords? What kind of method should I use to separate different keywords (e.g. use commas "," or tab "/t")? And what is the best/efficient way to search for those keywords using PHP? (I am thinking about the string compare functions and array explode functions, but I am worrying about the speed of the search because we will have more than 100 records in the table and there will be at least 5 keywords for each record.) Thanks for your help. Eager =:o3 ipeager@interchange.ubc.ca -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net For additional commands, e-mail: php-db-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net