improve search formular

Question

I want to make a searching script where I can search for multiple words but the script finds the words in all the rows instead of a specific row. For example.

  __________________________   ________
 |Cheese                    | | Search |
  ——————————————————————————   ————————

Then I make a php script that says:

SELECT * FROM table WHERE column1 = "$value" OR column2 = "$value" OR column3 = "$value" ... ;

This will show the results where the word"Cheese" appears in any of the columns. The thing is, that I want it to possible for people to search for both "cheese" and maybe "kitchen" but then I need to separate the words and search for something like this:

SELECT * FROM table WHERE column1 = "$value" OR column2 = "$value" OR column3 = "$value" ... AND column1 = "$value2" OR column2 = "$value2" OR column3 = "$value2" ... ;

The thing is that I really don't know how to do this. neither the word separation or how to find the words i the SQL database.. Also I don't know how to search for the words where "Cheese" is a part of.. like "Cheesecake"..

Please help me if anybody has an idea of how to make this..

Thank you.

Answer 1

You'll want to have a look at MySQL's Fulltext indexing: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Fulltext indexing creates an index of every word in the indexed columns (defaults require that the words be at least 4 characters, but this can be changed in the MySQL ini file). You can then run searches against multiple words, including beginning of word (cheese* will match cheesecake... but keep in mind that there is no way of using indexes to make "cake" match cheesecake as the indexes are alphabetical). This has the advantage of:

1. being MUCH faster than a series of AND () OR () statements
2. Being able to return relevancy indexes, so you know that a row that matches all keywords 5 times is more relevant than a row that has one of the words once, etc.

Answer 2

All your SQL needs are correct parentheses:

SELECT *
FROM table
WHERE (column1 = "$value" OR column2 = "$value" OR column3 = "$value") AND
      (column1 = "$value2" OR column2 = "$value2" OR column3 = "$value2")

"AND"s get evaluated before "OR"s.

By the way, having to search through multiple columns indicates a possible shortcoming in your data model. You might want a table structure that places each word in a separate row.

Answer 3

It is not possible to search an entire row in one clause. You can, however, use a LIKE clause to search for words like "Cheese" and have them match "Cheesecake".

This is going to get a lot more complicated than you think. I suggest looking into something like MySQL's MyISAM's full text searching, or if you plan on doing frequent searches or on large sets of data using something like Sphinx. Also, if you do decide to follow your current route, what you're looking for is a LIKE clause.

Anyway, to search for words that contain cheese or cake:

SELECT blah FROM tbl WHERE (col1 LIKE '%cheese%' OR col2 LIKE '%cheese%') AND (col1 LIKE 'cake' OR col2 LIKE 'cake');

This is extremely inefficient though. It can't use indexes, meaning it will require a full table scan for every query.

Answer 4

You're on a good track with those queries.

The most conspicuous absence is the wildcard operator in your SQL. This is used to match partial strings. E.g., this will find "cheesecake" and "cheese crackers" records:

SELECT *
FROM table
WHERE column1 = '%cheese%'

More advanced ways of handling searches involve indexing the data you want to search. Either doing it yourself or using third party or specific database tools. Look this up in Google.

However, for a regular site that isn't being hit by a lot of searches, your current approach is fine.

One more thing: if your question is about how to automatically add extra lines of SQL for each word in the search box, you will need to learn a bit more. Start by looking for these terms in the official PHP documentation (php.net),

• explode
• foreach

Answer 5

Why not retrieve all the data you need and use string function to campare the searched item from the haystack, i believe stristr would be of help to you, or you can just use SQL Query but then that might be stressful for your DB. correct me if im wrong.