CODEWITHSUNDEEP
phpmysqlcount
T.C
T.C

Reputation: 311

How to count a column in database and if empty return 0 PHP

I have a table where I want to count total entries in one column. But if the entire column is empty, I want the count to return 0 (meaning there are zero entries)

This is what I tried, but when I use echo it returns blank. In the database it is blank, but I want it to return 0 when column recall is empty.

CODE:

$chartsql = "SELECT recall from report where child_id='$childId'"; 
$ChartRes = mysqli_query($con,$chartsql);

while ($ChartRow=mysqli_fetch_array($ChartRes)){
    $recall[] = $ChartRow['recall1'];
}

foreach($recall as $index=>$value) {
    if($value === null) unset($recall[$index]);
}
$count_recall = count($recall);

if($count_recall = ''){
    $count_recall1 = 0;

}

echo $count_recall;

Also, in the recall column, there are null entries as well as blank entries. So I want to ignore the nulls, but if all other entries are blank then it should return zero. If there are some blank and some valid entries, then it should only count the valid entries and not what is blank.

It should only return 0 if it is completely empty, ignoring nulls.

Upvotes: 1

Views: 2445

Answers (4)

ICE
ICE

Reputation: 1737

Use SQL count function instead:

$chartsql = "SELECT count(child_id) as totalitems from report where child_id='$childId' and recall is not null and recall != ''";
$ChartRes = mysqli_query($con,$chartsql);
$ChartRow = mysqli_fetch_array($ChartRes);
$count_recall = $ChartRow['totalitems'];

Never use PHP for counting, when you can use SQL for your result, because:

  • When you have many rows, you will have performance issue.
  • You can easily do it inside your query and have cleaner codes.
  • Debugging is much easier.

Upvotes: 1

Dice
Dice

Reputation: 233

<?php
  $chartsql = 
  "SELECT (count(id) - (
       SELECT count(id) 
       FROM report 
       WHERE attributeName LIKE ''))
   FROM report"; 
?>

All you need to do is substract the total of rows to the amout of empty rows. It should do the job

Upvotes: 0

ino
ino

Reputation: 2581

In PHP you could simplify it by:

assign only valid value to $recall array

add zero to returned value from count() to make it an integer

<?php
$chartsql = "SELECT recall from report where child_id='$childId'"; 
$ChartRes = mysqli_query($con,$chartsql);

while ($ChartRow=mysqli_fetch_array($ChartRes)){
    if(!is_null($ChartRow['recall1'])){
        $recall[] = $ChartRow['recall1'];
    }
}

$count_recall = count($recall) + 0;

echo $count_recall;

Upvotes: -1

Eriks Klotins
Eriks Klotins

Reputation: 4180

How about count(*) https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html

$chartsql = "SELECT count(*) from report where child_id='$childId'" and recall is not null and recall != ''";

Upvotes: 1

Related Questions