INSERT, UPDATE AND DELETE WITH PDO

SUBMITTED BY: Konstantinos

DATE: March 10, 2020, 8:34 p.m.

FORMAT: Text only

SIZE: 3.0 kB

Raw Download

HITS: 132302

Report
  1. INSERT
  2. Assuming a HTML form of method $_POST with the appropriate fields in it, the following would insert a new record in a table called movies. Note that in a real world example all the variables from $_POST would be validated before been sent to the query.
  4. $sql = "INSERT INTO movies(filmName,
  5. filmDescription,
  6. filmImage,
  7. filmPrice,
  8. filmReview) VALUES (
  9. :filmName,
  10. :filmDescription,
  11. :filmImage,
  12. :filmPrice,
  13. :filmReview)";
  15. $stmt = $pdo->prepare($sql);
  17. $stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);
  18. $stmt->bindParam(':filmDescription', $_POST['filmDescription'], PDO::PARAM_STR);
  19. $stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
  20. // use PARAM_STR although a number
  21. $stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
  22. $stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);
  24. $stmt->execute();
  25. Notice the use of colons as position placeholder for the bindParam() methods.
  27. GETTING AUTO INCREMENT KEY VALUES WITH LASTINSERTID()
  28. When using SQL INSERT you may have set up your database table with an auto_increment field to ensure your primary keys remain unique. You may need to know what this value is as soon as the database creates it. In this scenario the PDO method lastInsertId() can be used. Create a variable from this property after the execute() method as follows:
  30. $stmt->execute();
  31. $newId = $pdo->lastInsertId();
  32. UPDATE
  33. An UPDATE example would work in the same fashion this time the SQL has a WHERE clause to identify which record to update.
  35. $sql = "UPDATE movies SET filmName = :filmName,
  36. filmDescription = :filmDescription,
  37. filmImage = :filmImage,
  38. filmPrice = :filmPrice,
  39. filmReview = :filmReview
  40. WHERE filmID = :filmID";
  41. $stmt = $pdo->prepare($sql);
  42. $stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);
  43. $stmt->bindParam(':filmDescription', $_POST['$filmDescription'], PDO::PARAM_STR);
  44. $stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
  45. // use PARAM_STR although a number
  46. $stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
  47. $stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);
  48. $stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);
  49. $stmt->execute();
  50. DELETE
  51. Finally a DELETE statement. Like the UPDATE a WHERE clause ensures the correct record is removed.
  54. $sql = "DELETE FROM movies WHERE filmID = :filmID";
  55. $stmt = $pdo->prepare($sql);
  56. $stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);
  57. $stmt->execute();
  58. That covers the basics of getting started with PDO.
comments powered by Disqus