PHP question

I'm learning PHP and have a basic question.

When I access a MySQL database like this:

$dsn = "mysql:host=localhost;dbname=myDB";
$db = new PDO($dsn, 'username', 'password');
$products = $db->query( 'SELECT * FROM products WHERE categoryID = 1 ORDER BY productID');

How do I get the total number of records returned from the query?
elepilAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
Companies expect you to be able to use any and/or all of it.  PDO and mysqli are the current drivers for MySQL.
0
 
Dave BaldwinFixer of ProblemsCommented:
"rowCount" or "fetchColumn" can be used.  See here: http://www.php.net/manual/en/pdostatement.rowcount.php
0
 
OddHenriksenCommented:
rowCount will not provide the actual number of rows in a query, because of the way PDO works. Your most reliable option would likely be to explicitly query the number of rows (using the SQL function COUNT) and fetch that value using e.g. fetchColumn.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
elepilAuthor Commented:
OddHenriksen, you're right about rowCount.

I was disappointed to see the sample code in the documentation that it has to call two queries -- one to get the count, and the other to get the result set, like you described. This is clumsy!

With the length of time PHP has been out, I expected more maturity, like maybe a method in the PDO object. Is this the only way to get the record count of a PDO result set??
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
The problem with PDO is that it is a front end for the actual drivers for many different databases.  Sometimes it's methods are not as direct as the ones in the actual drivers.  The 'mysql' and 'mysqli' drivers return 'num_rows' for a 'select'.  See here: http://www.php.net/manual/en/mysqli-result.num-rows.php
0
 
elepilAuthor Commented:
Dave Baldwin,

What circumstances would you use PDO? I used to be a Java developer, and in Java, databases came with their own drivers and Java would take care of interfacing JDBC with the database driver; in this way, programmers only need to learn one API. I'm getting the notion that PHP is not that way. Having to call a specific COUNT() query to get the number of records is laughable.

I just wanted to make sure I don't end up picking something deprecated. So for database access purposes, would you say mysqli would be the best way to go? Any caveats I need to be aware of?

Thanks.

P.S. I'm going by the book "Murach's PHP and MySQL", I can't believe he's teaching a clumsy version of database access.
0
 
OddHenriksenCommented:
There is almost always more than one way. However, with PDO, the method previously described is the most reliable and least problematic method I am personally aware of.

I agree that it feels clumsy, and I certainly share that frustration.
0
 
Dave BaldwinFixer of ProblemsCommented:
I don't use PDO though I know it's recommended by a number of experts.  But I don't use stored procedures either.  I am slowly changing from the 'mysql' to the 'mysqli' driver.
0
 
elepilAuthor Commented:
One last question. What do most companies who hire PHP programmers use? PDO or mysqli?

Thanks.
0
 
elepilAuthor Commented:
Thank you for all your responses, I learned things from our discourse.
0
 
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
0
 
elepilAuthor Commented:
Oh by the way, I found a workaround on having to call an extra query just to get the record count. Let me put the code I originally have:

$dsn = "mysql:host=localhost;dbname=myDB";
$db = new PDO($dsn, 'username', 'password');
$products = $db->query( 'SELECT * FROM products WHERE categoryID = 1 ORDER BY productID');

Here's the workaround:

$results = $products->fetchAll();
               
echo 'Number of Records: '.count($results).'<p />';

So by invoking 'fetchAll()' and storing the results in the $results array, I can now get the record count with the count() function, and I can also loop through the data in the $results array with the foreach loop construct. :) The result is only one query was made instead of two.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.