In this post I am discussing the difference between mysql and pdo with example and explanation. So let’s have a look on this.
What is PDO?
PDO – PHP Data Objects – is a database access layer providing a uniform method of access to multiple databases.
PHP PDO => Database Driver => DATABASE
Connection to MySQL database:
In mysql function we can connect to the database by following code:
<?php
$link = mysql_connect("localhost.com", "user", "pass");
mysql_select_db("data", $link);
mysql_set_charset('UTF-8', $link);
?>
In PDO: We need to create a new PDO object. The constructor accepts mostly four parameters for specifying the database source which are DSN (data source name) and optionally username, password.
DSN is basically a string of options of driver to use and connection details of database.
<?php
$pdo = new PDO('mysql:host=localhost.com;dbname=data;charset=utf8', 'user', 'pass'); ?>
It will throw PDOException object if there any connection error, we can catch it to handle Exception.
You can also pass fourth parameter of several driver options as an array. Some PDO drivers don’t support native prepared statements, So I recommend pass the parameter which put PDO into exception mode, so PDO performs emulation of the prepare. You can also manually enable this emulation. To use the native server-side prepared statements, you should explicitly set it false.Prepare emulation should be turned off to use PDO safely.
We can set attributes after PDO construction with the setAttribute method:
<?php
$db = new PDO('mysql:host=localhost.com;dbname=data;charset=utf8', 'user', 'pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
?>
Error Handling:
Error handling is much easier in PDO than mysql_*.
In mysql:
<?php
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));
?>
We can not handle the thing in die so or die is not a good approach to handle error. Die will end the script abruptly and then echo the error to the screen which you usually don’t want to show to your users, and let hackers to discover your schema.
In PDO a better solution is exceptions. Anything in PDO should be wrapped in a try-catch block. Also we can force PDO into error mode by setting the error mode attribute from following error modes.
PDO::ERRMODE_SILENT: By setting this error code you must check each result then check $db->errorInfo() to get error details.
PDO::ERRMODE_WARNING: It privides Run-time warnings (E_WARNING) no fatal errors. It’s excecution does not hault the script.
PDO::ERRMODE_EXCEPTION: It Throw exceptions that shows an error raised by PDO. You should not throw a PDOException with your own code. It acts much like or die(mysql_error()), when it isn’t caught. But we can catch these PDOException and handle as we want.
We can set these error modes as follow:
<?php $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT ); $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); ?>
You can add a try/catch block as like below:
<?php
try {
$db->query('hello'); //Invalid query
}
catch (PDOException $ex) {
//$ex->getMessage();
echo "An Error has occured";
}
?>
You can set a user friendly error message instead of exception message. You should use try/catch block for your PDO part also you can use it outside the function that has the PDO functions.
<?php
function get_data($db) {
$stmt = $db->query("SELECT * FROM data");
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
try {
get_data($db);
} catch(PDOException $ex) {
echo "An Error has occured";
//$ex->getMessage(); // you can log these errors
}
?>
Fetching Data from database:
Objects => PDO => ARRAYS
In mysql
<?php
$result = mysql_query('SELECT * from data') or die(mysql_error());
$num_rows = mysql_num_rows($result);
while($row = mysql_fetch_assoc($result)) {
echo $row['field1'];
}
?>
In PDO, you can get a single row by using fetch:
<?php
$stmt = $db->query('SELECT * FROM data');
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['field1'];
}
?>
Or you can get all by using fetchAll:
<?php
$stmt = $db->query('SELECT * FROM data');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
PDOStatement::fetchAll() – Returns an array containing all of the result set rows
PDOStatement::fetchColumn() – Returns a single column from the next row of a result set
PDOStatement::fetchObject() – Fetches the next row and returns it as an object.
PDOStatement::setFetchMode() – Set the default fetch mode for this statement
query() returns a PDOStatement object by that you can fetch the data directly by using foreach and for loop also.
Getting the Row Count:
Instead of using mysql_num_rows to get the number of returned rows, you can get a PDOStatement and do rowCount(), like:
<?php
$stmt = $db->query('SELECT * FROM data');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
?>
Getting the Last Inserted ID
<?php
$result = $db->exec("INSERT INTO data(fname, lname) VAULES('John', 'Doe')");
$insertedId = $db->lastInsertId();
?>
Update statements:
Prepare => Bind => Excecute
In mysql:
<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);
?>
In pdo: we can be done the same by:
<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;
?>
PDO::exec(execute an SQL statement and returns the number of affected rows)
Prepared Statements:
A prepared statement is a precompiled SQL statement that can be executed multiple times by sending the data to the server.
The flow of the prepared statement is as follow:
1. Prepare: The statement template is created by the application and sent to the DBMS. Some parameters are left unspecified by placeholders “?” like as below:
INSERT INTO data (fname, lname) VALUES (?, ?)
2. Bind: The DBMS parses, compiles, and performs query optimization on statement template and stores the result without executing it.
3. Execute: The application binds values for the parameters and the DBMS executes the statement. The application may execute the statement as many times as it wants with different values.
Named placeholders:
You can use a descriptive names preceded by a colon, instead of question marks. We don’t care about position or order of value in named placeholder:
$stmt->bindParam(':foo', $foo);
You can also bind using an execute array as well:
<?php
$stmt = $db->prepare("SELECT * FROM data WHERE id=:id AND fname=:fname");
$stmt->execute(array(':fname' => $fname, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
For OOP users named placeholders have the ability to insert objects directly into database, assuming the properties match the named fields. For example:
<?php
class user {
public $fname;
public $lname;
function __construct($a,$b) {
$this->fname = $a;
$this->lname = $b;
}
}
$user = new user('john','doe');
$stmt = $db->prepare("INSERT INTO data (fname, lname) value (:fname, :lname)");
$stmt->execute((array)$user);
?>
Unnamed placeholders:
In unnamed placeholders we must follow the proper order of the elements in the array that we are passing to the PDOStatement::execute() method. Let’s have an example of unnamed placeholders:
<?php
$stmt = $db->prepare("INSERT INTO data (fname, lname) values (?, ?)");
$stmt->bindValue(1, $fname, PDO::PARAM_STR);
$stmt->bindValue(2, $lname, PDO::PARAM_STR);
$stmt->execute();
?>
also
<?php
$stmt = $db->prepare("INSERT INTO data (fname, lname) values (?, ?)");
$stmt->execute(array('john', 'doe'));
?>
You can see ? instead of a name like in a name placeholder and it follow the order of parameters.
So PDO has many advantages over the mysql_* functions such as:
- It has same interface for different relational database systems.
- It provides the protection against SQL injections.
- It is much easier (uses an object-oriented approach).
- It supports Stored Procedures.
- It has Prepared statements feature.
- Various fetch/error handling modes that you can set and change on the fly.
I hope it has explained the difference between MySQL and pdo.
