Learning PDO in PHP from Scratch for a Growing Career

What Is PDO?

PDO in PHP offers a data-access abstraction layer, which means you can issue queries and fetch data using the same functions regardless of which database you're using. PDO isn't a database abstraction; it doesn't rewrite SQL or imitates features that aren't accessible.

extension=pdo.so

Installing PDO

• By default, PDO and PDO SQLite driver are available. You will need to allow the PDO driver for your database of choice. (For more information, see the documentation for database-specific PDO drivers.)
• The php.ini file must be modified when installing PDO as a shared module so that the PDO extension is loaded automatically when PHP runs. You'll also need to add database-specific drivers there; make sure they're listed after the PDO. So the section since PDO must be loaded before it can load database-specific extensions. You couldbaseNamePostgresPostgres skip this step if you developed PDO and the database-specific extensions statically.

extension=pdo.so

Choose the other database-specific DLL files, load them at runtime with dl(), or enable them in php.ini below PHP pdo.dll. Consider the following scenario:

• extension=php_pdo.dll
• extension=php_pdo_firebird.dll
• extension=php_pdo_informix.dll
• extension=php_pdo_mssql.dll
• extension=php_pdo_mysql.dll
• extension=php_pdo_oci.dll
• extension=php_pdo_oci8.dll
• extension=php_pdo_odbc.dll
• extension=php_pdo_pgsql.dll
• extension=php_pdo_sqlite.dll 

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Predefined Constants

PDO::PARAM_BOOL (int)

This represents a boolean data type.

PDO::PARAM_NULL (int)

It represents the SQL NULL data type.

PDO::PARAM_INT (int)

The command represents the SQL INTEGER data type.

PDO::PARAM_STR (int)

This offers the SQL CHAR, VARCHAR, or other string data type.

PDO::PARAM_STR_NATL (int)

This is used to denote a string that uses the national character set. Available since PHP 7.2.0

Supported Databases

Sample 

Database Name: postgres

Host Name: localhost

Database user: postgres

Password: abc123

Structure of the table: user_details

Records of the table: user_details

Input

PDO {

      __construct ( string $dsn [, string $username [, string $password [, array $driver_options ]]] ) 

bool beginTransaction ( void ) 

bool commit ( void ) 

mixed errorCode ( void ) 

array errorInfo ( void ) 

int exec ( string $statement ) 

mixed getAttribute ( int $attribute ) 

static array getAvailableDrivers ( void ) 

bool inTransaction ( void ) 

string lastInsertId ([ string $name = NULL ] ) 

PDOStatement prepare ( string $statement [, array $driver_options = array() ] ) 

PDOStatement query ( string $statement ) 

string quote ( string $string [, int $parameter_type = PDO::PARAM_STR ] ) 

bool rollBack ( void ) 

bool setAttribute ( int $attribute , mixed $value ) }mp($var_name);

}

The PDO Class

There are three PDO in PHP groups to choose from as mentioned below:

• PDO - A relation between PHP and the database is represented by PDO.
• PDOStatement - It represents a prepared statement and sets an associated outcome after the statement is executed.
• PDOException - This class represents PDO errors.

Details of the PDO Class Methods

PDO:: Construct

Creates a PDO instance that represents a database relation.

Syntax:

PDO::__construct() ( string $dsn [, string $username [, string $password [, array $driver_options ]]] )

DSN - The Data Source Name, or DSN, is a string of characters that contains the information needed to link to a database. The prefix name (for example, pgsql for PostgreSQL database), a colon, and the server keyword make up the string.

• Username - A string containing the username of the user. For certain PDO drivers, this parameter is optional.
• Password - The user's password is stored in this string. For certain PDO drivers, this parameter is optional.
• Driver options - This is an optional parameter—a key=>value list of link options unique to the driver.

On completion, this method returns a PDO in PHP object. If there is a mistake, a PDOException object is returned.

Details of PDO Statement Class

The PDO represents a relation between PHP and a database server. The PDOStatement reflects a prepared statement and the corresponding result set after it is executed. The PDOException class reflects a PDO in PHP error.

Represents a prepared statement and its corresponding outcome collection after it is executed.

PDO Statement Implements Traversable 

{ /* Properties */

 readonly string $queryString; 

 /* Methods */ 

 public bool bindColumn ( mixed $column , mixed &$param [, int $type [, int $maxlen [, mixed $driverdata ]]] ) 

 public bool bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] ) 

 public bool bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] ) 

 public bool closeCursor ( void ) 

 public int columnCount ( void ) 

 public void debugDumpParams ( void ) 

 public string errorCode ( void ) 

 public array errorInfo ( void ) 

 public bool execute ([ array $input_parameters ] )

 public mixed fetch ([ int $fetch_style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]] ) 

 public array fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] ) 

 public string fetchColumn ([ int $column_number = 0 ] ) 

 public mixed fetchObject ([ string $class_name = "stdClass" [, array $ctor_args ]] ) 

 public mixed getAttribute ( int $attribute ) 

 public array getColumnMeta ( int $column ) 

 public bool nextRowset ( void ) 

 public int rowCount ( void ) 

 public bool setAttribute ( int $attribute , mixed $value ) 

 public bool setFetchMode ( int $mode ) 

}

For example: The following example demonstrates how to bind a variable to a column in a result collection.

<?php

try {

$dbhost = 'localhost';

$dbname='hr';

$dbuser = 'root';

$dbpass = '';

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

}catch (PDOException $e) {

echo "Error : " . $e->getMessage() . "<br/>";

die();

}

$query = "SELECT fname, lname, dtob, country, emailid FROM user_details where gender = 'M'";

$stmt = $conn->prepare($query);

$stmt->execute();

$stmt->bindColumn('emailid', $email);

while ( $row = $stmt->fetch( PDO::FETCH_BOUND ) )

{

echo "$email"."<br>";

}

?>

Conclusion

This was all about PDO in PHP. In this article, we learned about PDO, how to install PDO, what are Predefined Constants and Supported Databases. We saw some sample databases, tables, table structure, table records and discussed in detail about the PDO class, the PDO class methods and PDOStatement class.

Want to learn more about the concept? You must enroll in the Simplilearn’s Full Stack Developer - MERN Stack course. This course will help you find grip over the concept. And if you want to skill-up your expertise, you can check out Simplilearn’s skillup platform, which offers several free online courses that are focused on building strong foundational skills for career growth.