MIN returning 1 on empty table

[ricmetal]

hi guys

so im getting 1 result row from an empty table, when querying using min...

i dont know what this should be...

 

this is my code:

$theTrout = 1;
$stmt = $mysqli->prepare("SELECT MIN(news_date_year) FROM news WHERE news_published = ? AND user_published = ? ");
$stmt->bind_param('ii', $theTrout, $theTrout);
$stmt->execute();
$stmt->bind_result($oldestYear);
$stmt->store_result();
echo $stmt->num_rows; // echoes 1

anyone know why? thanks

cheers

regards

[Barand]

because there is 1 row returned containing the value of the min date

[ricmetal]

okay but this is a quirk sql has, right? i mean, i should be getting 0 rows returned (seeing the table is empty)

Edited August 1, 2013 by ricmetal

[DavidAM]

Not really a "quirk". When you SELECT an aggregate, you will always get a row. If the table is empty, or no rows match the WHERE clause, the "MIN" value is NULL. You need to check the value that is returned:

if (empty($oldestYear)) ...

 

Or you can add COUNT(*) to the query and see how many rows matched the WHERE clause (zero if the table is empty). Even in that case, you will get 1 row returned.