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

Question

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.

ICE · Accepted Answer · 2018-12-01 14:30:18Z

1

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.

answered Dec 1, 2018 at 14:30

ICE

1,7773 gold badges23 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eriks Klotins · Accepted Answer · 2018-11-30 21:15:39Z

1

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 != ''";

answered Nov 30, 2018 at 21:15

Eriks Klotins

4,1701 gold badge14 silver badges27 bronze badges

5 Comments

T.C Over a year ago

This is returning 1 when I echo count(recall); Is there a way to get it to return 0 since it is blank?

ICE Over a year ago

@TaahiraCarter Because you are counting how many rows returned from that query not the real value of the count(*). you don't need those php codes anymore after this query. you have the total items count in $ChartRow['count(*)']. I recommend to use SELECT count(child_id) as totalitems and get it with $ChartRow['totalitems'].

T.C Over a year ago

@ICE But I need to know how many rows have a value in it. So if column "recall" has 5 rows and 3 rows have a value but 2 rows are blank, then $count_recall value should be 3. Because only 3 of the rows have a value. And if all 5 rows in column "recall" are blank, then I need it to return 0. This is important as I need to use this value in a math equation.

ICE Over a year ago

@TaahiraCarter He added and recall is not null and recall != ''"; to the query just for that. you can do that with IS NOT NULL in mysql.

T.C Over a year ago

@ICE But when I tried that it kept returning blank. Even if 5 rows had a value it kept returning blank when it should return 5. and if all were empty it returned blank when it should return 0

Dice · Accepted Answer · 2018-12-01 01:48:21Z

0

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

answered Dec 1, 2018 at 1:48

Dice

2351 silver badge9 bronze badges

Comments

ino · Accepted Answer · 2018-11-30 21:24:09Z

-1

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;

edited Nov 30, 2018 at 21:24

answered Nov 30, 2018 at 21:18

ino

2,6811 gold badge20 silver badges33 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related