How to Dynamically Build Queries with Pdo

How to dynamically build queries with PDO

Unfortunately, you can't bind parameters by column names.

What you could try is to dynamically create your SQL command:

$sql = "SELECT * FROM $tableName WHERE $columnName = :value";
$query = $dbh->prepare($sql);
$query->bindParam(':value', $value);

Just make sure to sanitize your parameters/variables if they are coming from elsewhere, to prevent SQL Injection. In this case, $value is safe to a degree but $tableName and $columnName are not -- again, that is most especially if the values for these variables are not provided by you and instead by your users/vistors/etc...

One other thing; please avoid using * and name your columns instead... See some reasons why:

http://www.jasonvolpe.com/topics/sql/

Performance issue in using SELECT *?

See other similar posts here:

Why doesn't binding parameter in ORDER BY clause order the results?

How do I set ORDER BY params using prepared PDO statement?

PDO Dynamic Query Building

You'll need a separate $params parameter to your select method. I took the liberty of providing defaults for the method parameters. Like @userXxxx notes, you don't need a transaction just to do a SELECT.

<?php

class db {

    public $connection; //PDO
    public $dbFields; // This is an array of the fields plus VALUES

    public function select($where = '1', $params = array(), $limit = '', $fetchStyle = PDO::FETCH_ASSOC) { //fetchArgs, etc
        $fields = implode(', ', $this->dbFields);

        //create query
        $query = "SELECT $fields FROM {$this->table} WHERE $where $limit";

        //prepare statement
        $stmt = $this->connection->query($query);

        $stmt->execute($params);

        return $stmt->fetchAll($fetchStyle);
    }

    //...
}

$where = 'userId IN(:userId1, :userId2)';
$params = array(':userId1' => 111, ':userId2' => 2222);
$db->select($where, $params);

Notes:

• If you really want, you can add additional method parameters to match up with all the flexibility of PDOStatement::fetchAll.
• I'm not sure what you mean about $dbFields being "fields plus VALUES". Can you explain?

---

[Edit]

You might want to take a look at the docs/examples for PDOStatement::execute, since that seemed to be where your confusion was rooted--in particular, the $input_parameters method parameter.

PHP PDO Dynamic Query Building

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

PDO Dynamic Query Building with prepared statements

this is funny. i can't answer my question.
i found the solution. it is because of double foreach loop.

$i = 0;
    foreach ($params as $key) {
        $query->bindValue($key, $values[$i], PDO::PARAM_STR);
    $i++;}

Building dynamic PDO Mysql Query

First use Ternary operator with isset().

$category = isset($_GET["category"]) ? $_GET["category"] : "";

Declare an array to hold parameters.

$params = array();

Then set the stub of query into variable.

$sql = "SELECT * FROM parts ";

Set a flag to 0 to switch from WHERE to AND for 2 or more parameters

Start building the rest of your query incrementing $flag adding parameters to $params array

if($category != ""){
        $sql .= " WHERE main_category = ?";
        $params[] = $category;
        $flag++; 
        }

If flag > 0 use AND instead of WHERE

if($search != ""){
       if($flag > 0){
            $sql .= " AND search = ?";

       }else{
            $sql .= " WHERE search = ?";
       }
       $params[] =$search;
       $flag++;

Use "lazy" binding passing data into execute.

$sm->execute($params);

The final code.

<?php
$category = isset($_GET["category"]) ? $_GET["category"] : "";
$search = isset($_GET['search']) ? $_GET['search'] : "";
$subcategory = isset($_GET['subcategory']) ? $_GET['subcategory'] : "";
$params = array();

function getbycategory($category, $search, $subcategory){

    global $db;
    $sql = "SELECT * FROM parts ";
    $flag = 0;
    if($category != ""){
        $sql .= " WHERE main_category = ?";
        $params[] = $category;
        $flag++; 
        }
    if($search != ""){
       if($flag > 0){
            $sql .= " AND search = ?";

       }else{
            $sql .= " WHERE search = ?";
       }
       $params[] =$search;
       $flag++;
        }   
    if($subcategory != ""){
       if($flag > 0){
            $sql .= " AND subcategory = ?";

       }else{
            $sql .= " WHERE subcategory = ?";
       }
       $params[] = $subcategory;
       }   

    echo   $sql; //Remove after testing  
    print_r($params);//Remove after testing     
    $sm = $db->prepare($sql);  
    $sm->execute($params);
    return $sm->fetchAll();
}   
getbycategory($category, $search, $subcategory);

    } 

Reading your code I see $category = '%'; If you require LIKE instead of = you require to prepare FULL literal first.

$category = "%$category%";

PHP PDO with dynamically created where statements

You should build your query and instead of using values inside the query you should use placeholders (? or :placeholder_name):

$query = 'SELECT * FROM table WHERE col IN (?,?,?)';
$stmt = $pdo->prepare($query);
$stmt->execute($_POST['org']);

Go and read https://www.php.net/manual/en/pdo.prepare.php#refsect1-pdo.prepare-examples for more examples

---

Related Topics