PDO database connection

PHP tip 7: how to use PDO to connect to your database

by
PDO database connection

 

HOW TO USE PDO TO ESTABLISH A DATABASE CONNECTION AND SEND SQL QUERIES

 

Looking for a complete, step-by-step PHP/MySQL tutorial? Here is it:

 

 

PDO is an abstract database connection extension that you can use to connect to different database types (MySQL, Oracle, PostgreSQL etc.).

 

While specific extensions (like MySQLi) may have slightly better performances, I suggest you to use PDO whenever possible.

PDO code can be easily changed to use a different database type, and it has a more readable syntax compared to most of the other extensions.

It also natively supports prepared statements, which I recommend you tu use to prevent SQL injection attacks.

 

In the following short example you can see how to create a PDO connection (to a MySQL database) and how to execute a simple SELECT query.

(Note: in production environments you should keep access data in a separate file).

<?php
/* PDO database connection */
try
{
	/* Actual connection */
	$PDO = new PDO('mysql:host=localhost;dbname=my_db', 'my_user',  'my_passwd');
	
	/* Set some parameters */
	$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$PDO->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
}
catch (PDOException $e)
{
	/* If there is an error an exception is thrown */
	pdo_exception($e);
}
/* Example query; "?" is a dynamic data placeholder */
$query = 'SELECT * FROM users WHERE user_id > ?';
/* All the dynamic data is put inside an array to use prepared statements */
$params = array('10');
try
{
	/* Use prepared statements for maximum security against injections */
	$res = $PDO->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
	$res->execute($params);
}
catch (PDOException $e)
{
	/* If there is an error an exception is thrown */
	pdo_exception($e);
}
/* Iterate through results */
while (is_array($row = $res->fetch(PDO::FETCH_ASSOC)))
{
	echo $row['user_name'] . '<br>';
}
/* Function to echo the SQL error
   (you should never print database errors in production environments */
function pdo_exception($e)
{
	echo 'PDO exception. Error message: "' . $e->getMessage() . '". Error code: ' . strval($e->getCode()) . '.';
	die();
}

 

 

If you have any questions, feel free to ask in the comments below or on my Facebook Group: Alex PHP café.

If this tip has been helpful to you, please spend a second of your time and share it using the buttons below… thanks!

 

Alex

Leave a Comment