PHP中的PHP循环和绑定查询

Question

Context

I'm trying to write an efficient and secure search script using PHP and MySQL which will prevent SQL injection attempts. I'm using the MySQLi suite of functions and specifically binding user input to the database queries. Because users may be searching for multiple words at a time I have developed a method to dynamically bind variables to a MySQLi query but am getting stuck in one particular place.

I have an example table of users and a table of all the skills the users have:

USERS:               SKILLS:
ID | Username        Userid  | Skill
1  | Patty           1       | Decorating
2  | Billy           1       | Renovating
                     2       | Painting
                     2       | Flooring

I have produced a search query which explodes all of the search terms a visitor has searched for and then runs them through a MYSQLi query:

$searched = explode (' ', stripslashes ($search)); // What the visitor searched for

$bindParam = new BindParam (); // Initiate the dynamic binding class
$qArray = array (); 

foreach ($searched as $sd) {    
    $sd = trim ($sd);   
    if ($sd != '') {
        $qArray[] = 'skills.skill LIKE CONCAT(?,\'%\')'; // Use arrays to construct part of the dynamic query
        $bindParam->add ('s', $sd); // Users are searching for strings so use 's' as opposed to 'i' for integer for example
    }
}

$query = "SELECT username FROM users WHERE users.id IN (SELECT skills.userid FROM skills WHERE ";
$query .= implode (' OR ', $qArray);
$query .= ")"; // Join the query together

$stmt = $mysqli->prepare ($query) or die ("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);    
call_user_func_array (array ($stmt, "bind_param"), $bindParam->get ()); // Bind the variables to the query

$stmt->execute ();
$stmt->store_result ();

if ($stmt->num_rows > 0) {              
    $stmt->bind_result ($username);
    $searchoutput = array ();
    while ($stmt->fetch ()) {
        $searchoutput[] = $username; // send all usernames to an array      
    }

print_r ($searchoutput); // print all contents of the array

}

The Question

Now, when the visitor searches for "decorating renovating painting" the code returns:

Array ( [0] => patsy [1] => billy )

...as the MySQL query is effectively ignoring the fact that Patsy comes up twice. Ideally I would like the array to actually show:

Array ( [0] => patsy [1] => patsy [2] => billy )

Unless I incorporate the query into a loop based on the number of search terms, How can I solve this?

Answer 1

Instead of a subquery I would recommend a JOIN:

SELECT username FROM users 
JOIN skills ON users.id=skills.userid
WHERE
skills.skill LIKE CONCAT(?,\'%\')
OR skills.skill LIKE CONCAT(?,\'%\')
OR ...

This should show username multiple times when they match on multiple skills.