PDO version of num_rows?

[TapeGun007]

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

 

 

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

        echo "<p>No record(s) found.</p>";
}else{
    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.

 

 

[mac_gyver]

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 February 24, 2017 by mac_gyver

[Jacques1]

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
}
else
{
    echo 'No records found.';
}

[gizmola]

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.

[TapeGun007]

@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);
$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
 
if ($row){
        echo "<br>Not Empty<br>";
}else{
    echo "<br>Empty<br>";
}

[benanamen]

What @Jaques1 showed you is the way to do it. You are wanting to know if there are results returned or not, true or false. How many are returned if any is irrelevant.

[Barand]

 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.

[Jacques1]

If you want to discuss your own problems, create a new thread.

[benanamen]

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

 

The OP said enough right here

 

I just want to check to see if there are any values at all in the query.

 

In that case, @Jaques1 and I are correct.

[kessie]

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

[Jacques1]

Guys, please read the question. The OP knows rowCount(). He mentioned it right in the first post. What he actually wanted was an existence check, and he already got that (see replies #3 and #5).

 

So this problem is solved. You can stop replying now.