Difference Between MySQL, MySQLi and Pdo

What is the difference between MySQLi and PDO?

PDO is an interface for accessing databases:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server. (source)

MySQLi is an extension for accessing MySQL databases:

The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. (source)

Which one you should use is primarily opinion-based and not particularly well suited for the Stack Overflow format.

PHP PDO and MySQLi

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.

mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.

PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

PHP: mysql v mysqli v pdo

The design of the mysql_query function is such that you've got to be careful to escape each and every bit of data you're injecting into it, and if you miss even one your entire application can be destroyed by an automatic SQL vulnerability exploit tool.

Both mysqli and PDO support placeholders which are required to ensure that your queries are safe from SQL injection bugs. Calling mysql_real_escape_string on everything is not only tedious, but error-prone, and that's where the problems arise.

The mysql functions are a product of the very early days of PHP and are significantly more limited than the new object-oriented features offered by both mysqli as an option, or PDO by design.

There's a number of very good reasons to use one of these two new interfaces, but the most important is that the mysql_query function is simply too hazardous to use in production code. With it you will always be one mistake away from some very serious problems.

There's a reason rips of databases full of passwords and credit card numbers keep showing up. Having an obvious SQL injection point makes it almost too easy to completely take over a site.

What is the difference between PDO and MySQLi prepared statements?

The difference is below:-

Mysqli is only for the MySQL database. PDO supports other database using the same functions.
Mysqli can be used in either an object-oriented style or a procedural style. PDO is always object-oriented.
Mysqli supports prepared statements with ? placeholders for parameters. PDO supports both ? placeholders and also named placeholders, like :columnName.
Mysqli requires that you use a function to bind each parameter value to the prepared statement. PDO also allows you to simply pass an array of parameter values as you execute the prepared statement.

What is the difference between mysql and mysqli in WAMP?

mysqli and PDO, both php APIs for accessing MySQL, are bundled into WAMP. There's nothing else to download. You should be good to go.

Good for you for noticing the deprecation of mysql!

If you're doing this project to learn about dbms technology in general, you might consider working with PDO: you can use the same APIs (with small variations) to connect to other dbmss, including PostgreSQL, Oracle, and MS SQL Server. Even though some of those are expensive commercial products, you can get free evaluation copies for learning purposes to run on your own machine.

Difference between mysqli and mysql?

Use MySQLi over the older MySQL functions. The "i" stands for "improved". The list of improvements can be found in the docs.

Which is faster for Large Data Sets? mysql, mysqli or PDO?

mysql shouldn't be used since it's outdated and has been superseded by mysqli. As for the differences between mysqli and PDO, consider these:

Reasons to use mysqli

Should be slightly faster.
Easier, especially if you're familiar with the mysql extension, since it is quite similar.

Reasons to use PDO

Much easier to transition to other databases.
More features (eg. named parameters in prepared statements, object mapping).