Check for query returning no rows

PHP
1

I have the following SQLite query, but I’m not sure how to check for no rows being returned, other than by doing another query. Can anyone help?

$query = 'SELECT
    id, name, email, comment, datetime, ipaddress, approved
    FROM guestbook
    WHERE approved=0
    ORDER BY id DESC;';
$result = $db->query($query);
2

i don’t do php, but i’m certain there’s a php function to tell you how many rows a query returned

3

I guess it might be possible to add to the SELECT eg.
COUNT(id) AS 'rows'

But as r937 posted counting the result rows with PHP should be doable. Is there a reason that won’t work for your use case?

4

If I

var_dump($result);

it simply returns an object with the query string.

5

What is $result? A PDO object?

$result->rowCount()

EDIT: rowCount is a function, not a property.

6

If you mean you’ve invoked the SQLite3 object type, it would be a loop:

$i = 0;
$result->reset(); //Make sure we're at the start.
while($result->fetchArray()) {
 $i++;
}
$result->reset(); //Return to start state.

(Can we see why PDO is preferred yet?)

1 Like
7

http://php.net/manual/en/function.mysql-query.php

From the mysql_query manual;

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

Edit:

http://php.net/manual/en/pdostatement.rowcount.php

8

Yes, I’m using PDO.

Seems to return 0 even when there are 4 rows returned. :upside_down_face:

Gives me:
Fatal error : Uncaught Error: Call to undefined method PDOStatement::reset()

9

mysql_* isn’t available in PHP7.

1 Like
10

Try this:

1 Like
11

That means the SQLite driver isnt implementing the number of affected rows, and you’ll have to resort to the more draconian loop-and-count. (Or fetchall and size the array. Same thing.)

1 Like
12

Thanks, fetchall does the job without much extra code.

16

fetchAll example
(* The two if’s are just examples. You wouldn’t code it that way.)


$query = 'SELECT * FROM guestbook WHERE approved = 0;';
$result = $db->query($query);
$data = $result->fetchAll();

if($data ){
    echo 'There are results';
}
if(!$data ){
    echo 'There are no results';
}
1 Like
17

Thanks, that’s more efficient than what I had!

18

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.