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.

18 Thoughts to “INSERT, UPDATE and DELETE with PDO”

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

  2. 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

    1. admin

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

  3. 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

  4. Patrick Weber

    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…..

  5. Dan Cacovean

    great tutorial. tks :))

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

  7. 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

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

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

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

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

    1. admin

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

  12. 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.

  13. Thank a lot, you dont imagine how you save my day.

  14. Tiago Oliveira

    Very good! Thks helped my alote!!

Leave a Comment