What Is Pdo & Why Should I Use It

Why use PDO over MySQL in PHP?

The mysql_* functions are no longer maintained and community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi.

If you cannot decide, this article should help you choose. Though, you should know, that PDO is able to work with different kinds of RDBMS, while MySQLi is made for a specific one.

In case you decide to go with PDO, it's recommended you follow this tutorial.

Why we use PDO::closeCursor() method/function in PDO PHP?

Some databases does not support to execute and fetch the result from the next query until the previous query has the un-fetched results.

Let suppose you are executing 2 queries, the first one is in the fetching state and is not completed yet and on the same moment you want the first query to be stop in the state where it is currently in, and want to move to next query so the PDO::closeCursor() is used between the two queries to pause the first query in the same state and begin the next one.

Remember: This method is not compatible with all the PDO drivers.

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.

Advantages of pdo over mysql_* or mysqli_*

It's not about supporting multiple databases with one app (although you can, with care). It's not about making it easy to change databases in the future (although it helps). It's about having one consistent, sensible interface to use regardless of database. Not only does that benefit programmers (by making their skills more broadly applicable) and projects (by making it easier for programmers to jump in), it also makes it a lot easier to create libraries that sit one layer above the data access layer. Perl has had the DBI for 20 years and it's been a decidedly good thing. PDO is a very similar concept (in fact it steals at least half of DBI's interface).

What PDO query should i use?

Let construct the query step by step. In order to get the top 10 user by most topic by community, you need to compute number of topic by each user on that community.

SELECT topic_by, COUNT(*) AS total_topic FROM topic 
WHERE topic_comm = :comm GROUP BY topic_by

Then, you want to get the top 10 user, you can use basic subquery and LIMIT. So the final query

SELECT * FROM (
  SELECT topic_by, COUNT(*) AS total_topic FROM topic 
  WHERE topic_comm = :comm GROUP BY topic_by
) AS t ORDER BY t.total_topic DESC LIMIT 10

PHP PDO vs normal mysql_connect

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

So, for larger or portable project PDO is preferable. Even zend framework use PDO.