In this tutorial, we will learn how to perform an update operation. A PHP script and a MySQL UPDATE query are used to modify existing data in the database. This is commonly known as the edit operation in PHP.
Before updating, you need to know how to insert and display data from the database. When the data is displayed on a web page, you can easily edit it by clicking an "Edit" or "Update" button. While you can update data directly using the phpMyAdmin panel, that is not a practical approach for end-users working on a web application.
You can seamlessly manage update operations on your website using PHP and MySQL. Below, we will discuss the PHP script and MySQL database step-by-step process using secure PDO (PHP Data Objects).
Create a MySQL Database Table
Data can only be updated if it exists in the database. First, create a MySQL database, and then create a table inside it. We will perform the update operation on this database table. Let's create a table named users using the following SQL query. Note that we have added an AUTO_INCREMENT primary key, which is essential for updating specific rows.
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`age` INT(3) NOT NULL,
`email` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Make a Connection File
Now, we create a database connection file. Creating a separate PHP file for the connection is standard practice. We will use PDO to create a secure connection between PHP and MySQL. Create a file named config.php.
<?php
// config.php
$databaseHost = 'localhost';
$databaseName = 'test';
$databaseUsername = 'root';
$databasePassword = '';
try {
// Create a PDO connection
$pdo = new PDO("mysql:host=$databaseHost;dbname=$databaseName;charset=utf8mb4", $databaseUsername, $databasePassword);
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
die("ERROR: Could not connect. " . $e->getMessage());
}
?>
1. Define your database host (usually localhost).
2. Define your created database name.
3. Define your database username (default is root).
4. Define your database password (default is empty for localhost).
Insert Data into the Database (Preparation for Update)
You must have noticed on websites like Facebook that when you edit your profile, your previously saved data is already visible in the form fields. To edit data, we must first have data in our database.
Let's follow this process:
1. Insert data into the database.
2. Display it in an HTML table using PHP.
3. Update it using its unique ID.
1. Create an HTML Form (add.html)
Let's create a simple HTML form to insert data into the MySQL database.
Insert Data Form
<html>
<head>
<title>Insert Data</title>
</head>
<body>
<form action="add.php" method="POST" name="form1">
<table border="0" width="30%">
<tbody>
<tr>
<td>Name</td>
<td><input name="name" type="text" required /></td>
</tr>
<tr>
<td>Age</td>
<td><input name="age" type="number" required /></td>
</tr>
<tr>
<td>Email</td>
<td><input name="email" type="email" required /></td>
</tr>
<tr>
<td></td>
<td><input name="Submit" type="submit" value="Add User" /></td>
</tr>
</tbody>
</table>
</form>
</body>
</html
2. Create a PHP Script for Insertion (add.php)
Now, we create a PHP script (add.php) to securely insert the data using PDO Prepared Statements.
Insert into Database
<?php
// Include the database connection file
require_once("config.php");
if(isset($_POST['Submit'])) {
$name = trim($_POST['name']);
$age = trim($_POST['age']);
$email = trim($_POST['email']);
// Basic validation
if(empty($name) || empty($age) || empty($email)) {
if(empty($name)) { echo "<span style='color:red;'>Name field is empty.</span><br/>"; }
if(empty($age)) { echo "<span style='color:red;'>Age field is empty.</span><br/>"; }
if(empty($email)) { echo "<span style='color:red;'>Email field is empty.</span><br/>"; }
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} else {
// Securely insert data using PDO
$sql = "INSERT INTO users (name, age, email) VALUES (:name, :age, :email)";
$stmt = $pdo->prepare($sql);
$stmt->execute([':name' => $name, ':age' => $age, ':email' => $email]);
echo "<span style='color:green;'>Data added successfully.</span>";
echo "<br/><a href='index.php'>View Results</a>";
}
}
?>
Display Data in an HTML Table and Create an Update Link
After inserting data, we display it in an HTML table. While displaying the information, we attach the database row id to an "Edit" button. This unique ID allows us to target and update specific users. Let's create the display page (index.php).
Display Data & Edit Button (index.php)
<?php
// Include the database connection file
require_once("config.php");
// Fetch data
$sql = "SELECT * FROM users ORDER BY id DESC";
$stmt = $pdo->query($sql);
?>
<html>
<head>
<title>Display Data with Edit Button</title>
</head>
<body>
<a href="add.html">Add New Data</a><br/><br/>
<table width='60%' border='1' style='border-collapse: collapse; text-align: left;'>
<tr style='background-color: #f2f2f2;'>
<th style='padding: 8px;'>Name</th>
<th style='padding: 8px;'>Age</th>
<th style='padding: 8px;'>Email</th>
<th style='padding: 8px;'>Update</th>
</tr>
<?php
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";
echo "<td style='padding: 8px;'>" . htmlspecialchars($row['name']) . "</td>";
echo "<td style='padding: 8px;'>" . htmlspecialchars($row['age']) . "</td>";
echo "<td style='padding: 8px;'>" . htmlspecialchars($row['email']) . "</td>";
echo "<td style='padding: 8px; background-color: #4CAF50; text-align: center;'>
<a href=\"edit.php?id=$row[id]\" style='color: white; text-decoration: none;'>Edit</a>
</td>";
echo "</tr>";
}
?>
</table>
</body>
</html>
Create a Form and Data Update PHP Script
Now, we will fetch the selected user's data using their unique ID and populate it inside an HTML form so the user can edit it.
Make an Edit Form (edit.php)
In this script, we capture the id from the URL. Security Note: Always use prepared statements when fetching data based on a URL parameter to prevent SQL Injection.
<?php
// Include the database connection file
require_once("config.php");
// Get id from URL parameter
$id = $_GET['id'];
// Securely select data associated with this particular id
$sql = "SELECT * FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if record exists
if($row) {
$name = $row['name'];
$age = $row['age'];
$email = $row['email'];
} else {
die("Record not found.");
}
?>
<html>
<head>
<title>Edit Data</title>
</head>
<body>
<a href="index.php">Home</a><br/><br/>
<form name="form1" method="POST" action="editprocess.php">
<table border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" value="<?php echo htmlspecialchars($name); ?>" required></td>
</tr>
<tr>
<td>Age</td>
<td><input type="number" name="age" value="<?php echo htmlspecialchars($age); ?>" required></td>
</tr>
<tr>
<td>Email</td>
<td><input type="email" name="email" value="<?php echo htmlspecialchars($email); ?>" required></td>
</tr>
<tr>
<td><input type="hidden" name="id" value="<?php echo htmlspecialchars($_GET['id']); ?>"></td>
<td><input type="submit" name="update" value="Update Data"></td>
</tr>
</table>
</form>
</body>
</html>
Now, create the final file that will process the updated information and save it back to the database.
editprocess.php
Update Query Script
<?php
// Include the database connection file
require_once("config.php");
if(isset($_POST['update'])) {
$id = $_POST['id'];
$name = trim($_POST['name']);
$age = trim($_POST['age']);
$email = trim($_POST['email']);
// Check for empty fields
if(empty($name) || empty($age) || empty($email)) {
if(empty($name)) { echo "<span style='color:red;'>Name field is empty.</span><br/>"; }
if(empty($age)) { echo "<span style='color:red;'>Age field is empty.</span><br/>"; }
if(empty($email)) { echo "<span style='color:red;'>Email field is empty.</span><br/>"; }
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
} else {
// Securely update the database using PDO Prepared Statements
$sql = "UPDATE users SET name = :name, age = :age, email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':name' => $name,
':age' => $age,
':email' => $email,
':id' => $id
]);
// Redirect back to the display page
header("Location: index.php");
exit();
}
}
?>
By following these steps, you can successfully and securely update data in a MySQL database using PHP programming.
Recommended Posts:-
