PHP PDO Update Query

Update Data from Database using PHP Data Objects(PDO)

PHP PDO Update Query

HI Guys In This Lesson we Will Learn How To Update Tables row /Data using PHP Data Objects(PDO).
here i Provide very Simple pdo codes for Beginners.

Overview and Setup:

Database Overview:
Database Name: tutorial
Table Name : test
Create a Table using Following SQL Commands

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Insert some rows to Display and Update using Following SQL Commands.

INSERT INTO `test` (`id`, `name`, `email`) VALUES
(12, 'ANJANBD', 'Anjankumardhali6@gmail.com'),
(11, 'Krishna', 'krishna@mail.com'),
(3, 'Rupam Mondal', 'RupomMondal@gmail.com'),
(4, 'ANJAN', 'KumarANjan@gmail.com'),
(5, 'Biswas Shiuli', 'ShiuliBiswas@gmail.com'),
(7, 'Hrithik', 'Hrithik@gmail.com'),
(8, 'Modhu Bonik', 'Modhu360@gmail.com'),
(9, 'Rupam Mondal Joy', 'RupomMondal@gmail.com'),
(13, 'Rupa', 'Rupa@hotmail.com'),
(14, 'ANJAN', 'Kumar@gmail.com'),
(15, 'ANJANBD', 'Anjankumardhali6@gmail.com'),
(16, 'Krishna', 'krishna@mail.com'),
(17, 'Rupam Mondal', 'RupomMondal@gmail.com'),
(18, 'ANJAN', 'KumarANjan@gmail.com'),
(19, 'Biswas Shiuli', 'ShiuliBiswas@gmail.com'),
(20, 'Hrithik', 'Hrithik@gmail.com'),
(21, 'Modhu Bonik', 'Modhu360@gmail.com'),
(22, 'Rupam Mondal Joy', 'RupomMondal@gmail.com'),
(23, 'Rupa', 'Rupa@hotmail.com'),
(24, 'ANJAN', 'Kumar@gmail.com'),
(25, 'ANJANBD', 'Anjankumardhali6@gmail.com'),
(26, 'ANJANBD60', 'Anjankumardhali6@gmail.comss'),
(27, 'Priyoshi', 'Priyoshi@gmail.com'),
(28, 'Rupom Chakma', 'Rupam@gmail.com'),
(29, 'Monisha Roy', 'Monisharoy@gmail.com');

Files Structure:
Create 5 files
1.connect.php [connecting server and database]
2.index.php [display data with update button]
3.updateform.php [display update form]
4.update.php [update data and redirect to index page]
5.style.css [styling our index and form page]
put all files in same folder.copy the below codes for your differents files.

codes for connect.php

<?php
$host = "localhost"; //Host Name
$username = "root"; //MySQL Username.if you don't know leave 'root' by Defaults
$password = ""; //MySQL Password if Password Protected .else Leave it Blank
$db_name = "tutorial"; //Database Name

try {
 //Creating Connection between Server and Database
    $conn = new PDO("mysql:host=$host;dbname=$db_name", $username, $password);
    // SET PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
    {
  //Displaying Error Message with Detail.
    echo "Sorry failed to Connect: " . $e->getMessage();
    }
?>

Codes for index.php

in this page we will display our test table data with Update Now Button.when You Click on "Update Now" it Will Send you to updateform.php page.

<?php
include('connect.php');
   ?>   
   <link rel="stylesheet" href="style.css" type="text/css"/>     
  <table>
     <tr>
         <th>ID</th>
      <th>Name</th>
      <th>Email</th>
      <th>Action</th>
   </tr>
   <?php
   $query = "SELECT * FROM test ORDER by id ASC";
        $stmt = $conn->prepare( $query );
        $stmt->execute();
        while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
        ?>
   
   <tr>
       <td><?php echo $row['id'];?></td>
    <td><?php echo $row['name'];?></td>
    <td><?php echo $row['email'];?></td>
    <td><a href="updateform.php?id=<?php echo $row['id'];?>">Update Now</a></td>
             </tr>
   <?php
      }
   ?> 
  </table>

Codes for updateform.php

in this you will see update form with data defined by ID.
When you will Click on Update Now it will send you to Update.php page.

<?php
include('connect.php');
   ?>   
   <link rel="stylesheet" href="style.css" type="text/css"/>
   <?php
   $id=$_GET['id'];
   $query = "SELECT * FROM test WHERE id='$id'";
        $stmt = $conn->prepare( $query );
        $stmt->execute();
        while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
        ?>
  <form method="POST" action="update.php"/>
  <input type="hidden" name="id" value="<?php echo $row['id'];?>"/>
  <label>Name:</label><br/>
  <input type="text" name="NewName" value="<?php echo $row['name'];?>"/><br/>
  <label>Email:</label><br/>
  <input type="text" name="NewEmail" value="<?php echo $row['email'];?>"/><br/>
  <input type="submit" name="submit" value="Update Now"/>
  </form>
<?php }?>

Codes update.php

Finally Update page will run Query. if query matched it will Updated Data and Redirect you to index.php page

<?php
include('connect.php');

 $id=$_POST['id'];
 $name=$_POST['NewName'];
 $email=$_POST['NewEmail'];
    $sql = "UPDATE test SET name='$name',email='$email' WHERE id=$id";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

    // echo a message to say the UPDATE succeeded
    echo $stmt->rowCount() .header('location:index.php');

?>

If you Like This Article Please Share With Your Friends. Keep Visting For More articles.Thank you.