{"id":2757,"date":"2015-04-29T11:17:22","date_gmt":"2015-04-29T05:47:22","guid":{"rendered":"http:\/\/cmscomputer.in\/blog\/?p=2757"},"modified":"2015-07-06T23:26:50","modified_gmt":"2015-07-06T17:56:50","slug":"difference-between-mysqli-and-pdo-in-php","status":"publish","type":"post","link":"https:\/\/www.cmscomputer.in\/blog\/difference-between-mysqli-and-pdo-in-php\/","title":{"rendered":"Difference between Mysqli and PDO in PHP."},"content":{"rendered":"

For accessing a database in PHP, we have got two choices: MySQLi (MySQL improved) and PDO (PHP Data Objects) . It is always good to know about these in detail before choosing one.<\/p>\n

The major differences are listed here.<\/p>\n

    \n
  1. Database Support<\/li>\n
  2. Database Connection.<\/li>\n
  3. Security<\/li>\n
  4. Performance<\/li>\n<\/ol>\n

    Database Support<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Database Support<\/strong><\/td>\n<\/tr>\n
    PDO<\/strong><\/td>\nMySQLi<\/strong><\/td>\n<\/tr>\n
    Cubrid<\/td>\nMySQL<\/td>\n<\/tr>\n
    FreeTDS \/ Microsoft SQL Server \/ Sybase<\/td>\n<\/td>\n<\/tr>\n
    Firebird<\/td>\n<\/td>\n<\/tr>\n
    IBM DB2<\/td>\n<\/td>\n<\/tr>\n
    IBM Informix Dynamic Server<\/td>\n<\/td>\n<\/tr>\n
    MySQL<\/td>\n<\/td>\n<\/tr>\n
    Oracle Call Interface<\/td>\n<\/td>\n<\/tr>\n
    ODBC v3 (IBM DB2, unixODBC and win32 ODBC)<\/td>\n<\/td>\n<\/tr>\n
    PostgreSQL<\/td>\n<\/td>\n<\/tr>\n
    SQLite 3 and SQLite 2<\/td>\n<\/td>\n<\/tr>\n
    Microsoft SQL Server \/ SQL Azure<\/td>\n<\/td>\n<\/tr>\n
    4D<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Database Connection<\/h2>\n

    PDO<\/em>
    \n$pdo = new PDO(\"mysql:host=localhost.com;dbname=data\", 'username', 'password');<\/code><\/p>\n

    Mysqli, procedural style<\/em>
    \n$mysqli = mysqli_connect('localhost.com','username','password','data');<\/code><\/p>\n

    Mysqli,object oriented style<\/em>
    \n$mysqli = new mysqli('localhost.in','username','password','data');<\/code><\/p>\n

    Security<\/h2>\n

    Both PDO and MySQLi provide SQL injection security, as long as developer follow them with features like escaping and parameter binding with prepared statements.
    \nFor example a hacker is trying to inject some malicious SQL through the \u2018name\u2019 HTTP query parameter (POST):
    \n $_POST['name'] = \"'; DELETE FROM users; \/*\" <\/code><\/p>\n

    If we fail to escape, it will be added in query \u201cas it is\u201d it will delete all rows from users table as PDO and MySQLi support multiple queries.
    \n\/\/ PDO, \u201cmanual\u201d escaping
    \n$name = PDO::quote($_POST['name']);
    \n$pdo->query(\"SELECT * FROM users WHERE name = $name\");
    \n\/\/ mysqli, \u201cmanual\u201d escaping
    \n$name = mysqli_real_escape_string($_POST['name']);
    \n$mysqli->query(\"SELECT * FROM users WHERE name = '$name'\");
    \nPDO::quote() not only escapes the string, but it also quotes it. But mysqli_real_escape_string() will only escape the string, you will need to apply the quotes manually.
    \n\/\/ PDO, prepared statement
    \n$pdo->prepare('SELECT * FROM users WHERE name = :name');
    \n$pdo->execute(array(':name' => $_POST['name']));
    \n\/\/ mysqli, prepared statements
    \n$query = $mysqli->prepare('SELECT * FROM users WHERE name = ?');
    \n$query->bind_param('s', $_POST['name']);
    \n$query->execute();<\/code>
    \nI always recommend you to use prepared statements with bound queries instead of PDO::quote() and mysqli_real_escape_string().<\/p>\n

    Performance<\/h2>\n

    Both PDO and MySQLi are quite fast. For SELECT query, MySQLi was about 2.5% faster for non-prepared statements and about 6.5% faster for prepared statements<\/p>\n

    Summary<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n
    <\/td>\nPDO<\/strong><\/td>\nMySQLi<\/strong><\/td>\n<\/tr>\n<\/thead>\n
    Database support<\/strong><\/td>\n12 different drivers<\/td>\nMySQL only<\/td>\n<\/tr>\n
    API<\/strong><\/td>\nOOP<\/td>\nOOP + procedural<\/td>\n<\/tr>\n
    Connection<\/strong><\/td>\nEasy<\/td>\nEasy<\/td>\n<\/tr>\n
    Named parameters<\/strong><\/td>\nYes<\/td>\nNo<\/td>\n<\/tr>\n
    Object mapping<\/strong><\/td>\nYes<\/td>\nYes<\/td>\n<\/tr>\n
    Prepared statements<\/strong>
    \n(client side)<\/strong><\/td>\n    		Yes<\/td>\nNo<\/td>\n<\/tr>\n
    Performance<\/strong><\/td>\nFast<\/td>\nFast<\/td>\n<\/tr>\n
    Stored procedures<\/strong><\/td>\nYes<\/td>\nYes<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Finally, MySqli is native for php and works very well with MySql. So, you can choose Mysqli only if you are working on Mysql database and not familiar with PDO statements. Its always recommended to prefer PDO, as it supports twelve different database drivers including Mysql database. Performance point of view Mysqli is better and security point of view, both are safe as long as the developer follows recommended statements.<\/p>\n","protected":false},"excerpt":{"rendered":"

    For accessing a database in PHP, we have got two choices: MySQLi (MySQL improved) and PDO (PHP Data Objects) . It is always good to know about these in detail before choosing one. The major differences are listed here. Database Support Database Connection. Security Performance Database Support Database Support PDO<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_joinchat":[],"footnotes":""},"categories":[1,7],"tags":[],"class_list":["post-2757","post","type-post","status-publish","format-standard","hentry","category-uncategorized","category-web-technologies"],"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/posts\/2757"}],"collection":[{"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/comments?post=2757"}],"version-history":[{"count":9,"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/posts\/2757\/revisions"}],"predecessor-version":[{"id":4063,"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/posts\/2757\/revisions\/4063"}],"wp:attachment":[{"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/media?parent=2757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/categories?post=2757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cmscomputer.in\/blog\/wp-json\/wp\/v2\/tags?post=2757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}