Solved

PHP question

Posted on 2013-12-25
13
466 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 110

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

734 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