Jump to content

PDO version of num_rows?


Go to solution Solved by TapeGun007,

Recommended Posts

Yes, Barand made me convert... so I'm learning slowly... :)



if ($stmt->num_rows == 0) {

        echo "<p>No record(s) found.</p>";
    echo "<br>".$row['FirstName']."<br>";


I'm getting no records found.  So then I realize it's because (Barand made me convert) I'm on PDO not mySQLi.  So I read about rowCount, but then it states the following (Which is disturbing):


"If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications."



So what is the best alternative?  From googling, I'm getting like so many different pieces of code, I'm not sure what is the best method.  I just want to check to see if there are any values at all in the query.



Link to comment
Share on other sites

the universal method (no OOP pun intended) would be to fetch all the data from the query into a php array variable (see the PDO fetchAll() method), then you can test if the array is empty or not using the empty() function or how many rows are in the array using count() function.


doing this (fetching all the data into a variable) also serves to separate the database specific code (that knows how to query and fetch the data) from the presentation code (that uses the data.) it also eliminates the 'out of sync' errors should you ever be in a position of not fetching all the data that you queried for. this also tends to force you to finish with the result set from one query before going onto the next query.

Edited by mac_gyver
Link to comment
Share on other sites

This num_rows stuff has always been an anti-pattern, so the answer is to get rid of it altogether.


Just check $row: As you can see in the manual, the fetch methods return false when there are no more rows.

if ($row)    // you'll want a more meaningful variable name than "row"
    // echo the escaped(!) first name
    echo 'No records found.';
Link to comment
Share on other sites

Unless you are writing portable code that runs on pseudo databases for some reason, there isn't much of a concern. Don't misinterpret that warning. If you are writing specifically to mysql, it is going to work fine.


For select statements you can always do this, although it's lots of extra overhead:


SELECT count(*) as countof FROM ... WHERE etc.


This query will always return a result that contains your number of rows to be returned in the next query, even if that number is zero.


Of course you have to author these queries for every SELECT statement which few people want to do, but it's an alternative to doing the query and relying on the client result set feature that provides num_rows, or using Mac's idea. In cases of large datasets that need to be paginated, Mac's work around would be bad.

Link to comment
Share on other sites

  • Solution

@gizmola, Yes, I just read that actually on a website, but I tried this instead and it seems to work fine (more along the lines of what Jacques suggested).  I want to simply check for any data so it doesn't bother building out a table for nothing.



$stmt = $con->prepare($sql);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row){
        echo "<br>Not Empty<br>";
    echo "<br>Empty<br>";
Link to comment
Share on other sites

 How many are returned if any is irrelevant.

How can you say that when you have no knowledge of the application?


Let's say I want to list names in three columns and want the numbers in each column to be as near equal as possible. For example I want

        A   E   I
        B   F   J
        C   G   K
        D   H

and not

        A   F   K
        B   G
        C   H
        D   I
        E   J

I would be interested in your method of doing this without knowing how many there are.

Link to comment
Share on other sites

num_row is a php function() to get the total number of rows in a databse table. To achieve this in PDO, use the rowCount() function instead.


 $query = "SELECT * FROM database_table";
 $result = $db->query($query);
 $total = $result->rowCount();
Hope this works for you
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.