PHP MySQL PDO UPDATE with prepared statement and named placeholders
From w3cyberlearnings
Contents |
PHP PDO Update Record
Update records by using name place holder and bind parameters.
Syntax PDO
// list of array to be update
$update_array = array(
array('email' => '[email protected]', 'id' => 1),
array('email' => '[email protected]', 'id' => 2),
array('email' => '[email protected]', 'id' => 3)
);
$sql = "UPDATE user_infor SET email=:email WHERE id=:id";
$sq = $db->prepare($sql);
foreach ($update_array as $a) {
$aa_tmp = array_keys($a); // make temp array key
$sq->bindParam($aa_tmp[0], $a[$aa_tmp[0]], PDO::PARAM_STR);
$sq->bindParam($aa_tmp[1], $a[$aa_tmp[1]], PDO::PARAM_INT);
$sq->execute();
echo "<br/>";
}
Example 1
<?php
$update_array = array(
array('email' => '[email protected]', 'id' => 1),
array('email' => '[email protected]', 'id' => 2),
array('email' => '[email protected]', 'id' => 3)
);
$dns = 'mysql:host=localhost;dbname=w3cyberlearning';
$user = 'user2000';
$pass = 'password2000';
$db = new PDO($dns, $user, $pass);
// update records
$sql = "UPDATE user_infor SET email=:email WHERE id=:id";
$sq = $db->prepare($sql);
foreach ($update_array as $a) {
$aa_tmp = array_keys($a);
$sq->bindParam($aa_tmp[0], $a[$aa_tmp[0]], PDO::PARAM_STR);
$sq->bindParam($aa_tmp[1], $a[$aa_tmp[1]], PDO::PARAM_INT);
$sq->execute();
echo "<br/>";
}
// select and display records
$sql = "SELECT id,first_name, last_name,
email FROM user_infor WHERE id IN(?,?,?)";
$sq = $db->prepare($sql);
echo '<table border="1">';
echo '<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>';
$sq->execute(array(1, 2, 3));
while ($r = $sq->fetch(PDO::FETCH_NUM)) {
echo '<tr>';
echo '<td>' . $r[0] . '</td>';
echo '<td>' . $r[1] . '</td>';
echo '<td>' . $r[2] . '</td>';
echo '<td>' . $r[3] . '</td>';
echo '</tr>';
}
echo '</table>';
?>
