Solved

PHP question

Posted on 2013-12-25
13
468 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
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 
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
 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

626 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