CODEWITHSUNDEEP
mysqlpdodynamic-sql
sebastian.roibu
sebastian.roibu

Reputation: 2859

Mysql dynamic sql statement with PDO and parameters

I want to create a dynamic sql statement and run it using PDO. My problem is that i have some parameters and i cannot think of a way to pass the parameters. Ex : 

$query = "Select * from tbl_task where 1=1";
if (!empty($name)) $query .= " AND name = ?";
if (!empty($status)) $query .= " AND status = ?"
$db_stmt = new PDOStatement();
$db_stmt = $this->db->prepare($query);
$db_stmt->bindParam (1,$name); 
$db_stmt->bindParam (2,$status);

My parameters does not get binded and i don't know how many parameters i have to bind, unless i write the same if statements but with bindParam instructions.

I tryed with mysql_real_escape_string instead bindParam to PDO but for some reason my parameters are added empty.

Any idea on how can i build a dynamic query and bind parameters to PDO ?

Edit 1 : 

$arr = array();
if (!empty($name)){
    $query .= " AND `name` like :NAME";
    $arr['NAME'] = $name;
}
$db_stmt = new PDOStatement();
$db_stmt = $this->db->prepare($query);
$db_stmt->execute($arr);

How can i write a "like" statement ? I tried 

$query .= " AND `name` like :NAME" . "%";

and is not working.

Upvotes: 0

Views: 1655

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157839

What I usually do is the following:

$query = "Select * from tbl_task where 1=1";
if (!empty($name)) $query .= $db->parse(" AND name = ?s", $name);
if (!empty($status)) $query .= $db->parse(" AND status = ?s",$status);
$data = $this->db->getAll($query);

the idea is in having a function to parse placeholders in arbitrary query part instead of whole query.
I don't bother with native prepared statements though. They pollute PHP scripts with heaps of useless code with not a single benefit.

To answer updated question
as you've been told, you can't bind arbitrary query part. But a literal only.
So, make your literal looks like foo% and then bind it usual way.

Upvotes: 0

Pekka
Pekka

Reputation: 449395

You can't add SQL code as a parameter; only data will do. You'll have to force these bits into $query. They won't be escaped then so they shouldn't contain user-submitted data.

Upvotes: 1

Tschallacka
Tschallacka

Reputation: 28722

What I usually do is the following:

$query = "Select * from `tbl_task` where 1=1";
$arr = array();
if (!empty($name)) 
    {
    $query .= " AND `name` = :NAME";
    $arr['NAME'] = $name;
    }
if (!empty($status))
    {
    $query .= " AND `status` = :STATUS";
    $arr['STATUS'] = $status;
    }
$this->db->beginTransaction();
try
    {  
    $tmp = $this->db->prepare($query);  
    $tmp->execute($arr);
    $this->db->commit();
    }
catch(PDOException $ex)
    {
    $this->db->rollBack();
    $this->log->error($ex->getMessage());
    }

Upvotes: 2

Related Questions