PHP Data Object has a much nicer interface, you will end up being more productive, and write safer and cleaner code. PDO also has different drivers for different SQL database vendors which will allow you to easily use other vendors without having to relearn a different interface. (though you will have to learn slightly different SQL probably). Instead of concatenating escaped strings into SQL, in PDO you bind parameters which is an easier and cleaner way of securing queries. Binding parameters also allow for a performance increase when calling the same SQL query many times with slightly different parameters. PDO also has multiple methods of error handling. The biggest issue I have seen with mysql_* code is that it lacks consistent handling, or no handling at all! With PDO in exception mode, you can get consistent error handling which will end up saving you loads of time tracking down issues.
Getting Connected.
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //to show any sql error
Error Handling
try {
//connect as appropriate as above
$db->query('hi'); //invalid query!
} catch(PDOException $ex) {
echo "An Error occured!"; //user friendly message
some_logging_function($ex->getMessage());
$db->errorInfo(); to get the error details.
}
Select Query
$stmt = $db->query('SELECT * FROM table');
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
echo $row['field1'].' '.$row['field2'];
}
query() method returns a PDOStatement object. A Cursor Handler of that Table, need to use loop to retrieve data.
$stmt = $db->query('SELECT * FROM table');
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
echo $row['field1'].' '.$row['field2'];
}
$stmt->fetch(PDO::FETCH_ASSOC) will fetch single row as an associative Array, to fetch all records use fetchAll.
Row Count
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
Last Insert ID
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();
SQL Prepare
$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);
The prepare method sends the query to the server, and it's compiled with the '?' placeholders to be used as expected arguments. The execute method sends the arguments to the server and runs the compiled statement. Since the query and the dynamic parameters are sent separately, there is no way that any SQL that is in those parameters can be executed... so NO SQL INJECTION can occur! This is a much better and safer solution than concatenating strings together.
$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);
Executing Prepared Statement in a Loop
$values = array('bob', 'alice', 'lisa', 'john');
$name = '';
$stmt = $db->prepare("INSERT INTO table(`name`) VALUES(:name)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
foreach($values as $name) {
$stmt->execute();
}
Transaction
try {
$db->beginTransaction();
$db->exec("SOME QUERY");
$stmt = $db->prepare("SOME OTHER QUERY?");
$stmt->execute(array($value));
$stmt = $db->prepare("YET ANOTHER QUERY??");
$stmt->execute(array($value2, $value3));
$db->commit();
} catch(PDOException $ex) {
//Something went wrong rollback!
$db->rollBack();
echo $ex->getMessage();
}
