Re: using Mysql and PHP to store and search for keywords of a record

Bring your schema into some semblence of any Normal Form. Relational database management systems are designed and optimized to be relational database management systems, not flat file impersonators. For example, you will need to break out your single table into three (3) tables to correctly and optimally implement the keyword search feature you're trying to build: record (id, title, ... ) keyword (id, word) record_keyword (record_id, keyword_id) Doug Eager Ip was heard at 12:40 PM 6/29/00 -0700 to say: >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