PHP PDO vs regular mysql_connect

Question

Should I use php PDO or regular mysql_connect to execute database queries in PHP?

Which one is faster?

One of the great benefits of PDO is that the interface is consistent across multiple databases. For prepared statements, there are some interesting functions that prevent you from escaping all query strings. PDO portability is greater than mysql_connect.

So, should you use PDO for these reasons or stick to traditional mysql_connect?

Answer 1

PDO is a bit slower than mysql_ * But it has more mobility. PDO provides a single interface for multiple databases. This means that you can use multiple databases without using mysql_query for mysql, mssql_query for MS sql, etc. Just use something like $ db-> query ("INSERT INTO ...") always. No matter which DB driver you use.

Thus, for a larger or portable PDO project, it is preferable. Even the zend framework uses PDO.

Answer 2

Some quick timings show that PDOs are slightly faster when connected.

$start = microtime(true); for($i=0; $i<10000; ++$i) { try { $db = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage()."\n"; } $db = null; } $pdotime = microtime(true) - $start; echo "PDO time: ".$pdotime."\n"; $start = microtime(true); for($i=0; $i<10000; ++$i) { $db = mysql_connect($host, $user, $password); if(!$db) { echo "Connection failed\n"; } if(!mysql_select_db($schema, $db)) { echo "Error: ".mysql_error()."\n"; } mysql_close($db); } $rawtime = microtime(true) - $start; echo "Raw time: ".$rawtime."\n"; 

Gives results such as

 PDO time: 0.77983117103577 Raw time: 0.8918719291687 PDO time: 0.7866849899292 Raw time: 0.8954758644104 PDO time: 0.77420806884766 Raw time: 0.90708494186401 PDO time: 0.77484893798828 Raw time: 0.90069103240967 

In any case, the speed difference will be negligible; establishing a network connection is likely to take LOT longer than any overhead caused by PDO, especially if the mysql server is on a different host.

You mentioned all the reasons for using PDO yourself. Indeed, never use mysql_ * functions directly, use PDO or use some other library .

Answer 3

I don’t think that speed is what people look for when they use PDO - I don’t know if there is a difference, and I’m very interested: as long as I make a couple of queries to the database when creating the page, a couple of milliseconds per PHP side will not change anything.

There are two / three great things with PDO compared to mysql_* :

• A more or less consistent interface through databases; better than using mysql_* , pg_* , oci_* , ...
• Object Oriented API ( mysqli_* has an OO-API, but not mysql_* )
• Support for new MySQL functions> = 4.1 (same as mysqli_* , but not mysql_* , again)

BTW: Usually I use PDO - either "manually", or how it is integrated / used by the Zend Framework and / or Doctrine .

As a side element: even if you are not going to use PDO, note that it is recommended to use mysqli instead of mysql.

See this PHP manual page for more on this.

Answer 4

• In PDO, you can use bound parameters, which will prevent most attacks on SQL injection.
• You can increase speed with the prepared PDO instructions.
• standard interface for all db backends
• There are many useful methods (for example, the fetch * family)

Answer 5

I did some performance testing to compare the Mysqli functions with the PDO functions, using both prepared statements and regular direct queries (tested using select statements in the Mysqlnd and MyISAM tables).

I found that PDO queries are slightly slower than Mysqli, but only slightly. This makes sense because PDO is used for this purpose basically just a shell that calls Mysqli functions. The advantage of using PDO is that it is slightly easier to transfer to another database, since function names are not specific to MySQL.

The real difference in performance is whether you use prepared queries. There is a large and significant decrease in performance when using prepared queries. Other people who tested them found the same results.

The only time-requested requests are faster if you prepare the request once and then send it thousands of times with different data values. Otherwise, it is always faster to use mysqli :: query () or PDO :: query (). But it is important to remember that these functions do not avoid data values ​​for you, so you need to remember to use mysqli :: real_ escape_ string () or PDO :: quote () for variable data.

Answer 6

I would recommend using PDO if there is no specific reason why you cannot. If there is no difference between them, and you have no reason not to use PDO, I think it would be better to deal with the use of DB abstraction in your applications than with mysql_ * simply because it exists. I would say it's best to win.

Answer 7

In both cases, you call the same mySQL server from the same Php server ... so you cannot notice a big difference.

If you need good performance, think of a cache (memcache or a simple Php file ...) and create a good database structure (INDEX ...)

Answer 8

• PDO is better than SQl
• PDO and its readiness statement provide the best secure code for SQL injection
• PDO is object oriented;)
• PDO is compatible with some databases as previously described.
• MySQLl_ * Deprecated and will be removed soon

• PDO provides more functionality with fewer codes Example:

  PDO

  • To plug
  • Check the box "<" AND ">" AND "#" (this check is for global purposes)
  • To prepare
  • Run
  • Close

MySQL _ *

• To plug
• Add backslash
• Xsafe
• Check the box "<" AND ">" AND "#" (this check is for global purposes)
• Inquiry
• Close

both have the same functionality, but you are comparing PDO codes more humanly readable :) And so what do you think?

Answer 9

If performance is not a “real problem” for you, you should use PDO. Performance differs in small fields, and PDO has a very nice and portable interface between databases, which can save you some headaches if you need to use several database drivers.

Answer 10

The mysql_connect function has been deprecated since PHP 5.5.0 and, like most legacy functions, will be removed. Therefore, prefer to use PDO_MySQL (or another MySQLi alternative) over mysql_connect .

Source: http://php.net/manual/en/function.mysql-connect.php

Answer 11

Some of the benefits of PDO:

• Access to multiple databases.
• Many database drivers are provided for connecting to another database.
• When switching from one database to another database, you do not need to write all the code to connect to the new database, just change the connection string and some query that is required for the new database.
• It provides a preparation instruction, which is a kind of request template that is compiled only once and can be executed as many times as you want by simply changing the attributes called place-holder.
• Simple and efficient general operation: Insert, update ... etc.

Answer 12

PDO database connection code:

 <?php $dbhost = 'localhost'; $dbname = 'clsrepair'; $dbuser = 'root'; $dbpass = ''; try { $db = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo "Connection error: ".$e->getMessage(); } ?> 

Normal MySQL database connection code:

 <?php mysql_connect("localhost","root", ""); mysql_select_db ("clsrepair"); ?> 

or

  <?php $dbHost = 'localhost'; // usually localhost $dbUsername = 'root'; $dbPassword = ''; $dbDatabase = 'clsrepair'; $db = mysql_connect($dbHost, $dbUsername, $dbPassword) or die ("Unable to connect to Database Server."); mysql_select_db ($dbDatabase, $db) or die ("Could not select database."); ?> 

MySQL database connection code is easy, but PDO has many advantages.