PHP
Update Data in MySQL
Update Data
In a MySQL Table Using MySQLi and PDO
The UPDATE
statement is used to update existing records in a table:
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!
To learn
more about SQL, please visit our SQL tutorial.
Let's look
at the "MyGuests" table:
id firstname lastname email 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:
Example
(MySQLi Object-oriented)
<?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 email 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
0 comments: