PDO Tutorials

Open connection to database :
<?php//Database Constants$DB_SERVER = "localhost";$DB_NAME = "2aekdb";$DB_USER = "root";$DB_PASS = "bbacc";try{ $conn = new PDO("mysql:host=$DB_SERVER;dbname=$DB_NAME",$DB_USER,$DB_PASS); $conn->setAttribute(PDO::ATTR_PERSISTENT,true); $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );}catch(PDOException $pe){ die('Connection error, because: ' .$pe->getMessage());}?>

An example of Select query and show error message if fail :
<?php$sql = "SELECT * FROM traceuser ORDER BY id DESC LIMIT 300";$q = $conn->prepare($sql);$result = $q->execute();if(!$result){ die("Error Select traceuser table query : ". implode(" : ", $q->errorInfo())); //if don't use implode, output of errorInfo() will be "Array".}while($r = $q->fetch(PDO::FETCH_ASSOC)){ $id_db = $r['id']; $username_db = $r['username']; $ibrowser_db = $r['ibrowser']; $operatingsystem_db = $r['operatingsystem']; $ipaddress_db = $r['ipaddress']; $datetime_db = $r['datetime'];}?>If you wrote $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); on database open connection previously, then you don't need to write implode(" : ", $q->errorInfo())); anymore.

An example of Select query with WHERE Clause :
$sql = "SELECT * FROM traceuser WHERE username=? ORDER BY id DESC LIMIT 300";$q = $conn->prepare($sql);$q->execute(array("wwwwwwwwww"));

An example of Select query with two inputs in WHERE Clause :
$title = 'PHP%';$author = 'Bobi%';$sql = "SELECT * FROM books WHERE title like ? AND author like ? ";$q = $conn->prepare($sql);$q->execute(array($title,$author));The question marks on codes above are called as "Position Placeholder".

Below is an example of using "Named Placeholder". The placeholder is not depends on position of value, it depends on name. That's why it called "Named Placeholders" :$title = 'PHP%';$author = 'Bobi%';$sql = "SELECT * FROM books WHERE title like :title AND author like :author ";$q = $conn->prepare($sql);$q->execute(array(':author'=>$author, ':title'=>$title));

Count Result (must use count(*) and $q->fetchColumn()) :
$sql = "SELECT count(*) FROM traceuser WHERE username=? ORDER BY id DESC LIMIT 300";$q = $conn->prepare($sql);$q->execute(array("ddd"));$number_of_rows = $q->fetchColumn();echo $number_of_rows;

Check if sql query successful or fail :
$result = $q->execute(array("ddd")); //add a php variable $result to pdo executeif($result){ echo "<br /><br />sql query successful. ";}else{ echo "sql query fail. "; print_r ($q->errorInfo()) ; //or implode(" : ", $q->errorInfo()));}

First example of Insert query with PDO Prepared :
$sql = "INSERT INTO users (username, password, email) VALUES (?, ?, ?)";$q = $conn->prepare($sql);$q->execute(array($username, $password, $email));

Second example of Insert query with PDO Prepared :
$sql = "INSERT INTO books (title,author) VALUES (:title,:author)";$q = $conn->prepare($sql);$q->execute(array(':author'=>$author,':title'=>$title));

An example of Update query with PDO Prepared :
$title = 'PHP Pattern';$author = 'Imanda';$id = 3;$sql = "UPDATE books SET title=?, author=? WHERE id=?";$q = $conn->prepare($sql);$q->execute(array($title,$author,$id));

Persistent connections are known as way to improve performance. When a persistent connection is requested, PHP checks if there's already an identical persistent connection from earlier remained open. If it exists, it uses it. What meaning of remained open? Persistent connection are links that do not close when the execution of your script ends. An 'identical' connection is a connection that was opened to the same host, with the same username and the same password. We can use PDO::ATTR_PERSISTENT to do persistent connection :
// database connection$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass, array(PDO::ATTR_PERSISTENT => true)); // query$sql = "SELECT * FROM books";$q = $conn->query($sql) or die("ERROR: " . implode(":", $conn->errorInfo()));$r = $q->fetch(PDO::FETCH_ASSOC);print_r($r);echo $conn->getAttribute(PDO::ATTR_PERSISTENT); //true if output is 1, false if output is empty

Check connection attribute. Get value from connection attributes. We can use getAttribute(). Look this example :
echo $conn->getAttribute(PDO::ATTR_DRIVER_NAME);echo "<br>";echo $conn->getAttribute(PDO::ATTR_CLIENT_VERSION);echo "<br>";echo $conn->getAttribute(PDO::ATTR_SERVER_VERSION);
Output will be :
mysql
5.0.51a
5.1.32-community-log

More tutorial at http://www.phpeveryday.com/articles/PDO-Error-Mode-Attributes-P558.html.

---

The different between two prepared statements :
1) fake / emulate pdo prepared statement (client-side prepared statement).
2) real / native pdo prepared statement (server-side prepared statement).

This SQL statement is wrong - "SELECT ice FROM artica AND antarctica" because spelling mistake of "artica", the correct spelling is "arctic".

fake / emulate pdo prepared statement will treat the sentence is right because its database doesn't require to check the statement.

real / native pdo prepared statement will check it and will know the sentence has error, and will treat the sentence is wrong.

For insert query, the real / native pdo / Server prepared statement is slower 10% than fake / emulate pdo / Client ps.
Client PS = 13.5645 seconds.
Server PS = 15.5019 seconds.
But if you use bindParam(), Server PS is 10% faster than Client PS. Eg,
$stmt = $db->prepare('INSERT INTO test(id, label) VALUES (?, ?)');$stmt->bindParam(1, $j);$stmt->bindParam(2, $label);for ($j = 0; $j > $num_rows; $j++) {$stmt->execute(); }For select query, both client ps and server ps is the same speed but server ps become more faster if select more LARGE or MANY records.

The default setting use fake / emulate prepared statement. If you want to use real / native pdo prepared statement, u can use the code : setAttribute(PDO::ATTR_EMULATE_PREPARES,false);More info at : http://blog.ulf-wendel.de/?p=187#pdo.