14- MySQL Update Data

0
39

MySQL Update Data

To Update the existing data in MySQL database you need to used UPDATE statement.

Syntax:

UPDATE tablename
SET column1=val1, column2=val2, column3=val3.
WHERE column=value;
Take Note! Using the UPDATE command you must not forget to include the WHERE clause, because the Where clause is used to specify record you want to modify. Without having the WHERE clause, all records in the table will be modified.
Example in Updating Data using MySQLi(Object-Oriented), MySQLi(Procedural) and PDO.

Example using MySQLi(Object-Oriented):

  1. <!--?php $server = "localhost"; $username = "root"; $password = ""; $database = "mysqltutorial"; // Create connection $conn = new mysqli($server, $username, $password, $database); // Check connection if ($conn-&gt;connect_error) {&lt;br ?--> die("Connection failed: " . $conn-&gt;connect_error);
  2. }
  3.  
  4. // sql to UPDATE record from table
  5. $sql = "UPDATE `tblpeople` SET `LNAME` = 'Villan',`FNAME` = 'Joven',`ADDRESS` = 'Bacolod' WHERE `tblpeople`.`ID` =5;";
  6.  
  7. if ($conn-&gt;query($sql) === TRUE) {
  8. echo "Record UPDATED successfully";
  9. } else {
  10. echo "Error: " . $sql . "
  11. " . $conn-&gt;error;
  12. }
  13.  
  14. $conn-&gt;close();
  15. ?&gt;

Example using MySQLi(Procedural):

  1. <!--?php &lt;/p&gt;
  2. &lt;p&gt;$server = "localhost";&lt;br ?--> $username = "root";
  3. $password = "";
  4. $database = "mysqltutorial";
  5.  
  6. // Create connection
  7. $conn = mysqli_connect($server, $username, $password, $database);
  8. // Check connection
  9. if (!$conn) {
  10. die("Connection failed: " . mysqli_connect_error());
  11. }
  12.  
  13. // sql to UPDATE record from table
  14. $sql = "UPDATE `tblpeople` SET `LNAME` = 'Villan',`FNAME` = 'Joven',`ADDRESS` = 'Bacolod' WHERE `tblpeople`.`ID` =5;";
  15.  
  16. if (mysqli_query($conn, $sql)) {
  17.  
  18. echo "Record UPDATED successfully";
  19. } else {
  20. echo "Error: " . $sql . "
  21. " . mysqli_error($conn);
  22. }
  23.  
  24. mysqli_close($conn);
  25. ?&gt;

Example using PDO:

  1. <!--?php $server = "localhost"; $username = "root"; $password = ""; $database = "mysqltutorial"; try { $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password); // set the PDO error mode to exception $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);&lt;/p&gt;
  2. &lt;p&gt;// sql to UPDATE record from table&lt;br ?--> $sql = "UPDATE `tblpeople` SET `LNAME` = 'Villan',`FNAME` = 'Joven',`ADDRESS` = 'Bacolod' WHERE `tblpeople`.`ID` =5;";
  3.  
  4. $conn-&gt;exec($sql);
  5. echo "Record UPDATED successfully";
  6. }
  7. catch(PDOException $e)
  8. {
  9.  
  10. echo $sql . "
  11. " . $e-&gt;getMessage();
  12. }
  13.  
  14. $conn = null;
  15. ?&gt;
 Our Previous Topic Called: 13- MySQL Delete Data

Facebook Comments
(Visited 58 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here