PHP offers three different APIs to connect to MySQL. There are the
mysql,
mysqli, and
PDO
extensions.
The
mysql_* functions are
very popular, but their use is not encouraged anymore. The documentation team
is discussing the database security situation, and educating users to move away
from the commonly used ext/mysql extension is part of this (check
php.internals:
deprecating ext/mysql).
And the later PHP developer team has taken the decision to generate
E_DEPRECATED errors when users connect to MySQL,
whether through
mysql_connect(),
mysql_pconnect() or the
implicit connection functionality built into
ext/mysql.
ext/mysql is now
officially deprecated
as of PHP 5.5!
See the Red Box?
When you go on any mysql_* function manual page, you see a red box,
explaining it should not be used anymore.
Why
Moving away from
ext/mysql
is not only about security, but also about having access to all the features of
the MySQL database.
ext/mysql was built for
MySQL
3.23 and only got very few additions since then while mostly keeping
compatibility with this old version which makes the code a bit harder to
maintain. Missing features that is not supported by
ext/mysql include:(
from PHP manual)
Reason to not use mysql_*
function
- Not under
active development
- In
deprecation process (so the intention is to remove it from a future
version of PHP)
- Lacks an
OO interface
- Doesn't
support non-blocking, asynchronous queries
- Doesn't
support prepared statements or parametrized
queries
- Doesn't
support stored procedures
- Doesn't
support multiple statements
- Doesn't
support transactions
- Doesn't
support all of the functionality in MySQL 5.1
Above
point quoted from Quentin's answer
Lack of support for prepared statements is particularly important as they
provide a clearer, less error prone method of escaping and quoting external
data than manually escaping it with a separate function call.
See the
comparison
of SQL extensions
Suppressing deprecation warnings
While code is being converted to MySQLi/PDO,
E_DEPRECATED errors can be suppressed by setting
error_reporting in
php.ini to exclude
E_DEPRECATED:
error_reporting = E_ALL ^ E_DEPRECATED
Note that this will also hide
other deprecation warnings,
which, however, may be for things other than MySQL.(
from
PHP manual)
The article
PDO
vs. MySQLi: Which Should You Use? by
Dejan Marjanovic will help you
to choose.
And a better way is
PDO,
and I am now writing a simple PDO tutorial.
A simple and short PDO tutorial
Q.First question in my mind was: what is PDO?
A. “
PDO – PHP Data Objects – is a database access layer
providing a uniform method of access to multiple databases.”
Connecting to MySQL
With
mysql_* function or
we can say it the old way (deprecated in PHP 5.5 and above)
<?php
$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);
With
Pdo: All you need to
do is create a new PDO object. The constructor accepts parameters for
specifying the database source PDO's constructor mostly takes four parameters
which are DSN (data source name) and optionally username, password.
Here I think you are familiar with all except DSN; this is new in PDO. A DSN
is basically a string of options that tell PDO which driver to use, and
connection details. For further reference, check
PDO
MYSQL DSN.
<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
Note: you can also use
charset=UTF-8,
but sometimes it causes an error, so better to use
utf8.
If there any connection error it will throw PDOException object it can be
cached to handle Exception further.
Good read :
Connections and
Connection management ¶
You can also pass in several driver options as an array to the fourth parameter.
I recommend passing the parameter which puts PDO into exception mode. Because
some PDO drivers don't support native prepared statements, so PDO performs
emulation of the prepare. It also lets you manually enable this emulation. To
use the native server-side prepared statements, you should explicitly set it
false
The other is to turn off prepare emulation which is enabled in the MySQL
driver by default, but prepare emulation should be turned off to use PDO
safely.
I will later explain why prepare emulation should be turned off To find
reason please check
this
post .
It is only usable if you are using an old version of MySQL which I do not
recommended.
Below I am showing how you can do it.
<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8',
'username',
'password',
array(PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
Can we set attributes after PDO construction?
Yes, we can also set some attributes after PDO construction
with the
setAttribute
method:
<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8',
'username',
'password');
$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_*.
A common practice when using
mysql_*
is:
<?php
//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));
OR die() is not a good
way to handle the error since we can not handle the thing in
die. It will just end the script
abruptly and then echo the error to the screen which you usually do NOT want to
show to your end users, and let bloody hackers discover your schema.
Alternately, the return values of
mysql_*
functions can often be used in conjunction with
mysql_error() to handle
errors.
PDO offers a better solution: exceptions. Anything we do with PDO should be
wrapped in a
try-
catch block. We can force PDO into one
of three error modes by setting the error mode attribute. Three error handling
modes are below.
PDO::ERRMODE_SILENT. It's just setting error
codes and acts pretty much the same as mysql_*
where you must check each result and then look at $db->errorInfo(); to get the error details.PDO::ERRMODE_WARNING Raise E_WARNING. (Run-time warnings (non-fatal
errors). Execution of the script is not halted.)PDO::ERRMODE_EXCEPTION: Throw exceptions. It
represents an error raised by PDO. You should not throw a PDOException from your own code. See Exceptions for more
information about exceptions in PHP. It acts very much like or die(mysql_error());, when it isn't caught.
But unlike or die(), the PDOException can be caught and handled gracefully
if you choose to do so.
Good read
Like:
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
And you can wrap it in
try-
catch, like below:
<?php
try {
//Connect as appropriate as above
$db->query('hi'); //Invalid query!
}
catch (PDOException $ex) {
echo "An Error occured!"; //User friendly message/message you want to show to user
some_logging_function($ex->getMessage());
}
You do not have to handle with
try-
catch right now. You can catch it at any
time appropriate, but I strongly recommend you to use
try-
catch.
Also it may make more sense to catch it at outside the function that calls the
PDO stuff:
<?php
function data_fun($db) {
$stmt = $db->query("SELECT * FROM table");
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
//Then later
try {
data_fun($db);
}
catch(PDOException $ex) {
//Here you can handle error and show message/perform action you want.
}
Also, you can handle by
or die()
or we can say like
mysql_*,
but it will be really varied. You can hide the dangerous error messages in
production by turning `display_errors off' and just reading your error log.
Now, after reading all the things above, you are probably thinking: what the
heck is that when I just want to start leaning simple
SELECT,
INSERT,
UPDATE,
or
DELETE statements? Don't
worry, here we go:
Selecting Data
So what you are doing in
mysql_*
is:
<?php
$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'];
}
Now in PDO, you can do this like:
<?php
$stmt = $db->query('SELECT * FROM table');
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['field1'];
}
Or
<?php
$stmt = $db->query('SELECT * FROM table');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
//Use $results
Note:: if you are using the method like below (
query()), this method returns a
PDOStatement object. So if you want to
fetch the result, use it like above.
<?php
foreach($db->query('SELECT * FROM table') as $row) {
echo $row['field1'];
}
In PDO Data, it is obtained via the
->fetch(),
a method of your statement handle. Before calling fetch, the best approach would
be telling PDO how you’d like the data to be fetched. In the below section I am
explaining this.
Fetch Modes
Note the use of
PDO::FETCH_ASSOC
in the
fetch() and
fetchAll() code above. This tells
PDO to return the rows as an associative
array with the field names as keys. There are many other fetch modes too which
I will explain one by one.
First of all, I explain how to select fetch mode:
$stmt->fetch(PDO::FETCH_ASSOC)
In the above, I have been using
fetch().
You can also use:
Now I come to fetch mode:
PDO::FETCH_ASSOC: returns an array indexed by
column name as returned in your result setPDO::FETCH_BOTH (default): returns an array
indexed by both column name and 0-indexed column number as returned in
your result set
There are even more choices! Read about them all in
PDOStatement Fetch documentation..
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 table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
Getting the Last Inserted ID
<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();
Insert and Update or Delete statements
What we are doing in
mysql_*
function is:
<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);
And in pdo, this same thing can be done by:
<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;
In the above query
PDO::exec(execute an SQL statement and returns the
number of affected rows)
(Insert and delete will be covered later.)
The above method is only useful when you are not using variable in query.
But when you need to use a variable in a query, do not ever ever try like the
above and there for
prepared
statement or parameterized statement is.
Prepared Statements
Q. What is a prepared statement and why do I need them?
A. A prepared statement is a precompiled SQL statement that
can be executed multiple times by sending only the data to the server.
The typical workflow of using a prepared statement is as follows (
quoted from Wikipedia
three 3 point):
1. Prepare:
The statement template is created by the application and sent to the database
management system (DBMS). Certain values are left unspecified, called
parameters, placeholders or bind variables (labelled "?" below):
INSERT INTO
PRODUCT (name, price) VALUES (?, ?)
2. The
DBMS parses, compiles, and performs query optimization on the statement
template, and stores the result without executing it.
- Execute: At a later time,
the application supplies (or binds) values for the parameters, and the
DBMS executes the statement (possibly returning a result). The application
may execute the statement as many times as it wants with different values.
In this example, it might supply 'Bread' for the first parameter and
'1.00' for the second parameter.
You can use a prepared statement by including placeholders in your SQL.
There are basically three ones without placeholders (don't try this with
variable its above one), one with unnamed placeholders, and one with named
placeholders.
Q. So now, what are named placeholders and how do I use
them?
A. Named placeholders. Use descriptive names preceded by a
colon, instead of question marks. We don't care about position/order of value
in name place holder:
$stmt->bindParam(':bla', $bla);
bindParam(parameter,variable,data_type,length,driver_options)
You can also bind using an execute array as well:
<?php
$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);
Another nice feature for OOP friends is that named placeholders have the
ability to insert objects directly into your database, assuming the properties
match the named fields. For example:
class person {
public $name;
public $add;
function __construct($a,$b) {
$this->name = $a;
$this->add = $b;
}
}
$demo = new person('john','29 bla district');
$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");
$stmt->execute((array)$demo);
Q. So now, what are unnamed placeholders and how do I use
them?
A. Let's have an example:
<?php
$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $add, PDO::PARAM_STR);
$stmt->execute();
and
$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->execute(array('john', '29 bla district'));
In the above, you can see those
?
instead of a name like in a name place holder. Now in the first example, we
assign variables to the various placeholders (
$stmt->bindValue(1, $name, PDO::PARAM_STR);). Then, we
assign values to those placeholders and execute the statement. In the second example,
the first array element goes to the first
?
and the second to the second
?.
NOTE: In
unnamed placeholders we must take
care of the proper order of the elements in the array that we are passing to
the
PDOStatement::execute()
method.
SELECT,INSERT,UPDATE,DELETE prepaired queries
1
SELECT
<?php
$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);
2
INSERT
$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2));
$affected_rows = $stmt->rowCount();
3
DELETE
<?php
$stmt = $db->prepare("DELETE FROM table WHERE id=:id");
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->execute();
$affected_rows = $stmt->rowCount();
4
UPDATE
<?php
$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
$stmt->execute(array($name, $id));
$affected_rows = $stmt->rowCount();
NOTE:
However PDO/MySQLi are not completely safe.
Check some example.
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query('SET NAMES GBK');
$stmt = $pdo->prepare("SELECT * FROM test WHERE name = ? LIMIT 1");
$stmt->execute(array(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));
