This tutorial is going to show you how to retrieve results from a database using PHP, then edit the row and save it back to the database. If you haven’t followed our other tutorials you might want to check out:
- Reading from a MySQL Database Using PHP and MySQLi
- Inserting Data Into a MySQL Database Using PHP and MySQLi
Assumptions
We’re going to assume you already have a development environment setup with PHP, MySQL, and Apache and that you have a database setup with a user to edit.
Here is our database schema
And here are our test users
Setup the Database Connection
Let’s create a file on our server called display.php. In this file, we’ll connect to the database and display all of our users on the page.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$conn->close();
?>
Next, we will query for all of the users.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
if (!$results = $conn->query("SELECT * FROM users")){
echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$conn->close();
?>
Then we will output all of the user information in a simple table. First, let’s set up our basic table.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
if (!$results = $conn->query("SELECT * FROM users")){
echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$conn->close();
?>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Username</th>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</table>
Now, we need to populate the table with the data from our $results array. We’ll loop through the results and add them to the table.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
if (!$results = $conn->query("SELECT * FROM users")){
echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$conn->close();
?>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Username</th>
</tr>
<?php while($row = $results->fetch_assoc()){ ?>
<tr>
<td><?= $row['id']?></td>
<td><?= $row['name']?></td>
<td><?= $row['username']?></td>
</tr>
<?php }?>
</table>
Next, we’ll modify our table to add an edit link to each row.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
if (!$results = $conn->query("SELECT * FROM users")){
echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$conn->close();
?>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Username</th>
<th>actions</th>
</tr>
<?php while($row = $results->fetch_assoc()){ ?>
<tr>
<td><?= $row['id']?></td>
<td><?= $row['name']?></td>
<td><?= $row['username']?></td>
<td><a href="edit.php?id=<?= $row['id']?>">edit</a></td>
</tr>
<?php }?>
</table>
At this point I’m just going to add a little bit of CSS to make our table look better then we’ll be done with the display.php file.
<style>
table{
border-collapse: collapse;
}
table th, table td{
border:1px solid #ccc;
padding:.3em;
margin:0;
}
table th{
background: #778899;
}
table tr:nth-child(odd) {
background: #efefef;
}
</style>
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
if (!$results = $conn->query("SELECT * FROM users")){
echo "Select statement failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$conn->close();
?>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Username</th>
<th>actions</th>
</tr>
<?php while($row = $results->fetch_assoc()){ ?>
<tr>
<td><?= $row['id']?></td>
<td><?= $row['name']?></td>
<td><?= $row['username']?></td>
<td><a href="edit.php?id=<?= $row['id']?>">edit</a></td>
</tr>
<?php }?>
</table>
Your table should look like this.
Editing the Data Using PHP and MySQLi
Next, we need to make our edit.php file. This file will display the data for the selected row and allow you to change it. We’ll start with our database connection.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$conn->close();
?>
<h2>Edit Record</h2>
Next, we’ll grab the variable for ID from our URL. If you click at the edit link from display.php you’ll see something like this in the address bar: https://localhost:8888/edit.php?id=3 . We need to grab the number 3 and query for that record.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$id = $_GET["id"];
$conn->close();
?>
<h2>Edit Record</h2>
Now we need to query for that record. To do that we’ll create a prepared statement. What you will see is a query that has a question mark in it. That question mark is a template. The ? will be replaced by the value that we include in bind_parm. In bind param you see: $stmt->bind_param(“i”, $id); The first parameter, “i”, is saying that the first parameter passed should be an integer. The $id is passing in the value we grabbed from the get variable.
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$id = $_GET["id"];
//prepared prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
//bind the paremeters
$stmt->bind_param("i", $id);
//execute the statement
$stmt->execute();
if($stmt->affected_rows === 0) {
echo ('No rows updated');
}else{
$result = $stmt->get_result();
}
$stmt->close();
$conn->close();
?>
<h2>Edit Record</h2>
Next, we need to use the result data to populate our edit form. We’ll set up our form. I also added just a few lines of CSS to change our display.
<style>
label{
width:80px;
display:inline-block;
}
div{
margin-bottom:.5em;
}
</style>
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$id = $_GET["id"];
//prepared prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
//bind the paremeters
$stmt->bind_param("i", $id);
//execute the statement
$stmt->execute();
if($stmt->affected_rows === 0) {
echo ('No rows updated');
}else{
$result = $stmt->get_result();
}
$stmt->close();
$conn->close();
?>
<h2>Edit Record</h2>
<form method="post" action="edit.php">
<div><label>id</label><input type="text" name="id" id="id" value=""/></div>
<div><label>name</label><input type="text" name="name" id="name" value=""/></div>
<div><label>username</label><input type="text" name="username" id="username" value=""/></div>
<input type="submit" value="Submit">
</form>
Now your form should look something like this.
Next, we need to get our result and populate the form using $row = $result->fetch_assoc();. Then we’ll fill in our form values. We’re also going to change the input for ID to make it readonly so you cannot change ID.
<style>
label{
width:80px;
display:inline-block;
}
div{
margin-bottom:.5em;
}
</style>
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$id = $_GET["id"];
//prepared prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
//bind the paremeters
$stmt->bind_param("i", $id);
//execute the statement
$stmt->execute();
if($stmt->affected_rows === 0) {
echo ('No rows updated');
}else{
$result = $stmt->get_result();
}
$stmt->close();
$conn->close();
$row = $result->fetch_assoc();
?>
<h2>Edit Record</h2>
<form method="post" action="edit.php">
<div><label>id</label><input type="text" name="id" id="id" value="<?= $row['id']?>" readonly/></div>
<div><label>name</label><input type="text" name="name" id="name" value="<?= $row['name']?>"/></div>
<div><label>username</label><input type="text" name="username" id="username" value="<?= $row['username']?>"/></div>
<input type="submit" value="Submit">
</form>
Ok. Now when the form is submitted it is sent back to the same page, edit.php. In edit.php we will check for the name and username. If they are present we will update the database.
<style>
label{
width:80px;
display:inline-block;
}
div{
margin-bottom:.5em;
}
</style>
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$id = $_GET["id"];
if(isset($_GET['name']) and isset($_GET['username'])) {
// prepare the update statement
$update = $conn->prepare("UPDATE users SET users.name = ?, users.username = ? WHERE users.id = ?;");
//bind the paremeters
$update->bind_param("ssi", $_GET['name'], $_GET['username'], $id);
//execute the statement
$update->execute();
if($update->affected_rows === 0) {
echo ('No rows updated');
}else{
echo ("{$update->affected_rows} rows updated");
}
}
//prepared prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
//bind the paremeters
$stmt->bind_param("i", $id);
//execute the statement
$stmt->execute();
if($stmt->affected_rows === 0) {
echo ('No rows updated');
}else{
$result = $stmt->get_result();
}
$stmt->close();
$conn->close();
$row = $result->fetch_assoc();
?>
<h2>Edit Record</h2>
<form method="get" action="edit.php">
<div><label>id</label><input type="text" name="id" id="id" value="<?= $row['id']?>" readonly/></div>
<div><label>name</label><input type="text" name="name" id="name" value="<?= $row['name']?>"/></div>
<div><label>username</label><input type="text" name="username" id="username" value="<?= $row['username']?>"/></div>
<input type="submit" value="Submit">
</form>
There is a lot of code changed above but mostly you want to look inside if(isset($_GET[‘name’]) and isset($_GET[‘username’])) {. We’re simply checking if the GET variables are set and if they are we set up our prepared statement and execute it. You’ll notice the bind_params: $update->bind_param(“ssi”, $_GET[‘name’], $_GET[‘username’], $id);. Here we’re saying the first two params are string and the last is integer. Then we add those variables in order.
Now your form should look like this.
Then we can edit the row and click submit to see our changes reflected in the screen. In my case, I changed Adrienne’s username to “bob”.
The last thing I want to do is add a link back to the main page, display.php.
<style>
label{
width:80px;
display:inline-block;
}
div{
margin-bottom:.5em;
}
</style>
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("MySQL Connection failed: " . $conn->connect_error);
}
$id = $_GET["id"];
if(isset($_GET['name']) and isset($_GET['username'])) {
// prepare the update statement
$update = $conn->prepare("UPDATE users SET users.name = ?, users.username = ? WHERE users.id = ?;");
//bind the paremeters
$update->bind_param("ssi", $_GET['name'], $_GET['username'], $id);
//execute the statement
$update->execute();
if($update->affected_rows === 0) {
echo ('No rows updated');
}else{
echo ("{$update->affected_rows} rows updated");
}
}
//prepared prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
//bind the paremeters
$stmt->bind_param("i", $id);
//execute the statement
$stmt->execute();
if($stmt->affected_rows === 0) {
echo ('No rows updated');
}else{
$result = $stmt->get_result();
}
$stmt->close();
$conn->close();
$row = $result->fetch_assoc();
?>
<h2>Edit Record</h2>
<form method="get" action="edit.php">
<div><label>id</label><input type="text" name="id" id="id" value="<?= $row['id']?>" readonly/></div>
<div><label>name</label><input type="text" name="name" id="name" value="<?= $row['name']?>"/></div>
<div><label>username</label><input type="text" name="username" id="username" value="<?= $row['username']?>"/></div>
<input type="submit" value="Submit">
</form>
<a href="display.php?id=<?= $row['id']?>">Back to Display</a>
Now our form will have a link back to our main page.
If you click on the link you will see the main page with Adrienne’s username set to “bob” now.
Conclusion
Hopefully, this tutorial has helped you learn more about editing data in a MySQL database using the PHP MySQLi libraries. If you have any questions feel free to leave them in the comments.
Johnathan Ward is an experienced developer and consultant that writes tutorials to help other developers. In his day job, he is an IBM Watson Consultant with several years of experience deploying and customizing Watson Explorer solutions.