Difference in connecting database PHP

Question

What is the difference between connecting database using PDO or mysqli_connect, etc?

What are the pros and cons of each approach? Which one is the preferred approach?

Here are my examples:

First the PDO approach.

<?php

$username = 'root';
$password = '';
$connection = new PDO( 'mysql:host=localhost;dbname=pacificsky', $username, $password );

?>

and here is the mysqli_connect.

$db = mysqli_connect('localhost','root','','pacificsky');

The difference is that `mysqli_connect` is used for when coding in procedural and `new mysqli` is the oop way of using it. You should use PDO if you want to support more databases in the future as MySQLi will only support MySQL — IsThisJavascript, Oct 17 '17 at 08:50
PDO is way superior: [Usability problems of mysqli compared to PDO](https://phpdelusions.net/pdo/mysqli_comparison) — Your Common Sense, Oct 17 '17 at 08:50
@WillParky93 as a matter of fact, "supporting more databases" is not that easy with PDO, whereas it has more practical benefits — Your Common Sense, Oct 17 '17 at 09:13
But still,PDO does support other databases whereas MySQLi does not? So my comment is still valid. I do agree that PDO is more beneficial. Personally, I found using MySQLi a better intro into prepared statements than PDO but each to their own! @YourCommonSense — IsThisJavascript, Oct 17 '17 at 09:46
@WillParky93 you simply can't say that MySQLi is a better intro into prepared statements, given the f\act you cannot even fetch a familiar associative array from a prepared statement! — Your Common Sense, Oct 17 '17 at 09:59
@YourCommonSense of course you can! we have `get_result()` and `fetch_assoc()` :D. To be fair, I'm only recently getting into PDO for the 2nd attempt. Was far too difficult for me at the start. I'm just a noob :(. Gl on your project Patrick — IsThisJavascript, Oct 17 '17 at 10:01
@WillParky93 First, get_result gives you a resource object. But you cannot get an array from the **statement** object. Second, you don't have get_result() all the time - it can be absent — Your Common Sense, Oct 17 '17 at 10:04
@YourCommonSense Why is that? It comes with Mysqlnd driver so I'm not sure why some webhosts disable it. Is there some security issues? — IsThisJavascript, Oct 17 '17 at 10:08
@WillParky93 there are no security issues. But it's a fact that that you may find get_result() unavailable and that's annoying. And there is not one, not two but a whole lot of such nagging nuisances in mysqli prepared statements, making your experience similar to walking through a prickly bush. On the contrary, PDO is smooth and predictable. I can't believe you have any trouble with it. By the way, what is it? Basically PDO prepared statements are as simple as 1-2-3: prepare/execute/fetch — Your Common Sense, Oct 17 '17 at 10:40
@YourCommonSense Honestly, I struggled on the most simplistic of things; understanding what binding was about, fetching results, error logging, what you can/cannot prepare, what prepare even means. Because of the more steps in MySQLi, I felt like I understood it. I'm much more confident using the manual these days — IsThisJavascript, Oct 17 '17 at 10:49
@WillParky93 it's all very simple. Binding you don't need - just send your vars right to execute(); there are many ways to fetch but basic while($row = $stmt->fetch()) is quite close to old mysql_fetch_array; error logging you don't need again, PHP will do it for you; prepare means the same as in mysqli. I wrote a comprehensive [tutorial for PDO](https://phpdelusions.net/pdo), given your good understanding of mysqli, I am sure you will find PDO much easier to use yet more flexible — Your Common Sense, Oct 17 '17 at 10:56
@YourCommonSense great article thanks! Got it bookmarked and will happily share that out — IsThisJavascript, Oct 17 '17 at 11:36

Difference in connecting database PHP

0 Answers0