PHP function/procedure to bind question marks dynamically

Question

I'm fairly new to PHP. I'm in need to bind the parameters in PDO by writing a custom function.

Say these are the 2 sqls I have.

sample_sql_1="select f_name, age, address from table1 where l_name=? and dob >= ? and cty =?"

sample_sql_2="select * from table2 where cty=?"

I would like to write a function that accepts the sql query in question & bind the parameters to be bound to question marks irrespective of how many parameters I pass.

Example: I want to call say,

bind_params(sample_sql_1,array($name,$dob,$cty));
bind_params(sample_sql_2,array($cty));

Here's the function I've writen so far to just connect to the DB

function pdo_db_query($query) {
    try {
    # MySQL with PDO_MYSQL
    $DBH = new dbconn(); // Create DB connection

    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    $STH = $DBH->prepare($query);

    // Please help to create a dynamic function to bind
     bind_params(sample_sql_1,array($name,$dob,$cty));
     bind_params(sample_sql_2,array($cty));

    / Execute the query
    $STH->execute();

    # setting the fetch mode
    $STH->setFetchMode(PDO::FETCH_ASSOC);

    // Create temporary array variable
    $json_arr = array();

    while ($row = $STH->fetch()) {
        $json_arr[] = $row;
    }

    # Close the connection
    $DBH = null;

    // Return the result set as a json
    echo json_encode($json_arr);
    } catch (PDOException $e) {
    echo $e->getMessage();
    var_dump($e->getMessage());
    }
}

I need help writing the function "bind_params". Any help would greatly benefit me please.

Answer 1

You don't necessarily need bind_params(), you can just provide the values as an array to execute().

See this example from the documentation:

/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));

Specifically for your case:

// add a parameter for the values
function pdo_db_query($query, $params = array()) {
    try {
    # MySQL with PDO_MYSQL
    $DBH = new dbconn(); // Create DB connection

    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
    $STH = $DBH->prepare($query);

    // Execute the query with the given params
    $STH->execute($params);

    # setting the fetch mode
    $STH->setFetchMode(PDO::FETCH_ASSOC);

    // Create temporary array variable
    $json_arr = array();

    while ($row = $STH->fetch()) {
        $json_arr[] = $row;
    }

    # Close the connection
    $DBH = null;

    // Return the result set as a json
    echo json_encode($json_arr);
    } catch (PDOException $e) {
    echo $e->getMessage();
    var_dump($e->getMessage());
    }
}

To use this with a LIKE query:

$query = "SELECT * FROM table WHERE field LIKE ?";
$params = array( '%' . $searchvalue . '%' );
$result = pdo_db_query( $query, $params );

Answer 2

The following code uses "Lazy" binding passing data into execute via array. It enables the ? place holders to be inserted and ANDs to be inserted depending on the number of columns passed in $columnArray. I have commented out some of your database specific code to enable you to see how query is formed.You require to pass an array of column names along with the first part of sql statement before WHERE clause.

I have added sample data for testing and the code to show query formed along with parameters for execute(). These should be removed and the commented code reinstated to test with database.

Sample result

select f_name, age, address from table1 WHERE name = ? AND dob = ? AND cty = ?

Array ( [0] => Tom [1] => 2014-11-11 [2] => London )

 function pdo_db_query($query,$columnArray) {
    /* try {
    # MySQL with PDO_MYSQL
    $DBH = new dbconn(); // Create DB connection

    $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $DBH->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

     // Please help to create a dynamic function to bind
     bind_params(sample_sql_1,array($name,$dob,$cty));
     bind_params(sample_sql_2,array($cty));
  */
   // Set the first clause to WHERE
   $clause = " WHERE ";
 foreach ($columnArray  as $column) {
    //Add column name and ? placeholder
    $query .= "$clause $column = ?"; 
    //Change WHERE to And for remaining conditions
    $clause = " AND "; 
 } 
   //This echo is to show query
   echo $query."<BR>";


   // Execute query using Lazy Binding passing data into execute via array
   /*$STH->execute($paramArray);

    /*
    // Create temporary array variable
    $json_arr = array();

    while ($row = $STH->fetch()) {
        $json_arr[] = $row;
    }

    # Close the connection
    $DBH = null;
    */
    }
    // Parameters for testing
    $name ="Tom";
    $dob ="2014-11-11";
    $cty ="London";
    $paramArray1 = array($name,$dob,$cty);
    $paramArray2 = array($cty);
    $columnArray1 = array("name","dob","cty"); 
    $columnArray2 = array("cty"); 
    $query = "select f_name, age, address from table1";

    pdo_db_query($query,$columnArray2) ;
     print_r($paramArray2);
    ?>