Mysqli versus PDO

[mark.giles.14]

I need to rebuild my website from scratch as its using old mysql script in pages and based on xmb forum software as a logon system and to connect to my sports database pages.
Can someone suggest the best option from here to either update to mysqli or PDO.

So far I have just started with a basic login and member rank system and now need to update over 100 seperate pages and want to do it in the simplest and most effective way.

[cereal]

PDO supports many databases, where MySQLi is limited only to MySQL and his forks. MySQLi instead has a better support for MySQL features and it can be used also in procedural style.

For more information read the documentation & the comments:

• https://php.net/manual/en/mysqlinfo.api.choosing.php

[diafol]

mysqli and PDO are different and I wouldn't say one is better than the other, however, I do find PDO easier to work with. For instance, I don't have to tell PDO about the datatypes I'm using with bound parameters. And output binding is more flexible too (IMO).

I would suggest PDO for its compatibility. Some mysqli functions depend upon the mysqlnd driver, which is often absent from hosted sites. This means that certain fetch functions don't work - as I found out when updating from mysql -> mysqli. :(

I posted a workaround for the mysqli mysqlnd-dependent fetch_all() here:

http://www.daniweb.com/web-development/php/code/476623/ajaxed-linked-dropdowns-select-fields-for-volatile-data#post2082327

[iamthwee]

Personally, I would opt for a framework, something like codeigniter has active record which is even easier to read than mysqli or pdo

[diafol]

You'd use codeigniter just for using a db? Sounds extreme to me. Running more than a strightforward query can be problematic:

http://ellislab.com/codeigniter/user-guide/database/active_record.html

If you intend to write your own queries you can disable this class in your database config file, allowing the core database library and adapter to utilize fewer resources.

If needs be, you could always roll you own abstraction class, but PDO is so simple (IMO), it's usually unnecessary.

[mark.giles.14]

How would I convert my pages to PDO?.

[diafol]

pritaeas has some nice articles on PDO in the code snippets section of the PHP forum, e.g.

http://www.daniweb.com/web-development/php/code/462126/pdo-binding-example

Also the PDO section in the php.net manual is pretty good (IMO). And... there are many, many, many PDO tutorials out there.

But in general, you only need to worry out a few bits and bobs to do most of what you want...

This is a typical connection...

$dsn = 'mysql:dbname=daniweb;host=localhost';
$user = '#username#';
$password = '#password#';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$dbh is now the PDO object and you can run everything off this - but as a rule, we create a PDOStatement object to do most of what we need.

Running a prepared statement (if you do not have any variables/parameters to insert into the SQL, then you could use the simple query() method instead).

$stmt = $dbh->prepare("SELECT ... WHERE field7 = :input1 AND field3 = :input8");

This creates the PDOStatement ($stmt). You'll notice the weird looking values (:something) - these are just placeholders for anything that you need to input into the dynamic SQL. The beauty of these 'bound parameters' is that PDO takes care of any 'escaping' or 'sanitizing'. There's no need to enclose the parameter with any quotes either (unlike mysql queries).

There is also another way to write this type of query...

$stmt = $dbh->prepare("SELECT ... WHERE field7 = ? AND field3 = ?");

Here the parameters are anonymous or 'unlabelled' - but that's not weird either as we shall see...

For 'labelled' parameters, we can either bind parameters individually:

$stmt->bindValue(':input8', $myinput, PDO::PARAM_STR);

and give the datatype (optional), or we can add the whole lot as an array in the execute method...

$stmt->execute(array(':input8'=>$myinput, ':input1'=>$myotherinput));

If using the ? placeholders, then you can just do this...

$stmt->execute(array($myotherinput, $myinput));

but variables must be in the order as they are meant to appear in the query

Once you've executed, you can then fetch the data via a number of different methods. here's an example of getting all records...

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Now all records are in a multidimensional array that you can loop over with a foreach() or while() loop.

There's obviously more to it, but that's what the manual is for :)

[mark.giles.14]

Thanks but thats way over my head :)
Im really just a begginer at coding and are just using the webmatrix starter site as a base for my site connection and login.

My old coding that is way out of date as it is. Just learning to connect that using webmatrix is a problem to start with.

[diafol]

So you're going to use PDO?
If so, then the stuff I posted will be your bread and butter. So if it's going over your head, try reading around the subject - the more you read, the better the chance that one of those articles will give you an 'Eureka' moment.

mysqli has a similar syntax to PDO on the basic stuff

[mark.giles.14]

I would like to try, but think its beyond my capabilities as Im just a begginer. Would I have to rewrite the pages I already have etc the session and connection code ?.

[pritaeas]

I would like to try, but think its beyond my capabilities as Im just a begginer.

Non-sense. Once you try one of the examples, all other will be just like it. Just jump in.

Would I have to rewrite the pages I already have etc the session and connection code ?.

You need to rewrite all that is using mysql_ functions now.

[mark.giles.14]

So your suggesting change from mysqli to PDO instead?. Where would I start with a basic setup with a config and header connection script and user login system ?. I just restarted rebuilding now using the webmatrix starter site template.
I have some long and drawn out sql queries in some of my pages, would these be easily converted to PDO as well.

[pritaeas]

So your suggesting change from mysqli to PDO instead?

No. Learning curve for either is similar.

Where would I start with a basic setup with a config and header connection script

Check the code snippets page. As diafol mentioned, I've written basic examples for both MySQLi and PDO.