PDO, Variable SQL Query, bindParam and Loop

Question

I recently moved from mysql_ functions to PDO, and there is something i can't adapt to my situation.

The problem is the search query, actually i'm doing like that :

$resultat = $DB->prepare('SELECT * FROM `posts` WHERE `title` LIKE :searchterm OR `tags` LIKE :searchterm LIMIT '.$debut.', '.NB_ART);
$resultat->bindParam(':searchterm',$searchterm);
$resultat->execute();

Knowing that the size of the query may change depending on user search, how can i do something like that :

$tab = explode(' ',$searchterm);
$query = 'SELECT * FROM posts WHERE ';

foreach($tab as $value){
    if( strlen($value)>2 ){
        $query .= ' (`title` LIKE "%'.$value.'%" OR `tags` LIKE "%'.$value.'%") AND';
    }
}
$query = substr($query,0,-3);
$query .= 'ORDER BY `date` DESC';

// And how can i do the bindParam() here ?

I'll appreciate if you can show me a small example to start with.

Thanks ;)

ShaaD · Accepted Answer · 2012-05-17 08:47:58Z

1

$data = array();
$arr_query = array();

$tab = explode(' ',$searchterm);
$query = 'SELECT * FROM posts WHERE ';

foreach($tab as $value){
    if( strlen($value)>2 ){
        // sry for that, but i think implode() better
        $arr_query[] = '(`title` LIKE ? OR `tags` LIKE ?)';

        $data[] = '%'. $value .'%';
        $data[] = '%'. $value .'%';
    }
}
$query .= implode(' AND', $arr_query);
$query .= ' ORDER BY `date` DESC';

$res = $pdo->prepare($query);
$res->execute($data);

limits in the first example must be protected too.

edited May 17, 2012 at 8:47

answered May 15, 2012 at 13:45

ShaaD

6263 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Piokaz Over a year ago

Thanks, it helped me. But needed to replace $data[] = $value by $data[] = '%'.$value.'%'

Collectives™ on Stack Overflow

PDO, Variable SQL Query, bindParam and Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related