INSERT, UPDATE and DELETE with PDO

We have seen how to use mysqli to Insert, Update and Delete - now lets do the same for PDO.

INSERT

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.


$sql = "INSERT INTO movies(filmName,
			filmDescription,
			filmImage,
			filmPrice,
			filmReview) VALUES (
			:filmName, 
			:filmDescription, 
			:filmImage, 
			:filmPrice, 
			:filmReview)";
										 
$stmt = $pdo->prepare($sql);
											 
$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);		
$stmt->bindParam(':filmDescription', $_POST['filmDescription'], PDO::PARAM_STR);	
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number	
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);	
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);	
									 
$stmt->execute(); 

Notice the use of colons as position placeholder for the bindParam() methods.

Getting Auto Increment Key Values with lastInsertId()

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:

$stmt->execute(); 
$newId = $pdo->lastInsertId();

UPDATE

An UPDATE example would work in the same fashion this time the SQL has a WHERE clause to identify which record to update.

$sql = "UPDATE movies SET filmName = :filmName, 
			filmDescription = :filmDescription, 
			filmImage = :filmImage,  
			filmPrice = :filmPrice,  
			filmReview = :filmReview  
			WHERE filmID = :filmID";
$stmt = $pdo->prepare($sql);									 
$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);		
$stmt->bindParam(':filmDescription', $_POST['$filmDescription'], PDO::PARAM_STR);	
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number	
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);	
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);	
$stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);	
$stmt->execute(); 

DELETE

Finally a DELETE statement. Like the UPDATE a WHERE clause ensures the correct record is removed.

$sql = "DELETE FROM movies WHERE filmID =  :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);	
$stmt->execute();

That covers the basics of getting started with PDO.

16 Comments

  1. Sigmato
    Aug 03, 2013 @ 04:50:06

    Great tuts, can we use the same in production environment? I am thinking of developing a app with php using pdo.

    Reply

  2. Patrick Weber
    Jul 15, 2014 @ 18:29:34

    Hi everybody !
    I have a simple question; how to insert datas in two differents table with either (preferably) MySQLI or PDO. I have a little project with a table containing users info, like first name & address etc… and a table containing the project linked to them. For example, Peter is member of a named project, but he's not yet confirmed etc.. Each table have an id (userID & projectID) in order to trace who's belongs to project name.

    Anyone could help me with that idea? I tried many methods without success, so your experience would be appreciated, indeed.

    I beg your pardon… I'm not fluent in English. I hope the explanation is enough clear for you 🙂
    Thank you very for your help.
    Have a good day
    Patrick

    Reply

    • admin
      Jul 16, 2014 @ 11:25:53

      Hi Patrick - there is a good solution to this on Stack overflow you might want to try.

      Reply

  3. Patrick Weber
    Jul 16, 2014 @ 13:28:05

    Thanks for your reply, but unfortunately codes do not work. I'm looking for a mysqli or pro script. I continue my quest 🙂 !
    Thanks again
    Patrick

    Reply

  4. Patrick Weber
    Jul 16, 2014 @ 13:59:59

    I don't know if this could help.
    I'd like to use :
    mysqli_autocommit($mysqli, FALSE);
    $sql = "INSERT INTO adherents (nom,prenom,fixe,portable,courriel,adresse,ville,codepostal)
    VALUES ('".$nom."', '".$prenom."''".$fixe."', '".$portable."''".$courriel."', '".$adresse."''".$ville."', '".$codepostal."'')";

    if (mysqli_query($mysqli, $sql ) === TRUE) {
    $membre_id = mysqli_insert_id($mysqli);
    $sql1 = "INSERT INTO projet (id,membre_id,fonction,nomProjet,coffrets,delai,rabais)
    VALUES ('".$membre_id."','".$fonction."','".$nomProjet."','".$coffrets."','".$delai."','".$rabais."')";
    mysqli_query($mysqli, $sql1 );
    }
    if (!mysqli_commit($mysqli)) { //commit transaction
    print("Opération annulée");
    exit();
    }
    but, unfortunately with no success.
    So, now I'm stuck !
    to be continued…..

    Reply

  5. Dan Cacovean
    Aug 06, 2014 @ 09:47:55

    great tutorial. tks :))

    Reply

  6. CMT3313 – Conclusion. | brgdane
    Jan 16, 2015 @ 21:05:16

    Reply

  7. Randall
    Mar 08, 2015 @ 20:49:00

    Thank you, thank you, for the clear, straightforward writing, and ...examples that actually work!
    (pdo prepared update/insert)

    Reply

  8. Peter
    Aug 12, 2015 @ 11:51:43

    As stated earlier by Randall, Thank you for an excellent tutorial, you have clarified a lot, and allowed me to complete some work which has been frustrating me for hours / a day...
    Thanks

    Reply

  9. Gbenu
    Sep 12, 2015 @ 08:03:25

    Your execute statement should contain binding value(s) as follow:
    $stmt->execute(array(:name=>$variable) )

    Reply

  10. vikram
    Jan 21, 2016 @ 10:00:24

    Really nice article. I understood this is a valuable blog for PHP.

    Reply

  11. Anto
    Jan 23, 2016 @ 00:57:17

    Thanks this tutorial. I think it's time this time I switched to using PDO.

    Reply

  12. php – update with PDO not working, serveral options tried out – shareitHQ
    Apr 27, 2016 @ 16:29:00

    […] also tried this http://www.mustbebuilt.co.uk/php/insert-update-and-delete-with-pdo/ and it also didn’t […]

    Reply

  13. Aldi Unanto
    May 11, 2016 @ 05:29:12

    What's the function of PDO::PARAM_STR ?
    i tried to remove and worked fine.

    Reply

    • admin
      Jun 14, 2016 @ 14:20:25

      It is used to indicate the data type that you are binding.

      Reply

  14. Andy Mechcanical
    Dec 09, 2016 @ 19:40:01

    Thanks so much for the UPDATE section here, ive been struggling for upwards of 6 months attempting to use PDO with an update statement. This is THE ONLY EXAMPLE ON THE WEB that worked. Ive attempted many different syntax styles but this one by far was the easiest to read, easiest to understand what is happening and did the job perfectly. ARE YOU AWARE THAT YOUR LIKE THE ONLY PERSON ON THE WEB THAT OPENLY OUTPUTS THAT LAYOUT! sorry but im genuinely ecstatic over the outcome. I wasted a lot of time on StackOverflow (learned to despise that site) looking for the answer and turns out that site is somewhat of a waste of time wish this example was there bc its the first search to come up, 5min here and i got exactly what i was looking for.

    Reply

Leave a Reply

*