Introduction to PHP Data Object PDO in PHP

There are three main API options when considering connecting to a MySQL database server.

PHP’s MySQL Extension.
PHP’s mysqli Extension.
PHP Data Object (PDO).

PHP Data Object (PDO)

PDO is a database access layer that provides a fast and consistent interface for accessing and managing databases in PHP applications. Every DBMS has specific PDO drivers(s) that must be installed when you are using PDO in PHP applications. It supports only the Object-Oriented interface. PDO will work on 12 different database systems.

Benefits of PDO

Usability
Error Handling
Multiple Databases
Secure
Reusability
Exception Class

Supported Databases

CUBRID
MS SQL Server Sybase
Firebird
IBM
Informix
MySQL
MS SQL Server
Oracle
ODBC and DB2
PostgreSQL
SQLite
4D

Difference and Similarities between MySQLi and PDO

Difference

MySQLi supports dual interface Procedural and object-oriented while PDO supports only Object-oriented.
MySQLi will work only with MySQL Database while PDO will work on 12 different database systems.

Similarities

Both support Prepared Statements.
Both provide the same level of security.
Both supports Object-Oriented.

PDO – Fetch DB Data/SELECT

query($sql_statement) – It executes an SQL statement in a single function call, returning the result set(if any) returned by the statement as a PDO Statement object or FALSE on failure.
fetch($fetch_Style) – It fetches a row from a result set associated with a PDO Statement object. The fetch_style parameters determine how PDO returns the row.
fetchAll($fetch_Style) – It returns an array containing all of the remaining rows in the result set, The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch, or FALSE on failure. The fetch_style parameter determines how PDO returns the row.

$fetch Style

It controls how the next row will be returned to the caller.

PDO:: FETCH_BOTH(default): It returns an array indexed by both column name and 0-indexed column number as returned in your result set.

PDO::FETCH_ASSOC: It returns an array indexed by column name(associative array) as returned in your result set.

PDO::FETCH_NUM: It returns an array indexed by column number(index array) as returned in your result set, starting at column 0.

PDO::FETCH_OBJ: It returns an anonymous object with property names that correspond to the column names returned in your result set.

PDO::FETCH_NAMED: It returns an array with the same form as PDO:: FETCH_ASSOC, except that if there are multiple columns with the same name, the value referred to by that key will be an array of all the values in the row that had that column name.

rowCount()

It returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatment object. If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some database may return the number of rows returned by the statement.

Insert Data

exec($sql_statement) – It executes an SQL statement in a single function call, returning the number of rows affected by the statement. It does not return results from a SELECT statement.

Table structure for table student

CREATE TABLE student (
id int(255) NOT NULL,
name varchar(100) NOT NULL,
roll varchar(100) NOT NULL,
address varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

//Create Connection

$dsn = "mysql:host=localhost;dbname=test;";
$db_user = "root";
$db_password = "";

try{

 //Create Connection
 $conn = new PDO($dsn, $db_user, $db_password);
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 echo "Connected  
";

 $name = 'JavaScript';
 $roll = '109';
 $address = 'Kolkata';

 // Insert Data
 $sql = "INSERT INTO student (name, roll, address) VALUES ('$name', '$roll', '$address')";
 $affected_row = $conn->exec($sql);
 echo $affected_row . "Row Inserted 
";  
}

catch(PDOException $e){
 echo "Connection Failed" .$e->getMessage();
}

?>

Insert Data into Form.

Retrieve and Insert Data using PDO in PHP.

Delete Data from Database using PDO in PHP.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Connected 
 
";

  //Delete Data
  $sql = "DELETE FROM student WHERE id = 19";
  $affected_row = $conn->exec($sql);
  echo $affected_row . "Row Deleted Successfuly 
";
}

catch(PDOException $e){
	echo "Connection Failed" .$e->getMessage();
}

$conn = null;

?>

Delete Data from Database using Button PDO in PHP.

Update Data into Database using PDO in PHP.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Connected 
 
";

  //Update Data
  
    $sql = "UPDATE student SET name = 'Sameer', roll = '110', address = 'Chennai' WHERE id = 15";
  	$affected_row = $conn->exec($sql);
  	echo $affected_row . "Row Updated Successfuly 
";
  
}

catch(PDOException $e){
	echo "Connection Failed" .$e->getMessage();
}
$conn = null;

?>

Update Data into Database using Form PDO in PHP.