MySQL uses the UPDATE SQL command to update.
For example, let’s create a test table with a list of all users:
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 their updates:
<!DOCTYPE html>
<html>
<head>
<title>All users</title>
<meta charset="utf-8" />
</head>
<body>
<?php
//connect to db
$conn = new mysqli("localhost", "root", "password", "testdb");
if ($conn->connect_error) { //if connection have errors
die("Error: " . $conn->connect_error);
}
//Our SQL request
$sql = "SELECT * FROM Users";
if ($result = $conn->query($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"] . "'>change</a></td>";
echo "</tr>";
}
echo "</table>";
$result->free(); //Frees the memory occupied by the query results
} else {
//Output an error if the connection did not make
echo "Error: " . $conn->error;
}
//close database connection
$conn->close();
?>
</body>
</html>
The change button sends users to the update.php page, where the GET method passes the post id:
<?php
$conn = new mysqli("localhost", "root", "password", "testdb");
if ($conn->connect_error) {
die("Ошибка: " . $conn->connect_error);
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Update my post</title>
<meta charset="utf-8" />
</head>
<body>
<?php
if ($_SERVER["REQUEST_METHOD"] === "GET" && isset($_GET["id"])) {
$userid = $conn->real_escape_string($_GET["id"]);
$sql = "SELECT * FROM Users WHERE id = '$userid'";
if ($result = $conn->query($sql)) {
if ($result->num_rows > 0) {
foreach ($result as $row) {
$username = $row["name"];
$userage = $row["age"];
}
//Now we will update our user with the POST method
echo "
<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>";
}
$result->free();
} else {
echo "Error: " . $conn->error;
}
} elseif (isset($_POST["id"]) && isset($_POST["name"]) && isset($_POST["age"])) {
$userid = $conn->real_escape_string($_POST["id"]);
$username = $conn->real_escape_string($_POST["name"]);
$userage = $conn->real_escape_string($_POST["age"]);
//Our SQL for update
$sql = "UPDATE Users SET name = '$username', age = '$userage' WHERE id = '$userid'";
if ($result = $conn->query($sql)) {
echo "Updated: " . $conn->error;
} else {
echo "Error: " . $conn->error;
}
} else {
echo "Data error";
}
$conn->close();
?>
</body>
</html>
Update data in MySQL. PHP function Example
May 8, 2022