PHP PDO

PHP PDO with MySQL

What stands for PDO?
The PDO stand for PHP Data Objects (PDO) is an extension defines a lightweight, consistent interface for accessing databases in PHP.

After period of using the older conventional way of accessing database, PDO extension was introduced in July 2004 with PHP5 release. The main reason behind the introduction of this new DB access method seems to be security and the speed. You may find many fact and examples in Internet, so I am not going for further detail and let go the point directly.

Here I am going to concern about MySQL database only. Why? Because as you already know many PHP websites uses MySQL as its database and it is open source. Moreover PHP and MySQL blend together so nicely and many hosting servers also support MySQL.

The connection string to the database is more likely as follows and not to forget include charset parameter to DSN as it is important to avoid any errors occur due to character sets. It is also important for the security.

hostname can either be localhost or your remote MySQL server

$db = new PDO('mysql:host= hostname;dbname=testdb;charset=utf8', 'username', 'password');

There are some optional driver parameters which can pass as an array to the above string or can add separately using setAttribute method.

1. $db = new PDO('mysql:host= hostname;dbname=testdb;charset=utf8', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
2. $db = new PDO('mysql:host= hostname;dbname=testdb;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
ATTR_EMULATE_PREPARES need to turned off to use PDO safely and is really only usable if you are using an old version of MySQL.

Error Modes in PDO

• 1. PDO::ERRMODE_SILENT
Same as mysql_* have to check each result and then look at $db->errorInfo(); to get the error details.
• 2. PDO::ERRMODE_WARNING
This mode throws PHP warnings.
• 3. PDO::ERRMODE_EXCEPTION
This mode throws an error type called PDOException. Unless you set it true it acts very much like or die(mysql_error()); in mysql_*. When set to true PDOException can be caught and handled at anywhere. As an example you can catch it inside try catch block after calling to certain DB related function executed.

try {
function db_Operations( );
}
catch (PDOException $ex) {
//show error message to user;
// do_logging($ex->getMessage());
}

PDO Fetch Modes

• PDO::FETCH_ASSOC: This mode returns an array indexed by column name.
• PDO::FETCH_BOTH: This mode is the default and returns an array indexed by both column name and 0-indexed column number.
• PDO::FETCH_BOUND: This mode returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method.
• PDO::FETCH_CLASS: This mode returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.
• PDO::FETCH_INTO: This mode updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class
• PDO::FETCH_LAZY: This mode combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed
• PDO::FETCH_NUM:

The more info can be found in this link. Now begins the interesting part. To make it more easy and understandable with comparison there is no other way better than implementing them in a table :)

Method	Conventional	PDO
Select	$result = mysql_query('SELECT * from table') or die(mysql_error()); $num_rows = mysql_num_rows($result); while($row = mysql_fetch_assoc($result)) { echo $row['field1'].' '.$row['field2']; //etc... }	foreach($db->query('SELECT * FROM table') as $row) { echo $row['field1'].' '.$row['field2']; //etc... } -------------------Or------------------------------------- $stmt = $db->query('SELECT * FROM table'); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['field1'].' '.$row['field2']; //etc... } ----------or fetch all the result as follows-------------- $stmt = $db->query('SELECT * FROM table'); $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
Insert	$results = mysql_query("Insert into table (field1,field2) VALUES('value1',’value2’)") or die(mysql_error()); $affected_rows = mysql_affected_rows($result); echo $affected_rows.' were affected';	$affected_rows = $db->exec("Insert into table (field1,field2) VALUES('value1',’value2’)"); echo $affected_rows.' were affected'
Update	$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error()); $affected_rows = mysql_affected_rows($result); echo $affected_rows.' were affected';	$affected_rows = $db->exec("UPDATE table SET field='value'"); echo $affected_rows.' were affected'
Delete	$results = mysql_query("DELETE from table WHERE field='value'") or die(mysql_error()); $affected_rows = mysql_affected_rows($result); echo $affected_rows.' were affected';	$affected_rows = $db->exec("DELETE from table WHERE field='value'"); echo $affected_rows.' were affected'
Statements With Parameters	$results = mysql_query(sprintf("SELECT * FROM table WHERE id='%s' AND name='%s'", mysql_real_escape_string($id), mysql_real_escape_string($name))) or die(mysql_error()); $rows = array(); while($row = mysql_fetch_assoc($results)){ $rows[] = $row; }	$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?"); $stmt->execute(array($id, $name)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); Here in the prepare method first sends the query to the server along with ‘?’ place holders where they compiles to be used as expected arguments. Then execute method sends the arguments to the server and runs the compiled statement. This make SQL statement much safer as arguments to be used sends separately to the server which has already compiled statement. So no SQL statement send inside a argument going to execute thus prevents SQL Injections.
Bind Parameter method		$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?"); $stmt->bindValue(1, $id, PDO::PARAM_INT) ; $stmt->bindValue(2, $name, PDO::PARAM_STR); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); bindValue() method useful to specify the type for each parameter.
Named Placeholders (Array)		$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name"); $stmt->execute(array(':name' => $name, ':id' => $id)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Name Placeholders bindValue()		$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name"); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->bindValue(':name', $name, PDO::PARAM_STR); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Prepared Insert		$stmt = $db->prepare("INSERT INTO table (field1,field2,field3) VALUES(:field1,:field2,:field3)"); $stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3)); $affected_rows = $stmt->rowCount();
Prepared Update		$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?"); $stmt->execute(array($name, $id)); $affected_rows = $stmt->rowCount();
Prepared Delete		$stmt = $db->prepare("DELETE FROM table WHERE id=:id"); $stmt->bindValue(':id', $id, PDO::PARAM_STR); $stmt->execute(); $affected_rows = $stmt->rowCount();

Correct way to use SQL functions inside PDO.

$name = 'John';
$stmt = $db->prepare("INSERT INTO table(`mod_num`, `name`) VALUES(MOD(7,3), ?)");
$stmt->execute(array($name));

$num1 = 49;
$angle = 30;
$stmt = $db->prepare("INSERT INTO table(`squre_root`, `sine_val`) VALUES(SQRT(?), SIN(?))");
$stmt->execute(array($num1, $angle));

$search='Tom';
$stmt = $db->prepare("SELECT field1 FROM table WHERE field2 LIKE ?");
$stmt->bindValue(1, "%$search%", PDO::PARAM_STR);
$stmt->execute();