Skip to main content

PHP Update Data In MySQL

Screenshot
Body

Good Day!!!

Yesterday, we create a tutorials for Inserting Data, Select Data, and Deleting Data from MySQL. For this follow-up tutorial, we are going to create another that related to them.

We are going to create PHP Update Data In MySQL. For this tutorial, we are going to use the UPDATE Statement to update the current data in our table.

This is the data in our table.

In the underline, that we are going to update the first name.

Data

Updating Data Using MySQLi and PDO

Using MySQLi (Object-Oriented)

  1. <?php<br />
  2. $servername = "localhost";<br />
  3. $username = "root";<br />
  4. $password = "";<br />
  5. $dbname = "edit_query_pdo";</p>
  6. <p>// Create connection<br />
  7. $conn = new mysqli($servername, $username, $password, $dbname);<br />
  8. // Check connection<br />
  9. if ($conn->connect_error) {<br />
  10. die("Connection failed: " . $conn->connect_error);<br />
  11. } </p>
  12. <p>$sql = "UPDATE tbl_registration SET first_name='Sarah' WHERE tbl_registration_id=2";</p>
  13. <p>if ($conn->query($sql) === TRUE) {<br />
  14. echo "Record updated successfully";<br />
  15. } else {<br />
  16. echo "Error updating record: " . $conn->error;<br />
  17. }</p>
  18. <p>$conn->close();<br />
  19. ?>

Using MySQLi (Procedural)

  1. <?php<br />
  2. $servername = "localhost";<br />
  3. $username = "root";<br />
  4. $password = "";<br />
  5. $dbname = "edit_query_pdo";</p>
  6. <p>// Create connection<br />
  7. $conn = mysqli_connect($servername, $username, $password, $dbname);<br />
  8. // Check connection<br />
  9. if (!$conn) {<br />
  10. die("Connection failed: " . mysqli_connect_error());<br />
  11. }</p>
  12. <p>$sql = "UPDATE tbl_registration SET first_name='Sarah' WHERE tbl_registration_id=2";</p>
  13. <p>if (mysqli_query($conn, $sql)) {<br />
  14. echo "Record updated successfully";<br />
  15. } else {<br />
  16. echo "Error updating record: " . mysqli_error($conn);<br />
  17. }</p>
  18. <p>mysqli_close($conn);<br />
  19. ?>

Using PDO (PHP Data Objects)

  1. <?php<br />
  2. $servername = "localhost";<br />
  3. $username = "root";<br />
  4. $password = "";<br />
  5. $dbname = "edit_query_pdo";</p>
  6. <p>try {<br />
  7. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);<br />
  8. // set the PDO error mode to exception<br />
  9. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);</p>
  10. <p> $sql = "UPDATE tbl_registration SET first_name='Sarah' WHERE tbl_registration_id=2";</p>
  11. <p> // Prepare statement<br />
  12. $stmt = $conn->prepare($sql);</p>
  13. <p> // execute the query<br />
  14. $stmt->execute();</p>
  15. <p> // echo a message to say the UPDATE succeeded<br />
  16. echo $stmt->rowCount() . " records UPDATED successfully";<br />
  17. }<br />
  18. catch(PDOException $e)<br />
  19. {<br />
  20. echo $sql . "<br>" . $e->getMessage();<br />
  21. }</p>
  22. <p>$conn = null;<br />
  23. ?>

And, this is another source code to update the record in the database table.

In this source code, you can edit one or more column in our table.

  1. <?php<br />
  2. include 'connection.php';</p>
  3. <p>$get_id=$_REQUEST['tbl_registration_id'];</p>
  4. <p>$first_name= $_POST['first_name'];<br />
  5. $middle_name= $_POST['middle_name'];<br />
  6. $last_name= $_POST['last_name'];<br />
  7. $email= $_POST['email'];<br />
  8. $contact_number= $_POST['contact_number'];</p>
  9. <p>$sql = "UPDATE tbl_registration SET first_name ='$first_name', middle_name ='$middle_name',<br />
  10. last_name ='$last_name', email ='$email', contact_number ='$contact_number' WHERE tbl_registration_id = '$get_id' ";</p>
  11. <p>$conn->exec($sql);<br />
  12. echo "<script>alert('Successfully Edit The Account!'); window.location='index.php'</script>";<br />
  13. ?>

Output:

This is the Edit Form.

Edit FormAfter the data updated, this is our new table.

Result1Result

Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.

Comments

Submitted bymartin kyabaggu (not verified)on Tue, 01/03/2017 - 18:06

The web based car hire sonko car company when i insert data, message error comes saying registration failed try again. any one understand the source of that error? please help me or someone who developed the system.

Add new comment