no
2018-06-05 03:06:00130

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.

comment