Cheat sheet for using PDO (PHP Data Objects) in connecting and querying MySQL database.

PDO and MySQL.md

PDO (PHP Data Objects)

Cheat sheet for using PDO to interact with MySQL db's

---

Used to use MySQL then found out it was being depreciated, this is of course right after I got the hang of it and was almost finished with the project i was using to learn myself. So desided to make the switch to PDO way much easier especially if you have security in mind.

For instance instead of $var = mysql_real_escape_string($_POST['data'];, you just $var = $db->quote($_POST['data'];. Or even better use prepared staements and the underlying driver will not only escape but quote the string for you!

• connect to MySQL and db with $db = new PDO("mysql:host=Localhost; dbname=databasename",'username','password');
• to query the db set your query string $q = "SELECT * FROM tablename";
• then query the db with $result = $db->query($q);
• to put $result into array with $arr = $result->fetch(PDO::FETCH_ASSOC);
• call values with echo $arr['value'];
• to free up resources $result->closeCursor();
• and to close connection $db = null;

that's it! to do prepared statements...

• $q = $db->prepare("SELECT * FROM tablename WHERE name = :name AND pass = :password");
• the :varName is used here $q->bindParam(":name",$_POST['name']);
• when you are done binding all parameters execute with $q->execute();

See the doc below for more detailed instructions

---

With all the cheat sheets thay are a work in progress and could contain mistakes. If you find these helpfull or find shit that is just plain wrong, please comment so I can fix.

Thanks

PDO_cheat_sheet.php

<?php 
/*
For this we will have a db set up in phpmyadmin with following:
database name = test_db
table = test_table
-- CREATE TABLE test_table (ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), fname VARCHAR(20), lname VARCHAR(20)) --
ID | fname | lname

create this and add a couple of entries (here gerry garcia & bob dylan)
INSERT INTO `test_db`.`test_table` (`ID`, `fname`, `lname`) VALUES (NULL, 'gerry', 'garcia'), (NULL, 'bob', 'dylan');

connect to mysql and db without variables
$db = new PDO("mysql:host=localhost;dbname=databaseName", "user", "password");
*/

//with variables for easy changes
$server = 'localhost';
$dbName = 'test_db';
//for PDO put into $dns variable for ease (PDO connects with DSN or Data Source name)
$dsn = "mysql:host=".$server.";dbname=".$dbName;
$user = 'root';
$pass = '';

//now set up connection to db with PDO
$db = new PDO($dsn, $user, $pass);

//that's it connection to mysql and db essablished, you can check with:

if($db){
  echo "<p>connection successful!</p>";
}

//to query database and output data

//set up query string
$query = "SELECT * FROM test_table";
// query database
$result = $db->query($query);

//creat array and loop through it
while($arr = $result->fetch(PDO::FETCH_ASSOC)){
	echo $arr['ID']." | ".$arr['fname']." ".$arr['lname']."<br>";
}

/*
Using prepared statements can make life easier with variables and such
also the paramiters do not have to be quoted like $db->quote(string) as the
underling driver will quote and escape the data to safeguard against SQL injections
*/

if(isset($_POST['submit'])){
/*
here we will be addeing people to the table from a FORM so create either an array
or list of POST varables for the prepared statment
*/

$fistName = $_POST['fname'];
$lastName = $_POST['lname'];
//build query string but this time use variables like so :variableName
$newQuery = $db->prepare("INSERT INTO test_table (`ID`, `fname`, `lname`) VALUES (NULL, :firstName, :lastName)");
//create placeholder and bind to variables in query
$newQuery->bindParam(":firstName",$firstName);
$newQuery->bindParam(":lastName",$lastName);
//execute query
$newQuery->execute();

}

/*=============================
ERROR ERROR ERROR ERROR ERROR
=============================*/
/* there are multiple error modes but i'm not smart enought yet to use them
so stick with the default of PDO::ERRMODE_SILENT this doesn;t interupt script
and you don't have to set it with PDO::setAttribute() like
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Lets create a query that looks for a table that doesn't exsits.
*/

$badQuery = "SELECT * FROM non_exsistent_table";
$badResult = $db->query($badQuery);

//now return the error
$error = $db->errorInfo();
if(!is_null($error[2])){
	echo $error[2];
}
/*$error[0] & $error[1] are just number calls on the error not human readable
but probably googleable you could always use print_r($error); to see the whole error
*/
/*====================================
FREE UP RESOURCES AND CLOSE CONNECTION
====================================*/
//to free resources from a query called $result
$result->closeCursor();
//to close connection
$db = null;
//you can check closed connection by
if(!$db){
	echo "<p>connection closed</p>";
}

//this was not possible witout the online article "Migrate from the MySQL Extension to PDO"
//by Timothy Boronczyk @ http://www.sitepoint.com/migrate-from-the-mysql-extension-to-pdo/

//to make it easier for me to learn i put things into a gist and create dummy pages in cloud9.io over and over and over :o)

//PDO documentaion  http://php.net/pdo
 ?>