multiple terms SQL with explode/implode and PHP PDO

Question

I have a search form that would like the users to search multiple terms. I'm currently using PHP PDO and I'm still learning... I was wondering if someone can tell me what I'm doing wrong here.

    $varSearch = @$_GET['dms']; 
    $varTerm = explode(" ", $varSearch);
    $termArray = array();
    foreach($varTerm as $term){
        $term = trim($term);
        if(!empty($term)){
            $termArray[] = "name LIKE '%".$term."%' OR tags LIKE '%".$term."%'";
        }
    }
    $implode = implode(' OR ', $termArray);
    $sql = $dbh->prepare("SELECT * FROM table WHERE ?");
    $sql->execute(array($implode));

possible duplicate of [How do I create a PDO parameterized query with a LIKE statement in PHP?](http://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement-in-php) — Brad, Jan 31 '12 at 04:36
thanks, i answered my own question, and posted it in case anyone else needs it — hellomello, Jan 31 '12 at 04:58

summea · Answer 1 · 2012-01-31T05:51:02.240

2

Have you considered doing something like this, instead:

$varSearch = @$_GET['dms'];
$varTerm = explode(" ", $varSearch);
$termsStringArray = array();
$termsArray = array();
foreach($varTerm as $term){
    $term = trim($term);
    if(!empty($term)) {
        array_push($termsStringArray, "name LIKE ? OR tags LIKE ? ");
        array_push($termsArray, $term);
        array_push($termsArray, $term);  // note, you can do this part differently, if you'd like
    }
}
$implodedTermsString = implode('OR ', $termsStringArray);
$sql = $dbh->prepare("SELECT * FROM biz WHERE " . $implodedTermsString);
$sql->execute(array($termsArray));

Output:

// prepare statement
SELECT * FROM biz WHERE name LIKE ? OR tags LIKE ? OR name LIKE ? OR tags LIKE ? OR name LIKE ? OR tags LIKE ? OR name LIKE ? OR tags LIKE ?

// $termsArray (for execute)
Array
(
    [0] => this
    [1] => this
    [2] => is
    [3] => is
    [4] => the
    [5] => the
    [6] => string
    [7] => string
)

Basically, trying to separate the array data from the initial SQL query prepare string. Let me know if that works for you!

Though, you still will want to do some sort of checking (or sanitization,) of the data you are getting from the $_GET variable. That $_GET variable could have anything in it... and could be bad for SQL injections or other unwanted issues.

And, LIKE isn't necessarily going to be the most efficient way to do this type of database search. But if you use it (and I have used it for search things in the past,) try checking out: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning.

edited Jan 31 '12 at 05:51

answered Jan 31 '12 at 05:06

summea

7,390
4
32
48

I thought the `prepare` was insusceptible to SQL injection? – hellomello Jan 31 '12 at 05:14
I must have updated this while you were writing your comment... :) It's usually good to sanitize your data before it goes into something that will be used with the database. (http://diovo.com/2008/09/sanitizing-user-data-how-and-where-to-do-it/) – summea Jan 31 '12 at 05:15
Thanks, also.. isn't my answer below the same? putting it into an array? – hellomello Jan 31 '12 at 05:22
Kind of like @Brad hinted at in his later comment, I believe that your earlier answer is still going to end up searching for the same `:term`... because `:term` is ultimately the only key in your array at the end of the code, right? :) The alternate answer I gave should go through all of the terms you are searching for because all of the terms are in a separate array (`termsArray `)... and the `prepare` string (`implodedTermsString `) just uses `?` for each of those separate array values. – summea Jan 31 '12 at 05:40
I'm getting an error `Warning: array_push() expects parameter 1 to be array,` – hellomello Jan 31 '12 at 06:12
That's weird; I double-checked my code just now... and `$termsArray = array();` does mean that `$termsArray` is an `array`... so when `array_push()` is being used with `$termsArray` (or even `$termsStringArray`,) that `parameter 1` is, indeed, an array. Any idea what version of PHP you are using? I tested this on PHP 5.2.17 (from the command line.) – summea Jan 31 '12 at 06:20
I've checked my code and I'm outputting different values, and not the same. the two `:terms` I have goes through two different tables, `name` and `tags`, so if I'm searching `term1`, it will go through table `name` and `tags` that has `term1`. Let me know if this make sense? – hellomello Jan 31 '12 at 06:23
Here is the output I'm getting for the SQL in your code from your answer: `SELECT * FROM table WHERE name LIKE :term OR tags LIKE :term OR name LIKE :term OR tags LIKE :term OR name LIKE :term OR tags LIKE :term OR name LIKE :term OR tags LIKE :term` And then, when you do `$sql->execute(array(":term"=>"%".$term."%"));` you are only searching for the one term. If you had this inside a loop, to iterate through all of the earlier-found terms... then it would probably work. But as it is, like @Brad said, it appears that you are only looking for one term...! – summea Jan 31 '12 at 17:50
That SQL is correct, it does output that, but you're forgetting the explode and the foreach. When I type in multiple words, it does a foreach getting each word, and then putting it into an array, so each term will go through a `name` and `tag` table – hellomello Feb 01 '12 at 02:59

score 1 · Answer 2 · answered Jan 12 '15 at 08:17

Remake

$my_explode = explode(" ",$search);
$query = array();
foreach($my_explode as $string)
{
$query[] ="name LIKE '%".$string."%' OR email LIKE '%".$string."%'";
}
$implode = implode(' OR ', $query);
foreach ($db->query("SELECT * FROM _table WHERE ".$implode."") as $info)
{
    echo $info['name']."<br />";
}

Secure for injection, with php retrieve just alphanumeric chars

$search = preg_replace("/[^a-zA-Z0-9]+/", "-", $_GET["text"]);

score 1 · Accepted Answer · answered Jan 31 '12 at 04:58

1

If anyone else needs this answer too...

$varSearch = @$_GET['dms']; 
$varTerm = explode(" ", $varSearch);
$termArray = array();
foreach($varTerm as $term){
    $term = trim($term);
    if(!empty($term)){
        $termArray[] = "name LIKE :term OR tags LIKE :term";
    }
}
$implode = implode(' OR ', $termArray);
$sql = $dbh->prepare("SELECT * FROM table WHERE ".$implode."");
$sql->execute(array(":term"=>"%".$term."%"));

answered Jan 31 '12 at 04:58

hellomello

8,219
39
151
297

You're only searching your database for one term. – Brad Jan 31 '12 at 05:02
am i not doing it in an array? I don't understand. Why am I getting a negative... – hellomello Jan 31 '12 at 05:12
1

No, you're preparing a really long statement where you re-use the exact same parameter name, and then bind one parameter to it. You're getting a downvote because your answer is wrong. – Brad Jan 31 '12 at 05:19
actually I just checked what my array outputs, and I believe my solution is the correct one.... – hellomello Jan 31 '12 at 06:16
Say if I type in term1 and term2, then it would search `name LIKE term1 OR tags LIKE term1 OR name like term2 OR tags LIKE term2`... this which gives people the ability to search two different terms going through name table and tags table – hellomello Jan 31 '12 at 06:20

multiple terms SQL with explode/implode and PHP PDO

3 Answers3