$query = "SELECT * FROM `table`";
$results = mysql_query($query, $connection);
If 'table' has no rows. whats the easiest way to check for this.?
5 Answers
Jeremy Ruten's answer above is good and executes quickly; on the other hand, it only gives you the number of rows and nothing else (if you want the result data, you have to query the database again). What I use:
// only ask for the columns that interest you (SELECT * can slow down the query)
$query = "SELECT some_column, some_other_column, yet_another_column FROM `table`";
$results = mysql_query($query, $connection);
$numResults = mysql_num_rows($results);
if ($numResults > 0) {
// there are some results, retrieve them normally (e.g. with mysql_fetch_assoc())
} else {
// no data from query, react accordingly
}
answered Oct 18, 2008 at 13:48
Piskvor left the building
93.1k46 gold badges182 silver badges226 bronze badges
Comments
You could use mysql_num_rows($results) to check if 0 rows were returned, or use this faster alternative:
$query = "SELECT COUNT(*) AS total FROM table";
$results = mysql_query($query, $connection);
$values = mysql_fetch_assoc($results);
$num_rows = $values['total'];
3 Comments
Barry Brown
If you want to get data from the table, but just want to know if it's empty so that you can say so on the results page, then do your normal query and use mysql_num_rows. What you don't want to do is: count the rows (using the above), then do the normal query if count > 0.
Paige Ruten
Yeah, I wasn't thinking of that because I usually do the "SELECT COUNT(*)" query to calculate the number of pages of content, then SELECT just a few rows from the table depending on what page of content the user is on.
Omar Tariq
mysql_query and mysql_fetch_assoc extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. php.net/manual/en/mysqli-result.fetch-assoc.phpAlternatively you can simply check if the result of mysql_fetch_assoc is false.
$query = "SELECT * FROM `table`";
$results = mysql_query($query, $connection);
$Row = mysql_fetch_assoc($results);
if ($Row == false)
{
$Msg = 'Table is empty';
}
3 Comments
The One and Only ChemistryBlob
In my opinion the best answer since all that needs to be done is check if mysql_fetch_assoc of the query produces something that is not null
Jason 'Slingshot' Miller
why do you use
mysql_fetch_assoc instead of $Row = $result->fetch_assoc(); any difference?
Toby Allen
This is a very old post. I now use Laravel. :)
One thing i noticed that was missed was the fact that the query might not succeed, so you do need to check if the $results variable is set. I'll use the answer given by yjerem as an example.
$query = "SELECT COUNT(*) AS total FROM table";
$results = mysql_query($query, $connection);
if ($results) { // or use isset($results)
$values = mysql_fetch_assoc($results);
$num_rows = $values['total'];
}
Comments
If you loop through the results, you can have a counter and check that.
$x = 1;
$query = mysql_query("SELECT * FROM table");
while($row = mysql_fetch_assoc($query))
{
$x++;
}
if($x == 1)
{
//No rows
}
1 Comment
nickf
no it wouldn't. array("") == true. This is still a very silly way to check it though.
if (!isset($result)) { // nothing has been returned }.