INSERT, UPDATE and DELETE with 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.





Related Post


Latest Post


Recent Posts Widget

Make sure to never miss a thing...

Get the latest news from the creative industry along with other creative goodies, conveniently delivered to social media.