Solved

PHP question

Posted on 2013-12-25
13
463 Views
Last Modified: 2013-12-26
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?
0
Comment
Question by:elepil
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39739448
"rowCount" or "fetchColumn" can be used.  See here: http://www.php.net/manual/en/pdostatement.rowcount.php
0
 

Expert Comment

by:OddHenriksen
ID: 39739452
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
 

Author Comment

by:elepil
ID: 39739456
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 500 total points
ID: 39739464
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
 

Author Comment

by:elepil
ID: 39739480
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
 

Expert Comment

by:OddHenriksen
ID: 39739497
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39739546
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
 

Author Comment

by:elepil
ID: 39739547
One last question. What do most companies who hire PHP programmers use? PDO or mysqli?

Thanks.
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 39739551
Companies expect you to be able to use any and/or all of it.  PDO and mysqli are the current drivers for MySQL.
0
 

Author Closing Comment

by:elepil
ID: 39739669
Thank you for all your responses, I learned things from our discourse.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39739703
You're welcome, glad to help.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39740439
0
 

Author Comment

by:elepil
ID: 39740590
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question