Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PHP question

Posted on 2013-12-25
13
Medium Priority
?
471 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 84

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 2000 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 84

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 84

Accepted Solution

by:
Dave Baldwin earned 2000 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 84

Expert Comment

by:Dave Baldwin
ID: 39739703
You're welcome, glad to help.
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

886 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