MySQL uses the UPDATE SQL command to update data in the database.
Let’s create a test base for an example. In which there will be columns id, name, age.
CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)
Now let’s create an index.php file in which we will display a list of all users with a link to update their data:
<!DOCTYPE html>
<html>
<head>
<title>Update user info</title>
<meta charset="utf-8" />
</head>
<body>
<?php
//connect to bd
$conn = mysqli_connect("localhost", "root", "password", "testdb");
//check connection on errors
if (!$conn) {
die("Error: " . mysqli_connect_error());
}
//SQL request to get all users from "Users" table
$sql = "SELECT * FROM Users";
//
if($result = mysqli_query($conn, $sql)){//connect to db and get users list
echo "<table><tr><th>Name</th><th>Age</th><th></th></tr>";
//create loop and get users infos
foreach($result as $row){
echo "<tr>";
echo "<td>" . $row["name"] . "</td>";
echo "<td>" . $row["age"] . "</td>";
echo "<td><a href='update.php?id=" . $row["id"] . "'>Save</a></td>";
echo "</tr>";
}
echo "</table>";
mysqli_free_result($result);//Frees the memory occupied by the query results
//Output an error if the connection did not make
} else{
echo "Ошибка: " . mysqli_error($conn);
}
//Close database connection
mysqli_close($conn);
?>
</body>
</html>
The save button sends users to the update.php page, where the GET method passes the record id
<?php
$conn = mysqli_connect("localhost", "root", "password", "testdb");
if (!$conn) {
die("Error: " . mysqli_connect_error());
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Update page</title>
<meta charset="utf-8" />
</head>
<body>
<?php
// if a GET request
if($_SERVER["REQUEST_METHOD"] === "GET" && isset($_GET["id"]))
{
$userid = mysqli_real_escape_string($conn, $_GET["id"]);
$sql = "SELECT * FROM Users WHERE id = '$userid'";
if($result = mysqli_query($conn, $sql)){
if(mysqli_num_rows($result) > 0){
foreach($result as $row){
$username = $row["name"];
$userage = $row["age"];
}
echo "<h3>Update user</h3>
<form method='post'>
<input type='hidden' name='id' value='$userid' />
<input type='text' name='name' value='$username' /></p>
<input type='number' name='age' value='$userage' /></p>
<input type='submit' value='Save'>
</form>";
}
else{
echo "<div>User not find</div>";
}
mysqli_free_result($result);
} else{
echo "Error: " . mysqli_error($conn);
}
}
elseif (isset($_POST["id"]) && isset($_POST["name"]) && isset($_POST["age"])) {
$userid = mysqli_real_escape_string($conn, $_POST["id"]);
$username = mysqli_real_escape_string($conn, $_POST["name"]);
$userage = mysqli_real_escape_string($conn, $_POST["age"]);
$sql = "UPDATE Users SET name = '$username', age = '$userage' WHERE id = '$userid'";
if($result = mysqli_query($conn, $sql)){
echo "User updated";
} else{
echo "Error: " . mysqli_error($conn);
}
}
else{
echo "Wrong data";
}
mysqli_close($conn);
?>
</body>
</html>
Update data in MySQL. PHP OOP Example
May 8, 2022