Edit/Update Data Using PHP/MySQL with PDO Query

This tutorial will teach you on how to edit data from database table. This edit system is in PHP/MySQL withy PDO query. to start this tutorial fallow the steps bellow:

Creating Our Database

First we are going to create our database which stores our data. To create a database: 1. Open phpmyadmin 2. Then create database and name it as "pdo_ret". 3. After creating a database name, click the SQL and paste the following code.
  1. CREATE TABLE IF NOT EXISTS `members` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `fname` varchar(100) NOT NULL,
  4. `lname` varchar(100) NOT NULL,
  5. `age` int(5) NOT NULL,
  6. PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Creating our Database Connection

Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format.
  1. <?php
  2. /* Database config */
  3. $db_host = 'localhost';
  4. $db_user = 'root';
  5. $db_pass = '';
  6. $db_database = 'pdo_ret';
  7.  
  8. /* End config */
  9.  
  10. $db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
  11. $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12. ?>

Creating Our Display With Edit Action

The code bellow will retrieve data from our database table and provide an edit action. Copy the code bellow and save it as "index.php".
  1. <table border="1" cellspacing="0" cellpadding="2" >
  2. <thead>
  3. <tr>
  4. <th> First Name </th>
  5. <th> Last Name </th>
  6. <th> Age </th>
  7. <th> Action </th>
  8. </tr>
  9. </thead>
  10. <tbody>
  11. <?php
  12. include('connect.php');
  13. $result = $db->prepare("SELECT * FROM members ORDER BY id DESC");
  14. $result->execute();
  15. for($i=0; $row = $result->fetch(); $i++){
  16. ?>
  17. <tr class="record">
  18. <td><?php echo $row['fname']; ?></td>
  19. <td><?php echo $row['lname']; ?></td>
  20. <td><?php echo $row['age']; ?></td>
  21. <td><a href="editform.php?id=<?php echo $row['id']; ?>"> edit </a></td>
  22. </tr>
  23. <?php
  24. }
  25. ?>
  26. </tbody>
  27. </table>

Creating Our Edit Form

The code bellow will be the landing page after we click the edit link in our display page. Copy the code bellow and save it as "editform.php".
  1. <?php
  2. include('connect.php');
  3. $id=$_GET['id'];
  4. $result = $db->prepare("SELECT * FROM members WHERE id= :userid");
  5. $result->bindParam(':userid', $id);
  6. $result->execute();
  7. for($i=0; $row = $result->fetch(); $i++){
  8. ?>
  9. <form action="edit.php" method="POST">
  10. <input type="hidden" name="memids" value="<?php echo $id; ?>" />
  11. First Name<br>
  12. <input type="text" name="fname" value="<?php echo $row['fname']; ?>" /><br>
  13. Last Name<br>
  14. <input type="text" name="lname" value="<?php echo $row['lname']; ?>" /><br>
  15. Age<br>
  16. <input type="text" name="age" value="<?php echo $row['age']; ?>" /><br>
  17. <input type="submit" value="Save" />
  18. </form>
  19. <?php
  20. }
  21. ?>

Edit Script

This script will edit the data in our database table. Copy the code bellow and save it as "edit.php".
  1. <?php
  2. // configuration
  3. include('connect.php');
  4.  
  5. // new data
  6. $lname = $_POST['lname'];
  7. $fname = $_POST['fname'];
  8. $age = $_POST['age'];
  9. $id = $_POST['memids'];
  10. // query
  11. $sql = "UPDATE members
  12. SET fname=?, lname=?, age=?
  13. WHERE id=?";
  14. $q = $db->prepare($sql);
  15. $q->execute(array($fname,$lname,$age,$id));
  16. header("location: index.php");
  17.  
  18. ?>
That's it you've been successfully created your edit script using PHP/MySQL with PDO query.

Comments

Submitted byLee Davis (not verified)on Thu, 10/09/2014 - 10:31

Argie, you are one of the ONLY sources of joy for myself in the last three weeks as I spent the time screaming and tearing my hair out trying to wade through an undercurrent of pedantic pestilence for an oh-so-simple way in which to perform a simple update using the PDO methodology. I did the HashPHP tutorial, investigated some Kevin Yank literature, and read almost 600-plus questions in forums like Stack Overflow, Sitepoint, Digitalpoint, et al. I was starting to think that I was going slightly insane, like, somehow a fundamental grasp on reality was about to be severed. How many tutorials and forums did I have to go through before I reached you, Argie?!? I've read AT LEAST 2 to 3,000 pages on the subject, and I'm still as confuzzled as a newborn in the wild. This is just a note of thanks from someone who's been returned their sanity by your helpful, graceful hand. Madame, I salute you ;)
Submitted byPer (not verified)on Mon, 09/11/2017 - 14:37

Thank you for this great and easy tutorial!

Add new comment