PHP MySQL PDO Database Connection and CRUD Operations

About Prepared Statement

In a prepared statement, we can use the same query multiple times. First, we need to analyze and optimize the query and later we can use it in many places. It reduces development time and executes faster. PDO provides various ways to work with objects and retrieves prepared statements that make work much easier.

PDO Drivers

To use PDO in PHP, php_pdo_mysql should be enabled in php.ini in PHP extension. This is by default enabled in PHP version 5.1.0 and later versions.

The PDO can work with 12 different database systems. If you have to switch your project database to another database, PDO makes the process easy. There are different PDO drivers available for different databases.

Databases	Drivers
MySQL 3.x & later	PDO_MYSQL
SQLite 3 and SQLite 2	PDO_SQLITE
PostgreSQL	PDO_PGSQL
ODBC v3	PDO_ODBC
IBM Informix	PDO_INFORMIX
Cubrid	PDO_CUBRID
Firebird	PDO_FIREBIRD
Microsoft SQL Server	PDO_SQLSRV

To check Available PDO Drivers in PHP

The following PHP command returns the available PDO Drivers in PHP. It returns all enabled PDO Drivers in Array.

<?php
 $database = new PDO("mysql:host=$hostname;dbname=$databasename", 
 $username, $password);
?>

PDO MySQL Database Connection

The PDO represents a connection between PHP and the database. Here is the syntax for PDO MySQL database connection. It creates a new PDO object with the data source name, user, and password. The PDO object is an instance of the PDO class.

<?php
$database = new PDO("mysql:host=$hostname;dbname=$databasename", 
$username, $password);
?>

• $hostname - Server Name
• $databasename - Database Name
• $username - Database User Name
• $password - Database Password

To handle PDO connection error

PDO has an exception class to handle any problems that may occur in our database queries. To handle the exception, it would be better to write the connection code in a try catch block. If an exception is thrown in the try block, the script stops execution and control goes directly to the catch block.

<?php
try{ 
$database = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
}
catch(PDOExtension $e){
echo $e->getMessage();
}
?>

Close the PDO MySQL database connection.

To close the MySQL PDO connection, just set null to the database connection variable.

$database = null;

MySQL CRUD operations using PDO

PHP MySQL Create Table

In this given code, we have created a table name 'employees'. It contains four fields(emp_id, first_name, last_name emp_code). The '$database' is the database connection variable that we have created above.

Here exec() is used to execute the PDO and return the number of affected rows.

<?php
$create_tbl = "CREATE TABLE employees emp_id int(11) NOT   NULL AUTO_INCREMENT, first_name varchar(25) NOT NULL,
 last_name varchar(25) NOT NULL, emp_code varchar(25) NOT NULL"; 
$create_emp = $database->exec($create_tbl);
if($create_emp != 'false'){
 echo 'Employee table created successfully.'; 
}
else{ 
 echo 'Error in Create Statement.';
} 
?>

PHP MySQL INSERT Table

Here, prepare() is used to prepare the insert statement for execution by using PDO execute() method.

<?php
$emp_insert = $database->prepare("INSERT into employees   ('emp_id','first_name','last_name','emp_code') VALUES ('', 'John', 'Bown', '121')");
$emp_insert->execute();
?>

To protect from SQL Injection, we should use placeholder instead of values. Like, we can write the above code as -

<?php
$emp_data = array('id'=>'', 'fname'=>'John', 'lname'=>'Bown', 'empcode'=>'121');
$emp_insert = $database->prepare("INSERT into employees('emp_id','first_name','last_name','emp_code') 
VALUES ('', ':fname', ':lname', ':empcode')");
$emp_insert->execute($emp_data);
?>

PHP MySQL UPDATE Table

Here, the prepare() is used to prepare the update statement for execution by using PDO execute() method. The bindparam() binds a parameter to the specified variable name.

<?php
$emp_update = $database->prepare("UPDATE employees SET first_name= :fname, last_name = :lname WHERE id = :id ");
$emp_update->bindparam(':fname', 'Doran');
$emp_update->bindparam(':lname', 'Devis');
$emp_update->execute();
?>

PHP MySQL SELECT from Table

Here, the prepare() is used to prepare the select statement for execution by using PDO execute() method. The bindparam() binds a parameter to the specified variable name to fetch the desired value, i.e., it sets the where clause condition and the fetch() method fetches a row from the result set.

Here, PDO::FETCH_ASSOC returns an array indexed by column name.

<?php
$emp_select = $database->prepare("SELECT FROM employees WHERE id = :id ");
$emp_select->bindparam(':id', '1');
$emp_select->execute();
$emprows = $emp_select->fetch(PDO::FETCH_ASSOC);
return $emprows;
?>

Related Articles

How to create search filter in PHP
PHP array length
Import Excel File into MySQL Database using PHP
PHP String Contains
PHP remove last character from string
PHP random quote generator
PHP calculate percentage of total
PHP sanitize input
Upload multiple files and store in MySQL database using PHP
multiple choice quiz in PHP and MySQL
Import Data Into MySQL From Excel File
Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
Simple pagination in PHP
Simple PHP File Cache
PHP upload multiple files
PHP Connection and File Handling on FTP Server
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
How to display PDF file in PHP from database