PDO loop array into query

Question

I have a search input, I explode user input into array (key words).

ex. web programmer -> $search[0]=>web, $search[1]=>programmer

How do I loop the array into the query for search?

$nums=count($search);

for($n=0; $n<$nums; $n++){
    $SQL=$db->prepare("SELECT * FROM post 
    WHERE title LIKE :search_1 OR classify LIKE :search_2");
    $SQL->bindValue(':search_1', "%".$search[$n]."%", PDO::PARAM_STR);
    $SQL->bindValue(':search_2', "%".$search[$n]."%", PDO::PARAM_STR);
    $SQL->execute();        
}

$db=NULL;

So it will search 'web' and 'programmer' these 2 words from db

http://www.php.net/manual/en/pdostatement.execute.php Example 3 or Example 2 — u_mulder, Jun 12 '13 at 15:36

score 3 · Accepted Answer · answered Jun 12 '13 at 15:32

Somethig like this:

$strSQL="SELECT * FROM post WHERE 1=1 ";
foreach ($search as $i => $value){
  $strSQL.=" AND (title LIKE :search_$i OR classify LIKE :search_clasify_$i)";
}
$SQL=$db->prepare($strSQL);

foreach ($search as $i => $value){
  $SQL->bindValue(":search_$i", "%".$value."%", PDO::PARAM_STR);
  $SQL->bindValue(":search_clasify_$i", "%".$value."%", PDO::PARAM_STR);
}

$SQL->execute();

score 1 · Answer 2 · answered Jun 12 '13 at 15:27

1

bindParam    //you dont need to re-prepare every loop counter just do it once thats the whole point of prepare!
    $SQL=$db->prepare("SELECT * FROM post WHERE title LIKE :search_1 OR classify LIKE :search_2");
    //now loop and bind each set of vars and then execute inside the loop
    for($n=0; $n<count($search); $n++){
        $SQL->bindParam(':search_1', "%".$search[$n]."%", PDO::PARAM_STR);
        $SQL->bindParam(':search_2', "%".$search[$n]."%", PDO::PARAM_STR);
        $SQL->execute(); // you may find you need to pass the return result into an array which you can loop through afterwards or echo out the contents of each query on each count of this loop   
    }

    $db=NULL;

answered Jun 12 '13 at 15:27

Dave

3,280
2
22
40

so if I loop bind and execute, i dont need to worry about placeholder will repeat – user2178521 Jun 12 '13 at 15:31
1

This is not going to work, you need to build the query dynamically based on the number of elements in the array and you also need to bind each variable separately in a loop after that. – jeroen Jun 12 '13 at 15:32
once you prepare your param with the placeholder you can re-execute the same prepared query as many times as you like passing in different params each time (write sql one execute many) – Dave Jun 12 '13 at 15:33
This doesn't do what OP wants, which is a dynamic number of search terms in a single query and single execution. – MrCode Jun 12 '13 at 15:37
Not sure how you get that from his question especially since his query tied with this pdo bind are making it look like he wants to select from 2 different column with column one looking from the first array keyword and column 2 from the 2nd array key word etc it mentions nothing about doing dynamic searching of the various key words on a single column. If you look his query contains 2 column `title` and `classify` – Dave Jun 12 '13 at 15:40
If that is what he's after curlas's answer would be the correct one :) – Dave Jun 12 '13 at 15:43
ya, curlas one is what im looking for but thanks to you too! – user2178521 Jun 12 '13 at 16:01

PDO loop array into query

2 Answers2