Welcome back to this tutorial of data update in MySQL database. Previously we have seen how to connect php with MySQL database, inserting data into database and retrieving data from database. Now, we will see how to update that data.The updatation operation is very simple and we can do it by just running an UPDATE query.
we are using the same database as in previous example. Please make sure you’ve gone through previous tutorials at Here. Alternatively, you can download the source code from the link at the bottom.
Connect to MySQL database
We first have to connect to database. So, we’re using the file connect.php with below code.
<?php $servername = "localhost"; $username = "root"; $password = ""; try { $conn = new PDO("mysql:host=$servername;dbname=firstdb", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
We are using an object oriented PDO approach for connection of php with database. The reason behind is, it does a lot of work in just few lines, has a capability of exception handling, and prevents the most common SQL injections.
Implement Data Update Logic
Now, we have implemented all the logic in the only one file. Create the update.php file and paste the following code in it.This file is responsible for updating the details through an SQL query.
<html>
<head>
<title>Update data in mysql database using php</title>
</head>
<body>
<center>
<h1>Update data in mysql database using php</h1>
<form action="update.php" method="post">
<input type="text" name="id" placeholder="Enter ID" method="post" required="required"/>
<input type="submit" name="subbtn" value="Submit" method="post"/></br>
<?php include "connect.php"; //Mysqli connection $con=mysqli_connect("localhost","root","","firstdb"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } if(isset($_POST['subbtn'])) { $id=$_POST['id']; $result = $con->query("SELECT name FROM users WHERE id=$id")
or trigger_error($con->error);
$name = $result->fetch_array(MYSQL_BOTH);
?>
Name:<?php echo $name['name']; ?>
<input type="submit" name="chngval" value="Change Name" method="post"/>
<?php } ?>
</form>
<?php if(isset($_POST['chngval'])) {?>
<form action="update.php" method="post">
<input type="text" name="id" placeholder="Enter ID" method="post" required="required"/>
<input type="text" name="name" placeholder="Enter Name" method="post" required="required"/></br>
<input type="submit" name="submitchng" value="Submit" method="post"/></br>
</br>
<?php } if(isset($_POST['submitchng'])) { $id=$_POST['id']; $name=$_POST['name']; try{ $stmt=$conn->prepare("UPDATE users SET name=:name WHERE id=:id");
$stmt->bindparam(":id",$id);
$stmt->bindparam(":name",$name);
$result=$stmt->execute();
if($result===TRUE)
{
if(($stmt->rowCount())>0)
{
echo "<i>record UPDATED successfully!</i>";
}
else{
echo "<i>No changes made!</i>";
}
}
else{
echo "Something Went Wrong!";
}
return true;
}
catch(PDOException $e)
{
echo $e->getMessage();
return false;
}
}
?>
</form>
</body>
</html>
We have used here prepared statements which prevents data leak. The bindParam() function binds the parameters to the query statement. If the process failed, we will see what is the actual error.
Now we’re done! simply try to change the name through the code we’ve designed so far and check the results as follows.
You’ll get a confirmation message after successful change. See the screenshot below of the confirmation message.
Also have a look at your database to see the change.The details are modified as a result of running the query.
Give it a try and see the effect. The source code is always available to download from following link.