Thursday, April 21, 2011

PHP Dynamic Prepared Statement Example

Prepared statement is currently one of the safest techniques to manipulate your database. The only problem is SQL statement has to be compiled first and then you have to populate/bind your parameters/values.

Here is how to create dynamic prepared statement in PHP. This example assumes you have setup database connection using PDO.

Requirement:
$host = "...";
$username = "...";
$password = "...";

$dbh = new PDO("mysql:host=$host;dbname=database_name", 
$username, $password);
Last line creates a PDO object.

1. Declare your initial SQL statement for selecting data:
$query = "SELECT * FROM tblExample WHERE 1=1";
Notice there is 1=1 after the WHERE clause this will help you avoid deciding which parameter goes first because you will have to use AND to combine other parameters later on.

2. Create an empty array for storing your binding name and parameters:
$list_array = "";

3. Now, perform a check on a variable to see if it qualifies to go into your SQL statement (this example receives values from a submitted form using POST method):
if(!empty($_POST["userName"]))
{
 $query .= " AND user_name = :userName";
 $list_array[":userName"] = $_POST["userName"];
}
What the heck is this? If user doesn't fill out his name, the field userName will return empty which is what we're checking for. If the field userName is not empty, we append (add on the end) more parameters to the SQL statement with a bind name. Second line inside the IF statement uses our previously created array to store bind name as KEY and value as VALUE. Repeat this step as many times as you like.

4. Close your SQL statement with a parentheses:
$query .= ")";

5. Compile SQL statement using prepare() method of PDO object and store result in a variable:
$statement = $dbh->prepare($query);

6. The fun part. Cycle through your array of binding names and values to bind parameters with your SQL statement:
if($list_array != "")
{
 // bind parameters to values
 foreach($list_array as $key => &$value)
 { 
  $statement->bindParam($key, $value);
 }
}
IF condition checks if there is any data in the array. In case user doesn't enter any information your SQL statement will just return everything from the specified table, in our case tblExample. Inside the IF statement, foreach loop is used to cycle through each item of $list_array and binds parameters using $key as a KEY and $value as VALUE.

(OPTIONAL) - You can print your SQL statement to see what it looks like before it gets executed for testing purposes. If userName field is entered the SQL statement will look like this:
SELECT * FROM tblExample WHERE 1=1 AND user_name = :userName"
:userName will be replaced with what user actually typed in the form.

7. Run your SQL statement:
$statement->execute();

8. Get your results and close database handle:
$result = $statement->fetchAll();
$dbh = null;

That's about it. I figured this out while writing a search function based on user input which is why I used a SELECT statement.

Let me know if you have any questions and I'll be glad to help you.

No comments:

Post a Comment