You should first read below article before continue with this article.
Many of the matured databases support the use of prepared statements. Prepares statements can be considered as a kind of compiled template for the SQL that as application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:
- The query needs to be parsed (or prepared) once but can be executed multiple times with same or different values. When query is prepared database analyzes, compile and optimized its plan for executing the query. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements will use fewer resources and so runs faster.
- The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application uses only prepared statements then developer can be sure that no SQL injection will occur.
For replacing different values in prepare statement we can use place holders in query. Placeholders can be of two types:
- Named placeholder
- Positional placeholder
Let’s try with some examples:
Multiple inserts with prepared statements and named placeholder.
[cc lang=”php”]
prepare(‘insert into info (name, value) values (:name, :value)’);
// Below two lines will bind the php variable to the prepared statements.
$query->bindParam(‘:name’,$name);
$query->bindParam(‘:value’,$value);
// Insert 1
$name=’name1′; $value=’value1′;
$query->execute();
// Insert 2
$name=’name2′; $value=’value2′;
$query->execute();
?>
[/cc]
Multiple Inserts with prepared statements and positional ? place holder
[cc lang=”php”]
prepare(‘insert into info (name, value) values (?, ?)’);
// Below two lines will bind the php variable to the prepared statements.
$query->bindParam(1,$name);
$query->bindParam(2,$value);
// Insert 1
$name=’name1′; $value=’value1′;
$query->execute();
// Insert 2
$name=’name2′; $value=’value2′;
$query->execute();
?>
[/cc]
Source : php.net
