PHP MySQL PDO UPDATE with prepared statement and question mark placeholders
From w3cyberlearnings
Contents |
PHP PDO Update Record
Update Record by using question mark place holder and bind parameter.
Syntax Update Record
$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=? WHERE id=?";
$sq = $db->prepare($sql);
foreach ($update_array as $a) {
$sq->bindParam(1, $a['email'], PDO::PARAM_STR);
$sq->bindParam(2, $a['id'], PDO::PARAM_INT);
$sq->execute();
//echo $sq->rowCount();
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)
);
// fetch as NUM
$dns = 'mysql:host=localhost;dbname=w3cyberlearning';
$user = 'user2000';
$pass = 'password2000';
$db = new PDO($dns, $user, $pass);
// update records
$sql = "UPDATE user_infor SET email=? WHERE id=?";
$sq = $db->prepare($sql);
// loop through array
foreach ($update_array as $a) {
$sq->bindParam(1, $a['email'], PDO::PARAM_STR);
$sq->bindParam(2, $a['id'], PDO::PARAM_INT);
$sq->execute();
//echo $sq->rowCount();
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>';
?>
