PHP Keyword search multiple fields populate new input

Question

I have an HTML input field from which the values are exploded and separated by each space in the string. This string then searches the database for matches and returns values if there is a match however I would like to search for multiple fields in one table from the original string.

Hopefully this will clear things up:

For example if the user searched for 'Sheldon boys jumper' I would like the database to search for a match form each of these keywords in each field of the database eg [school_name], [sex], [product_type]. At the moment I have this working for one field but I would like to return and gather the values for all three fields.

This is my code:

  if (empty($_POST) === false) {
    if(empty($_POST['title']) === true) {
    $no_data = '<div class="alert alert-danger center">Please enter a title</div>';
    } else {        
        $item_title = $_POST['title'];
        $keywords = explode(" ", $item_title);


        $query = "SELECT * FROM products WHERE ";
        foreach($keywords as $keyword) {
            $i++; // dump variable

            if($i == 1) { $query .= "product LIKE '$keyword' "; } 
                   else { $query .= "OR product LIKE '$keyword' "; }
        }
        $query = mysql_query($query);
        $numrows = mysql_num_rows($query);


        $row = mysql_fetch_assoc($query);
        echo $row['product'];
    }
}

[Please, don't use `mysql_*` functions in new code](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). **You are also wide open to [SQL injections](http://stackoverflow.com/q/60174)** — John Conde, Jan 08 '14 at 21:09
What have you tried? (further than the working code for searching one field) — Patrick Q, Jan 08 '14 at 21:12
Thanks John for your useful comments, I had not realised that! — user3170837, Jan 08 '14 at 21:18

score 0 · Answer 1 · answered Jan 25 '14 at 20:13

You really do want to be using PDO for safety and convenience. Either way, you still need to prepare a statement.

$fields = array('fields','you','want','to','search');
foreach($keywords as $keyword) {
    foreach ($fields as $field) {
    $i++; // dump variable
    if($i == 1) {
        $query .= "$field LIKE '$keyword' ";
    } else {
        $query .= "OR $field LIKE '$keyword' ";
    }
}

$i == 1 is weak true. You could set $i = false, then if($i === true), saving having to increment the variable for each loop. Alternatively, have OR at the end of every line, rather than the start, and the last instance of OR from the final query string. This also removes the if from each loop.

$fields = array('fields','you','want','to','search');
foreach($keywords as $keyword) {
    foreach ($fields as $field) {
        $query .= "$field LIKE '$keyword' OR ";
    }
}
$query = substr($query, 0, strlen($query) - 4);

PHP Keyword search multiple fields populate new input

1 Answers1