Using PHP Data objects (PDO) to insert into MySQL

PDO is a PHP construct which can be used with an integration with MySQL database. PDO is a sophisticated and secure practice to avoid malicious attacks such as SQL Injection. It's very beneficial tool for error reporting, named and unnamed placeholders and flexibility of usage.

to quote from official PDO documentation,

"PDO - PHP Data Objects - is a database access layer providing a uniform method of access to multiple databases."

In order to use PDO, you will have to create a database object to interact with underlying MySQL database as follows,


$dbh = new PDO('mysql:host=<Path_to_database or simply localhost>
dbname=<database_name> charset = <character_set_to_use or simply utf8>', '<MySQL database_username>', '<MySQL_database_password>',  array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

Once you setup the database object, its instance - Namely $dbh can be used for further database operations. such as insertion as follows,


$query = $dbh->prepare("INSERT INTO userDetails(user_identifier, user_email, user_password_hash, user_phone_number) 
 value (:user_id,:user_email,:user_password_hash,:user_phone_number)");  
$query->bindParam(':user_id', $user_id, PDO::PARAM_STR);
$query->bindParam(':user_email', $user_email,PDO::PARAM_STR);
$query->bindParam(':user_password_hash', $user_password_hash,PDO::PARAM_STR);
$query->bindParam(':user_phone_number', $user_phone_number,PDO::PARAM_STR);  
try {       
    $query->execute();
} catch( Exception $e ) {
    echo $e; 
    exit;
}

Beauty is, it also checks for the success. If query is unsuccessful, it logs the exception and exits. Which is really neat way rather than silently failing and then pulling hair why it didn't work.

In next post, I will show how to update the entries in database using PDO

Jayesh Kawli

I am a web and mobile developer working at Wayfair in Boston, MA. I come to learn so many things during course of life and I write about things which helped me and feel like they can help others too.

Subscribe to Fresh Beginning

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!