Update data in MYSQL database table using PHP | Edit operation

In this tutorial, we are going to perform an update operation.PHP script and MySQL update query are used to update the data in the database. This is known as edit operation in PHP. Update data from the table is done by the Update query.

First of all, you need to know how to insert and display data from the table? When the data displayed on the web page then you can edit just by click on button or link. You can update directly using MYSQL panel but that is not a better idea if you are working on the web application.

You can manage update operations on the website using PHP and MYSQL. Now, we talk about the PHP script code and MYSQL database step by step process.

Create an MYSQL database table

Data is updated only when the data from the database is being displayed on the website. This is a proper way to edit user information. First of all, create an MYSQL database. Create a table inside the database.

We will perform an update operation on this database table. Let's create a database table using the MySQL query.


CREATE TABLE  `users` (

 `id` INT( 11 ) NOT NULL ,
 `name` VARCHAR( 100 ) NOT NULL ,
 `age` INT( 3 ) NOT NULL ,
 `email` VARCHAR( 100 ) NOT NULL
) ENGINE = INNODB DEFAULT CHARSET = latin1;

Make a connection file

Now, we create a connection file. This is a best and standard way that you are creating a PHP file for the connection. Let's create a connection between MySQL and PHP. We use mysqli_connect() to create a new connection. In the mysqli_connect() , we pass four parameters.
config.php


<?php
$databaseHost = 'localhost';
$databaseName = 'test';
$databaseUsername = 'root';
$databasePassword = '';
$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, 
$databaseName);  
?>

1. Define database host.

2. Define your created database name .

3. What is your database username ?

4. Define your database password .

Insert data into the database for update operation

You must have seen on Facebook. When you edit your profile, you see your already inserted data. The signup form is known as the registration form. In the previous tutorial, we created a registration form in PHP. This is a process of inserting data into MYSQL database table using PHP and HTML form. Follow the process -

1. Insert data into database .

2. Display in HTML table using PHP .

3. Update by id .

Here, we create a simple HTML form. We insert data into the database using HTML form.

1. Create an HTML form

Let's create an HTML form. When users fill data in the HTML form, the data is inserted into the MYSQL database table.
add.html

<html>
<head>
	<title>Insert Data </title>
</head>

<body>


 
 Name
 

 
Age
 

 
 Email 


 





</body>
</html>

2. Create a PHP script for insertion.

Now, we create a PHP script to insert the data into the database. In this script, we use the insert query to insert data into the database table.

1. First of all, we connect the MYSQL database.

2. Use if condition. (if input values are set then insert the value in the database ) .

3. Now, we perform a small validation. If the user does not enter the value in input boxes then display the statement (Please enter the name or email or age).

4. After that, we will use the Insert query to insert the data into the database. It is a proper way to perform the update operation.
add.php

Insert into database

<?php

include_once("config.php");

if(isset($_POST['Submit'])) {	
$name = mysqli_real_escape_string($mysqli, $_POST['name']);
$age = mysqli_real_escape_string($mysqli, $_POST['age']);
$email = mysqli_real_escape_string($mysqli, $_POST['email']);
if(empty($name) || empty($age) || empty($email)) {
				
if(empty($name)) {
echo 'Name field is empty.
';
}
		
if(empty($age)) {
echo 'Age field is empty.
';
}
if(empty($email)) {
echo 'Email field is empty.
';
}
echo '
Go Back';
} else { 
$result = mysqli_query($mysqli, "INSERT INTO users(name,age,email) VALUES('$name','$age','$email')");
echo 'Data added successfully.';
echo '
<a href="index.php">View Result</a>';
}
}
?>

Display data into an HTML table and create update link

After the insert operation, we display the data into the HTML table. While displaying the information, we use the row id of the database table. We make a link or button for the update by id. By using this link or button we update the data through unique id. Let's display the data in the HTML table and create an updated link.
index.php

Display in Table (Select data from database)


<?php
include_once("config.php");
$result = mysqli_query($mysqli, "SELECT * FROM users ORDER BY id DESC"); 
?>

<html>
<head>	
	<title>Display data in table with edit button </title>
</head>

<body>
<a href="add.html">Add New Data</a>



	<table width='50%'height='15%' border='0'>

	<tr bgcolor='yellow'>
		<td>Name</td>
		<td>Age</td>
		<td>Email</td>
		<td>Update</td>	
	</tr>
	<?php 
	
	while($res = mysqli_fetch_array($result)) { 		
		echo "<tr>";
		echo "<td bgcolor=''>".$res['name']."</td>";
		echo "<td>".$res['age']."</td>";
		echo "<td>".$res['email']."</td>";	
echo "<td bgcolor='green'><a href='edit.php?id=$res[id]'><font color='white'>Edit</a>";         
	}
	?>
	</table>
</body>
</html>

Create a form and data update PHP script

After insert and display data, you can easily update the data. We use MySQL database table row id to update the data. It is important that you can update the user's data by a unique id. Each user id is unique. With Unique id, we can update the data of different users.

Update data by id using PHP

Now, we will perform the code for Update the data in the database table.

1. include connection file.

2. if condition checks the input box is empty or not. If empty then display the message "Empty field".

3. In the update web application, you have to need to select the data from the database and display in text boxes.

4. After the data fetch we will perform the update query using id one by one.

Make an edit form data PHP script

Let's make an HTML form. In this form, we bring data from the database table using id. In this HTML form, we can update already displayed data.
edit.php


<?php
// including the database connection file
include_once("config.php");

?>
<?php
//getting id from url
$id = $_GET['id'];

//selecting data associated with this particular id
$result = mysqli_query($mysqli, "SELECT * FROM users WHERE id=$id");

while($res = mysqli_fetch_array($result))
{
	$name = $res['name'];
	$age = $res['age'];
	$email = $res['email'];
}
?>
<html>
<head>	
	<title>Edit Data</title>
</head>

<body>
	<a href="index.php">Home</a>
	


	
	<form name="form1" method="post" action="editprocess.php">
		<table border="0">
			<tr>
				<td>Name</td>
				<td><input type="text" name="name" value="<?php echo $name;?>"></td>
			</tr>
			<tr>
				<td>Age</td>
				<td><input type="text" name="age" value="<?php echo $age;?>"></td>
			</tr>
		<tr> 
				<td>Email</td>
				<td><input type="text" name="email" value="<?php echo $email;?>"></td>
			</tr>
			<tr>
				<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
				<td><input type="submit" name="update" value="Update"></td>
			</tr>
		</table>
	</form>
</body>
</html>

Now , create another file for update process
editprocess.php

Update (Update Query)


 <?php
include_once("config.php");
if(isset($_POST['update']))
{	

$id = mysqli_real_escape_string($mysqli, $_POST['id']);
$name = mysqli_real_escape_string($mysqli, $_POST['name']);
$age = mysqli_real_escape_string($mysqli, $_POST['age']);
$email = mysqli_real_escape_string($mysqli, $_POST['email']);	
if(empty($name) || empty($age) || empty($email)) {	
if(empty($name)) {
echo 'Name field is empty.
';
}
if(empty($age)) {
echo 'Age field is empty.
';
}
if(empty($email)) {
echo 'Email field is empty.
';
}		
} else {	
$result = mysqli_query($mysqli, "UPDATE users SET name='$name',age='$age',email='$email' WHERE id=$id");
header("Location: index.php");
}
}
?>