CODEWITHSUNDEEP
phphtmlmysqlmysqlipdo
virtuon92
virtuon92

Reputation: 49

PHP MYSQL multiword search

<?$search=$_POST['search'];
$query = $pdo->prepare("select * from tag where tag1 LIKE '%$search%' OR tag2 LIKE  '%$search%' LIMIT 0 , 10");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();
// Display search result
         if (!$query->rowCount() == 0) {
                echo "Search found :<br/>";

            while ($results = $query->fetch()) {

                echo "$".$results['name'];
                echo "</td></tr>";              
            }
                echo "</table>";        
        } else {
            echo 'Nothing found';
        }
?>
<form action="" method="post">
Search: <input type="text" name="search" placeholder=" Search here ... "/>
<input type="submit" value="Submit" />
</form>

I know that there are a lots of similar questions about this, but I still can't figure it out. If anyone has the time, explain me how too add explode to my search, so that I can use more than 1 word for search? Thanks a lot for your time. This script do search if I enter 1 word,in my case tag. But if I enter 2 words it will return in 0 results.

Upvotes: 1

Views: 74

Answers (1)

trincot
trincot

Reputation: 350147

Note what the documentation on bindValue says about the first argument:

Parameter identifier.

For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

You have neither ? placeholders nor colon-prefixed named placeholders in your SQL string. Instead you actually inject the user-provided input directly in your SQL, and become vulnerable for SQL injection. So you should start using placeholders as described in the documentation.

If you want any of separate words to be found, then you'll need to extend your WHERE dynamically, adding OR-conditions for matching with each word. It will be easier to use ? placeholders in such dynamically generated SQL.

Also, since your arguments are strings, you can use the optional parameter of execute to pass an array of arguments:

input_parameters

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

Here is some suggested code:

// Explode to words and filter for words which are not the empty string:
$words = array_filter(explode(" ", $_POST['search']), 'strlen');
// Wrap each of the words in '%'
$words = array_map(function ($search) { return "%$search%"; }, $words);
// Add a condition for each of the words in the WHERE clause, and repeat for tag2
$sql = "select * 
        from   tag 
        where  " .
        implode(" OR ", array_fill(0, count($words), "tag1 LIKE ?")) .
        " OR " .
        implode(" OR ", array_fill(0, count($words), "tag2 LIKE ?")) .
        " LIMIT  0, 10";    
$query = $pdo->prepare($sql);
// Pass the values as string twice: once for tag1 and once for tag2
$query->execute(array_merge($words, $words));

Upvotes: 2

Related Questions