PHP function / procedure for dynamically linking question marks

Question

I am new to PHP. I need to bind parameters in PDO by writing a custom function.

Say that these are 2 sqls that 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 an sql query, and bind the parameters that will be bound to the question marks, no matter how many parameters I pass.

Example: I want to call,

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

Here the function I wrote so far just connected to 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 bind_params function. Any help I would really like.

Answer 1

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

See this example in 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)); 

In particular, 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 request:

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

Answer 2

The following code uses "Lazy", which binds the transfer of data to execute through an array. This allows? which you need to insert, and ANDs to insert depending on the number of columns passed into $ columnArray. I commented on some of your specific database code so you can see how the query is formed. You need to pass an array of column names along with the first part of the sql statement before the WHERE clause .

I added sample data for testing and code to show the request generated along with the parameters for execute (). They must be deleted, and the code with comments restored for verification with the database.

Result Example

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

Array ([0] => Volume [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); ?>