A limitation with searches using MySQL is that when using LIKE to find the words queried it will only find them if they are together in the same order they were written. Here is how to change that so the words are found in any order.
As I haven’t posted a tutorial in a while, sorry about that, I decided I would show you all how to change a standard LIKE MySQL query into a query that will find all words in the query in any order.
Normally you would have a MySQL query that looks like this:
SELECT column FROM table WHERE column LIKE '%word1 word2%'
This would only find word1 and word2 in that order. If you want to change this you need to make a few changes to the PHP code that sorts your query.
When you get your query from your search, do whatever SQL injection prevention you need to and then take your query and run it through explode like this:
$words = explode(' ', $searchquery);
This will return an array of your search words separated into an array by the spaces. Now you just need to create your MySQL query:
$sql = "SELECT column FROM table WHERE";
$sql_end = '';
foreach($words as $word) {
$sql_end .= " AND column LIKE '%{$word}%''';
}
$sql_end = substr($sql_end, 4);
$sql = $sql.$sql_end;
$result = $mysqli->query($sql);
//continue
That will then make a query that looks like this (assuming there are 2 words):
SELECT column from table WHERE column LIKE '%word1%' AND column LIKE '%word2%'
That will then find the words in any order. I would imagine, although I haven’t tried this, you could make the words optional by using the OR operator, this will then return the results if only some of the results are found where AND requires then all to be found or it will not return a result.
I hope this little tutorial has been some use to you guys & gals out there. Also I’ll be doing some screen cast style tutorials in the near future, so look out for them.
*We currently have £44 of the £90 needed to keep our server running.
It's late here in the UK but it's the 30th so a big Happy Birthday to @Y_Strahovski loads of hugs 'n kisses, hope you have a great day. - 10 hours ago
Wow! Jeffster are awesome and @Y_Strahovski has one hell of a throwing arm, lol. http://yfrog.us/n8b8xz (via @kentuckysocal) - 6 days ago
@AshBob87 No worries. Glad I could help out. ;) - 6 days ago
@AshBob87 No problem. I just found it a few seconds ago. Was sitting in my bookmarks all along. *facepalm* http://bit.ly/cI9cQS - 6 days ago
@AshBob87 Thanks. ;) I can't find the tutorial I followed anymore. :( - 6 days ago
Discussion: 2 Comments
can you please provide me the entire script of the above example
Well the rest of the script is just a standard MySQLi setup.
$mysqli = new mysqli('host', 'user', 'password', 'database'); $sql = "SELECT column FROM table WHERE"; $sql_end = ''; $words = explode(' ', $searchquery); foreach($words as $word) { $sql_end .= " AND column LIKE '%{$word}%'''; } $sql_end = substr($sql_end, 4); $sql = $sql.$sql_end; $result = $mysqli->query($sql); while($row = $result->fetch_row()) { //print out results however you like $row is an array } $result->free();Sanitization is completely dependant on the data being received. If it is just string data though you could run it through some REGEX like this:
$searchquery = preg_replace('[^a-zA-Z0-9 ]', '', $searchquery);That will replace all characters that aren’t alphanumeric or spaces. You would do that before the explode splits the query up.