Building a PHP and MySQL search function
Let’s say we have some a food item stored as Crackers, wheat, sandwich, with peanut butter filling and the user searches for peanut butter crackers. If the search is coded simplistically like:
SELECT * FROM some_table WHERE some_field LIKE '%peanut butter crackers%'
SELECT * FROM some_table WHERE some_field LIKE '%peanut%butter%crackers%'
Then we won’t get any results because the ordering is wrong: “peanut butter crackers” is not like “crackers, peanut butter”.
What we want to so is search for each query term individually:
So our query would look something like this:
SELECT * FROM some_table WHERE (some_field LIKE '%peanut%' AND
some_field LIKE '%butter%' AND
some_field LIKE '%crackers%')
Here is some simple code that accomplishes what we just discussed: PHP: Multi-word MySQL Search Algorithm and Output . There is a lot more stuff we can do: spell checking, suggesting alternative queries, ordering search results (the list goes on and on!) So that means I still have a lot of work to do on my search function. There are oodles of features that I want to implement. But you have to start somewhere and right now I have a basic search feature implemented.
Here are some of the pages I’ve been reading to brush up on search topics:
- search algorithm classes in PHP – PHPBuilder.com
- How do you implement a “Did you mean”? – Stack Overflow
- “Similar Posts” like functionality using MS SQL Server? – Stack Overflow
- How to Write a Spelling Corrector (in Python)
- Simple Keyword Relevance — r937.com
- search algorithm php – Google Search
- Word comparison algorithm – Stack Overflow
- Book: Foundations of Statistical Natural Language Processing – Christopher D. Manning, Hinrich Schuetze
- Pro PHP Security / Preventing SQL Injection (page 1)
- Pro PHP Security / Preventing SQL Injection (page 2)
PHP functions for advanced searching:
I hope these links have been helpful if you’re working on something similar :-).