Related Tutorials

• Create secure php scripts
• Using and Understanding Sessions
• PHP Form Image Upload
• Conditional Statements
• multiple files upload
• Alternating Row Colors With MySQL Results
• Prevent Hot Linking
• Regular Expressions in PHP
• Using PHP to search MySQL
• Smiley System
• PHP and Ajax
• Search MySQL Effectively
• Print Date
• Build PHP Style Switcher
• Get Current Page URL
• Send HTML Emails with PHP
• PHP Sessions
• Resize image on the fly with GD
• Parsing XML Using PHP
• Using If Else Statements

Search MySQL Database Effectively

I am not going to teach you how to create a search form or how to clean your inputs in this tutorial. I assume you have some knowledge of how to do that or you can try a search through our tutorial database. This tutorial will teach you how to search Mysql effectively without using tons of extra coding.
Most people know you can use LIKE %$search% to find matches but what if your user input "Pirates Action" with the hopes of finding an action movie that may have Pirates in them? Well this tutorial shows you how to separate the phrase and search for both words.

Steps :
At this point you would have already created your php file that will get the search phrase. In this example I will assume that your search field is named "search" and that you have sanitized the input.

//get search $search = $_POST['search'];

This is the fun part and the part that makes this work. We need to explode the spaces in the phrase and place them in an array. We can then use "OR" for each word other than the first. In this example we want to match the column "title" with our search phrase. You can use any column.

//check for spaces $stitle = explode(' ', $search); //count the values in the array $n = count($stitle); if($n >0){//if there are values in the array more than 1 (in arrays 0 is also a value) for($i =0; $i<$n; $i++){//loop through the array if($i == 0) {//if is the first result do not add "OR" $wsearch .= "title LIKE '%".$stitle[$i]."%'"; }else{//this is 2, 3, 4... $wsearch .= " OR title LIKE '%".$stitle[$i]."%'"; } } }else{//this is not an array e.g. "car" $wsearch = "title LIKE '%".$search."%'"; }

The final step is now to place the "$wsearch" within the sql and it will do the rest. The complete code below.

//get search $search = $_POST['search']; //check for spaces $stitle = explode(' ', $search); //count the values in the array $n = count($stitle); if($n >0){//if there are values in the array more than 1 (in arrays 0 is also a value) for($i =0; $i<$n; $i++){//loop through the array if($i == 0) {//if is the first result do not add "OR" $wsearch .= "title LIKE '%".$stitle[$i]."%'"; }else{//this is 2, 3, 4... $wsearch .= " OR title LIKE '%".$stitle[$i]."%'"; } } }else{//this is not an array e.g. "car" $wsearch = "title LIKE '%".$search."%'"; } //sql $sql = "SELECT * FROM mytable WHERE ".$wsearch." ORDER BY title ASC LIMIT 0, 5";

That's it!! Post a comment if you have any questions or changes.