Update Data In a MySQL Table Using MySQLi and PDO #
MySQL хүснэгтэд өгөгдлийг MySQLi болон PDO ашиглан шинэчлэх #
The UPDATE statement is used to update existing records in a table:
UPDATE мэдэгдэлийг хүснэгтэд байгаа бичлэгүүдийг шинэчлэхэд ашигладаг:
UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value
Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
UPDATE синтакс дахь WHERE гэсэн өгүүлбэрт анхаарлаа хандуулаарай: WHERE өгүүлбэрт аль бичлэг эсвэл бичлэгийг шинэчлэхийг зааж өгсөн болно. Хэрэв та WHERE гэсэн заалтыг орхисон бол бүх бүртгэл шинэчлэгдэх болно!
To learn more about SQL, please visit our SQL tutorial.
SQL-ийн талаар илүү ихийг мэдэхийг хүсвэл манай SQL заавраар зочилно уу.
Let’s look at the “MyGuests” table:
“MyGuests” хүснэгтийг харцгаая:
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | john@example.com | 2014-10-22 14:26:15 |
| 2 | Mary | Moe | mary@example.com | 2014-10-23 10:22:30 |
The following examples update the record with id=2 in the “MyGuests” table:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>
Example (MySQLi Procedural) #
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example (PDO) #
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
// Prepare statement
$stmt = $conn->prepare($sql);
// execute the query
$stmt->execute();
// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?> After the record is updated, the table will look like this:
Бичлэгийг шинэчилсний дараа хүснэгт дараах байдалтай байна.
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | john@example.com | 2014-10-22 14:26:15 |
| 2 | Mary | Doe | mary@example.com | 2014-10-23 10:22:30 |
