HELP ! How to detect that a sql query has no results

PHP
1

Hi, I am still learning php and want to detect if a sql query has no results. I tried three different ways but still warnings are displayed. I would be glad if you can give me tips how to improve the below stated code so that no warnings are displayed.

CASE A: if(!array_filter($result)) … do sth
-> delivers Warning: array_filter() expects parameter 1 to be array, object given in /Users/website/index.php on line 79. Is $result no array? I think it is.

CASE B: if(!$result) … do sth.
-> has no impacts at all. Why?

CASE C: if(mysql_num_rows($result)==0) … do sth.
delivers warning: Warning: mysql_num_rows() expects parameter 1 to be resource, object given in /Users/website/index.php on line 82. What does this meas?

Here is the code and warnings which are displayed:

try
{
$sql = ‘SELECT p2gtext FROM p2g’;
$result = $pdo->query($sql);
}
catch (PDOException $e)
{
$error = 'Error fetching entries: ’ . $e->getMessage();
include ‘error.html.php’;
exit();
}

// if(!array_filter($result)) // CASE A

// if(!$result) // CASE B

if(mysql_num_rows($result)==0) // CASE C

{
$output = 'Database contains no entries ';
include ‘output.html.php’;
exit();
}
else
{

foreach ($result as $row)
{
$ps2g[] = $row['p2gtext'];
}

}
include ‘p2g.html.php’;

2

Hi,

As you’re using PDO, you can’t use the mysql_* or mysqli_ functions to count the returned rows.

There are a couple of ways that you can check for an empty result set. If you’re just wanting to get a single row, call the fetch method and then check to see if the return value is empty:


$sql = 'SELECT p2gtext FROM p2g';
$result = $pdo->query($sql);

$row = $result->fetch();

if ( ! $row) {
    $output = 'Database contains no entries ';
    include 'output.html.php';
    exit();
}

or, for result sets that should return multiple rows, you could use the columnCount method:


$sql = 'SELECT p2gtext FROM p2g';
$result = $pdo->query($sql);

if ($result->columnCount() === 0) {
    $output = 'Database contains no entries ';
    include 'output.html.php';
    exit();
}

while ($row = $result->fetch()) {
// etc..
3

Hi freburner, first of all thanks a lot for your quick answer. Unfortunately I couldn’t fix the problem only partly because

  1. If I use the fetch solution it works, but afterwards if I enter more and more entries to the database, always the first entry will never be shown. Do you know why this is the case? Maybe it has something to do because I then apply the fetch method twice to $result (when using the foreach function (code above)?

  2. If I use the columnCount solution it did not work because I think I found out that the columnCount of $result is always “1”. So maybe the columnCount is stored in another variable?

Would be glad if you can think about it again.

Thanks

4

Sure, as I pointed out, it’s better for when you’re only wanting to return a single record. You retrieve the row and check if it’s empty… if not you just use $row:


$sql = 'SELECT p2gtext FROM p2g'; 
$result = $pdo->query($sql); 

// Retrieve the row (will return an empty array if no records match)
$row = $result->fetch(); 

// If the row is empty, show your error msg..
if ( ! $row) { 
    $output = 'Database contains no entries '; 
    include 'output.html.php'; 
    exit(); 
}

// otherwise display the result.
echo $row['p2gtext'];

Hmm yeah, sorry about that… the docs lead me to believe that columnCount should return 0 for an empty result set, but I’ve tested it and, as you say, that doesn’t seem to be the case. You could try using the rowCount method instead. The manual warns that it doesn’t with all databases for SELECT queries, but I just tested it with MySQL on my machine and it works, so it’s worth a try.

5

Hi there, thanks for taking time to look it again. rowCount works. Thanks also for all the detailed explanation which helped a lot to understand the background better !