Easy and Simple Edit/Update MySQL Table using PHP

Submitted by: 
Language: 
Visitors have accessed this post 1802 times.

This tutorial will show and give you knowledge on how to edit/update MySQL table using PHP. Is this tutorial, I'm gonna show you how to edit using 3 methods namely MySQLi Object-oriented, MySQLi Procedural and PDO.

Creating our Database

First, we're going to create a database that contains our data.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as "edit".
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

  1. CREATE TABLE `user` (
  2. `userid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `firstname` VARCHAR(30) NOT NULL,
  4. `lastname` VARCHAR(30) NOT NULL,
  5. `edit_via` VARCHAR(60) NOT NULL,
  6. PRIMARY KEY (`userid`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

edit

Inserting Data into our Table

Next step is to insert data into our table. This will serve as our reference in editing our rows.
1. Click our database "edit".
2. Click SQL and paste the below code.

  1. INSERT INTO `user` (`firstname`, `lastname`) VALUES
  2. ('neovic', 'devierte'),
  3. ('lee', 'ann');

Creating Our Table

Next is to create our table. This table will give us information about the data in our database. We name this table as "index.php". To create the table, open your HTML code editor and paste the code below after the tag.

  1. <!DOCTYPE html>
  2. <title>Editing MySQL Data</title>
  3. </head>
  4. <h2>User Table</h2>
  5. <div>
  6. <table border="1">
  7. <th>UserID</th>
  8. <th>Firstname</th>
  9. <th>Lastname</th>
  10. <th>Updated via</th>
  11. <th></th>
  12. </thead>
  13. <?php
  14. $con = mysqli_connect("localhost","root","","edit");
  15.  
  16. // Check connection
  17. if (mysqli_connect_errno())
  18. {
  19. echo "Failed to connect to MySQL: " . mysqli_connect_error();
  20. }
  21.  
  22. $query=mysqli_query($con,"select * from `user`");
  23. while($row=mysqli_fetch_array($query)){
  24. ?>
  25. <tr>
  26. <td><?php echo $row['userid']; ?></td>
  27. <td><?php echo $row['firstname']; ?></td>
  28. <td><?php echo $row['lastname']; ?></td>
  29. <td><?php echo $row['edit_via']; ?></td>
  30. <td><a href="edit.php?id=<?php echo $row['userid']; ?>">Edit</a></td>
  31. </tr>
  32. <?php
  33. }
  34.  
  35. ?>
  36. </tbody>
  37. </table>
  38. </div>
  39. </body>
  40. </html>

Creating our Edit Form and Script

Last step is to create our edit form and our edit script. This will update our selected row in our MySQL Table using the 3 methods. We name this page as "edit.php". To create the page, open your HTML code editor and paste the code below after the tag.

  1. <?php
  2. $id=$_GET['id'];
  3. ?>
  4. <!DOCTYPE html>
  5. <title>Editing MySQL Data</title>
  6. </head>
  7. <h2>Edit Form</h2>
  8. <?php
  9. $conn = mysqli_connect("localhost","root","","edit");
  10.  
  11. // Check connection
  12. if (mysqli_connect_errno())
  13. {
  14. echo "Failed to connect to MySQL: " . mysqli_connect_error();
  15. }
  16.  
  17. $query=mysqli_query($conn,"select * from `user` where userid='$id'");
  18. $row=mysqli_fetch_assoc($query);
  19. ?>
  20. <form method="POST">
  21. <label>Firstname: <input type="text" value="<?php echo $row['firstname']; ?>" name="firstname"></label>
  22. <label>Lastname: <input type="text" value="<?php echo $row['lastname']; ?>" name="lastname"></label>
  23. </br></br>
  24. <input type="submit" value="MySQLi Object-oriented" name="mysqli_oop">
  25. <input type="submit" value="MySQLi Procedural" name="mysqli_procedural">
  26. <input type="submit" value="PDO" name="pdo">
  27. </form>
  28. <br>
  29. <a href="index.php">Back</a>
  30.  
  31. <?php
  32.  
  33. if (isset($_POST['mysqli_oop'])){
  34.  
  35. $firstname=$_POST['firstname'];
  36. $lastname=$_POST['lastname'];
  37.  
  38. // Create connection
  39. $conn = new mysqli("localhost", "root", "", "edit");
  40. // Check connection
  41. if ($conn->connect_error) {
  42. die("Connection failed: " . $conn->connect_error);
  43. }
  44.  
  45. $sql = "UPDATE `user` SET firstname='$firstname', lastname='$lastname', edit_via='MySQLi Object-oriented' WHERE userid='$id'";
  46.  
  47. if ($conn->query($sql) === TRUE) {
  48. echo "Record updated successfully";
  49. } else {
  50. echo "Error updating record: " . $conn->error;
  51. }
  52.  
  53. $conn->close();
  54. }
  55. elseif (isset($_POST['mysqli_procedural'])){
  56.  
  57. $firstname=$_POST['firstname'];
  58. $lastname=$_POST['lastname'];
  59.  
  60. // Create connection
  61. $conn = mysqli_connect("localhost", "root", "", "edit");
  62. // Check connection
  63. if (!$conn) {
  64. die("Connection failed: " . mysqli_connect_error());
  65. }
  66.  
  67. $sql = "UPDATE `user` SET firstname='$firstname', lastname='$lastname', edit_via='MySQLi Procedural' WHERE userid='$id'";
  68.  
  69. if (mysqli_query($conn, $sql)) {
  70. echo "Record updated successfully";
  71. } else {
  72. echo "Error updating record: " . mysqli_error($conn);
  73. }
  74.  
  75. mysqli_close($conn);
  76. }
  77. elseif (isset($_POST['pdo'])){
  78. $servername = "localhost";
  79. $username = "root";
  80. $password = "";
  81. $dbname = "edit";
  82.  
  83. try {
  84. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  85. // set the PDO error mode to exception
  86. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  87.  
  88. $firstname=$_POST['firstname'];
  89. $lastname=$_POST['lastname'];
  90.  
  91. $sql = "UPDATE `user` SET firstname='$firstname', lastname='$lastname', edit_via='PDO' WHERE userid='$id'";
  92.  
  93. // Prepare statement
  94. $stmt = $conn->prepare($sql);
  95.  
  96. // execute the query
  97. $stmt->execute();
  98.  
  99. // echo a message to say the UPDATE succeeded
  100. echo $stmt->rowCount() . " records UPDATED successfully";
  101. }
  102. catch(PDOException $e)
  103. {
  104. echo $sql . "<br>" . $e->getMessage();
  105. }
  106.  
  107. $conn = null;
  108. }
  109.  
  110. ?>
  111. </body>
  112. </html>

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.