Use php’s PDO(PHP Database Object) for database access

PDO(PHP Database Object) in php 5.1 is a better way to collecting data from the database in the form of Objects. PDO provides you the freedom to switching any databse just by switching the connection.

Here are some Databases Supported by PDO:

  1. PDO_CUBRID : CUBRID
  2. PDO_DBLIB : Microsoft SQL Server and Sybase
  3. PDO_MYSQL : MySQL
  4. PDO_SQLITE : SQLite
  5. PDO_FIREBIRD : Firebird/Interbase
  6. PDO_INFORMIX : Informix
  7. PDO_IBM : IBM
  8. PDO_SQLSRV : Microsoft SQL Server
  9. PDO_OCI : Oracle
  10. PDO_ODBC : ODBC and DB2
  11. PDO_PGSQL : PostgreSQL
  12. PDO_4D : 4D

Or simple you can check in your system to use:

print_r(PDO::getAvailableDrivers());

 

Here are some examples of select, insert, update and delete query. But first of all we should that how to make a connection with PDO.

PDO Connection with MySQL:

// Database Handler
$db_handler = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

 

Here is the complete connection block. Make sure the Database values before apply this example.

// Change this section according to Database Settings
$hostname = "host_name";
$username = "user_name";
$password = "xxxxxx";
$database = "database_name";

try{
// Database Handler
$db_handler = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

$db_handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); # Throw Exception

}
catch( PDOException $e ){
echo "Error Occure!<br>";
echo $e->getMessage();
}

 

To know more about the PDO(PHP Database Object) follow the link PDO

Select Query:

// Try Select Statement
$sm_handler = $db_handler->prepare("select * from tbl_parent");
$sm_handler->execute();
$data = $sm_handler->fetchAll();

foreach( $data as $val ){
echo $val['p_name'] . "<br>";
}

 

Insert Query:

In this insert query i am using named placeholders instead of the direct query. Also remember that named placeholder always start with colon. In this example first prepare the query and give the named placeholder then bind it with the assigned variables then execute it.

// Try Insert Statement
// Using placeholder instead of direct values
$sm_handler = $db_handler->prepare("insert into tbl_parent (p_name, marks) values (:name, :marks)");

$name = "J Test2";
$marks = 20;

$sm_handler->bindParam(':name', $name, PDO::PARAM_STR);
$sm_handler->bindParam(':marks', $marks, PDO::PARAM_INT);

// Execute
$insert = $sm_handler->execute();
if( $insert ){
echo "Data Inserted Successfully!";
}

 

Update Query:

// Update Query
$sm_handler = $db_handler->prepare("update tbl_parent set marks=:marks where p_name=:name");

$name = "james";
$marks = 500;

$sm_handler->bindParam(":name", $name, PDO::PARAM_STR);
$sm_handler->bindParam(":marks", $marks, PDO::PARAM_INT);

$update = $sm_handler->execute();
if( $update ){
echo "Data Updated Successfully!";
}

 

Delete Query:

//Delete Query
$sm_handler = $db_handler->prepare("delete from tbl_parent where p_name=:name");

$name = "sam";
$sm_handler->bindParam(":name", $name, PDO::PARAM_STR);

$delete = $sm_handler->execute();
if( $delete ){
echo "Data Deleted Successfully!";
}

 

The above are the basic examples of PDO(PHP Database Object).