Description
“PHP Update Data in MySQL” chapter gives information on how to update the data in Database. MySQL tables information can be upgraded by executing UPDATE statement of SQL with the PHP function
mysql_query.
To update any table record, first the record has to be located using conditional clause. Primary key can also be used to match with any of the employee table record.
Syntax
UPDATE table_name SET field1=value1, field2=value2 [WHERE Clause]
- Single/Multiple fields to be updated altogether.
- Using WHERE clause, define the conditions.
- Values in a single table can be updated at a time.
- While updating the selected rows in a table,WHERE clause is the best choice.
Examples
MySQLi (Object-oriented)
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$HostName = "localhost";
$UserName = "root";
$Password = "password";
$dbname="accounts";
// Create connection
$connection = new mysqli($HostName, $UserName, $Password, $dbname);
// Check connection
if ($connection->connect_error)
{
die("Connection failed: " . $connection->connect_error);
}
$sql = "UPDATE users2 SET Username=’William’, Password=’password0′, First=’William’, Last=’Sarah’ WHERE id=1";
if ($connection->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $connection->error;
}
$connection->close();
?>
</body>
</html>
[/php]
The output will be “Record updated successfully” by changing the records in phpmyadmin like below.
MySQLi (Procedural)
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$HostName = "localhost";
$UserName = "root";
$Password = "password";
$dbname="accounts";
// Create connection
$connection = new mysqli($HostName, $UserName, $Password, $dbname);
// Check connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "UPDATE users2 SET Username=’William’, Password=’password0′, First=’William’, Last=’Sarah’ WHERE id=1";
if (mysqli_query($connection, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($connection);
}
mysqli_close($connection);
?>
</body>
</html>
[/php]
Key Points
- MySQL tables can be updated with SQL UPDATE statement.
- To update a record in any table it is required to locate that record by using a conditional clause.