Update Data From MySQL Database in PHP

Fetch and update data from database using form in php; In this tutorial, i would love to show you on how to get/fetch data from MySQL database using form in PHP. And as well as display this data on html form.

In this how to fetch and update data from database in php example; i will create database, and database connecting file. Then will fetch data and display in html form in PHP. After that, i will update form data from MySQL database in PHP.

How to Fetch and Update Data From MySQL Database in PHP

Let’s use the below given steps to fetch & update data from MySQL database using form in PHP:

Step 1 – Create PHP Project
Step 2 – Execute SQL query to Create Table
Step 3 – Connecting PHP App to Database
Step 4 – Fetch Data From MySQL Database in PHP by Id
Step 5 – Display Data In Html From MySQL DB with PHP
Step 6 – Update Data From MySQL Database
Step 7 – Start PHP App

Step 1 – Create PHP Project

In step 1, navigate to local web server directory. And inside directory, create one folder. And you can name this folder anything.

For example, i will use xampp server so, i willl navigate to C/xampp/htdocs/.

Here, I will “demo” the name of this folder. Then open this folder in any text editor (i will use sublime text editor).

Step 2 – Execute SQL query to Create Table

In step 2, open your browser and type http://localhost/phpmyadmin. Then create database and table by running the following sql query:

CREATE DATABASE my_db;


CREATE TABLE `customers` (
`custId` INT(11) NOT NULL AUTO_INCREMENT,
`fname` VARCHAR(255) NULL DEFAULT NULL,
`lname` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`custId`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

INSERT INTO `customers` (`custId`, `fname`, `lname`, `email`, `created`) VALUES (NULL, 'Tiago', 'Sam', '[email protected]', NULL), (NULL, 'Anil', 'Kumar', '[email protected]', NULL);

Step 3 – Connecting PHP App to Database

In step 3, create a php file that named mydbCon.php. Which is used to connect phpmyadmin mysql database to project (demo).

So, now create mydbCon.php file and add the below given code into your file:

<?php

    $hName='localhost'; // host name

    $uName='root';   // database user name

    $password='';   // database password

    $dbName = "my_db"; // database name

    $dbCon = mysqli_connect($hName,$uName,$password,"$dbName");

      if(!$dbCon){
          die('Could not Connect MySql Server:' .mysql_error());
      }
?>

Step 4 – Fetch Data From MySQL Database in PHP by Id

In step 4, you need to create a php file that named fetch-data-by-id.php. Because this file code will fetch data from MySQL database table using id in PHP.

Note that, in next step, i will include this file on index.php.

So add the following code on fetch-data-by-id.php file:

<?php


include 'mydbCon.php';

$sql = "SELECT * FROM customers WHERE custId='" . $_GET["custId"] . "'"; // Fetch data from the table customers using id

$result=mysqli_query($dbCon,$sql);

$customer = mysqli_fetch_assoc($result);


?>

Step 5 – Display Data In Html From MySQL DB with PHP

In step 5, you need create a php file that named index.php. Because, on index.php file will display data from database in form.

And when you click submit button, this form submit to update.php file.

Note that, you need to include fetch-data-by-id.php file on index.php file. Don’t worry i have already include this file on index.php file. Because this fetch-data-by-id.php file code will get single row data from database table in php using id.

So, you can add this code in your index.php file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>How to Fetch and Update Data from Database in PHP</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>

<div class="container mt-2">

  <div class="page-header">
      <h2>Fetch and Update Data From Database in PHP Using Form</h2>
  </div>

    <div class="row">
        <div class="col-md-12">
            <?php include 'fetch-data-by-id.php'; ?>
            <form action="update.php" method="POST">

              <input type="hidden" name="custId" value="<?php echo $_GET["custId"]; ?>" class="form-control">

              <div class="form-group">
                <label for="exampleInputEmail1">First Name</label>
                <input type="text" name="fname" class="form-control" value="<?php echo $customer['fname']; ?>">
              </div>                

              <div class="form-group">
                <label for="exampleInputEmail1">Last Name</label>
                <input type="text" name="lname" class="form-control" value="<?php echo $customer['lname']; ?>">
              </div>              

              <div class="form-group">
                <label for="exampleInputEmail1">Email address</label>
                <input type="email" name="email" class="form-control" value="<?php echo $customer['email']; ?>">
              </div>

              <button type="submit" class="btn btn-primary" value="submit">Submit</button>

            </form>
        </div>
    </div>        
</div>

</body>
</html>

Step 6 – Update Data From MySQL Database

In this step 6, you need to create update.php file, So that the data of the form can be updated in the database table.

Now, add the following code into update.php file:

<?php

if(count($_POST)>0){

include 'mydbCon.php';

$sql = "UPDATE customers set custId='" . $_POST['custId'] . "', fname='" . $_POST['fname'] . "', lname='" . $_POST['lname'] . "', email='" . $_POST['email'] . "' WHERE custId='" . $_POST['custId'] . "'"; // update form data from the database

$result=mysqli_query($dbCon,$sql);

echo "Form Data has been updated Successfully in PHP";

}else{
  echo "Form Data has not been updated in PHP";
}

?>

Step 7 – Start PHP App

Finally, you need to open your browser and type http://localhost/demo/?custId=1 this run project on local machine.

How to fetch, display and update data from database using form in php will be looks like shown in below image: