PHP PDO Dynamic Query Building

Question

$arr = array();
$from_date = '2015-01-01';
$to_date = '2015-01-31';
$order_no = '25215';
$sql = "SELECT * FROM test";
if(!empty($from_date)&&!empty($to_date))
{
    $sql.=" WHERE txn_date BETWEEN :from_date AND :to_date";
    $arr[] = ":from_date => $from_date";
    $arr[] = ":to_date => $to_date";
    $condition=true;
}
if(!empty($order_no))
{
    if($condition)
    {
       $sql.=" AND ref_number = :order_no";
       $arr[] = ":order_no => $order_no";
    }
    else
    {
       $sql.=" WHERE ref_number = :order_no";
       $arr[] = ":order_no => $order_no";
       $condition=true;
    }
}
$stmt = $db->prepare($sql);
$stmt->execute($arr);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

When executing this query shows a warning like

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

what is wrong with it?

Your ref_number LIKE :order_no might as well be ref_number = :order_no seeing as you have no wildcard characters — Phil
– Phil, Commented Apr 30, 2015 at 6:05

gvgvgvijayan · Accepted Answer · 2015-04-30 06:26:37Z

2

In variable $arr you should replace as like this for all associative index

$arr[':from_date'] = $from_date;
$arr[':to_date'] = $to_date;
$arr[':order_no'] = $order_no;

Also it is good practice to move common code in outer block instead of if/else ladder

if(!empty($order_no))
{
    $arr[':order_no'] = $order_no;
    if($condition)
    {
       $sql.=" AND ref_number = :order_no";
    }
    else
    {
       $sql.=" WHERE ref_number LIKE :order_no";
       $condition=true;
    }
}

As op asked in comment here is the explanation for that

$sql.=" WHERE txn_date BETWEEN :from_date AND :to_date";

Replace this with this one

$sql.=" WHERE date(txn_date) BETWEEN date(:from_date) AND date(:to_date)";

ref_number = :order_no

Instead of the above one type cast it to unsigned integer as like this in both where condition in if/else statement

CONVERT(ref_number,UNSIGNED INTEGER) = :order_no

edited Apr 30, 2015 at 6:26

answered Apr 30, 2015 at 5:56

gvgvgvijayan

2,5051 gold badge25 silver badges37 bronze badges

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

4 Comments

Bineesh Varghese Over a year ago

thank you. date and order_no are string fields. how can handle it?

gvgvgvijayan Over a year ago

you mean in your sql table the column of date and order_no are varchar data type is it right?

Bineesh Varghese Over a year ago

yes. so when i executing the query no row returns. right now the array is like Array ( [from_date] => 2014-01-01 [to_date] => 2014-01-31 ). but i need like Array ( [from_date] => '2014-01-01' [to_date] => '2014-01-31' )

gvgvgvijayan Over a year ago

either convert all column to respective data type or in your sql query type cast it see here for e.g. stackoverflow.com/a/26708807/1593365

Honza Haering · Accepted Answer · 2015-04-30 06:19:50Z

1

Instead of

$arr[] = ":from_date => $from_date";

do

$arr['from_date'] = $from_date;

1 Comment

Bineesh Varghese Over a year ago

i understand. thank you. one more thing, dates and order_no are string fields. how can handle it?

Collectives™ on Stack Overflow

PHP PDO Dynamic Query Building

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related