Check for query returning no rows


#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?)


#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.


#10

Try this:


#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.)


#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';
}

#17

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