Code To Count Non Zero Records

Looking For Code To Count Non Zero Records

[jhcadmin]

I am a newbie to php.

I have a database that has a particular field (herein referred to as field3) that frequently contains zeros [0.00 (DOUBLE 9,2)]. The dilemma is that when I display the data in tablular format, I total all the columns and I need a count of the records where that field is positive so I can get an average of the populated fields only. I have tried to nest the following code into my current php page, but it doesn't work:

<?php
$query = SELECT COUNT field3 FROM table WHERE field3 >= 1;
$result = mysql_query($query);
list($count) = $result;
?>

Any assistance would be greatly appreciated.

[dreamcatcher]

Why not just use SUM?

$query = SELECT SUM(field3) as total_count FROM table;
$row = mysql_fetch_object($query);

echo $row->total_count;

Hope that helps.

dc

[jhcadmin]

I don't want to add the values of the field together. I need a count of records where that field is not zero.

[dreamcatcher]

Sorry.

This should be ok:

$query = SELECT COUNT(*) as total_count FROM table WHERE field3!= '' AND field3!= NULL;

$row = mysql_fetch_object($query);

echo $row->total_count;

[jhcadmin]

I tried that:

<?php $query = SELECT COUNT(*) as contract_count FROM contracts WHERE contract_amt!= '' AND contract_amt!= NULL; $row = mysql_fetch_object($query); echo $row->contract_count;?>

But all I keep getting is the following error:

Parse error: parse error in
D:\hshome\myadminuser\mydomain.com\contracts\contractslist.php on line 577

I do appreciate your input on this problem.

[SeanW]

You'll have to quote your string, ie

$query = "SELECT blah...";

Also, "foo!= null" doesn't do what you want, it'll never match since null can't be compared. You want "foo is not null".

As an example:

mysql> select Count(*) from item where isbn!= null; 
+----------+ 
¦ Count(*) ¦ 
+----------+ 
¦ 0  ¦ 
+----------+ 
1 row in set (22.72 sec) 
  
mysql> select Count(*) from item where isbn is not null; 
+----------+ 
¦ Count(*) ¦ 
+----------+ 
¦ 47213 ¦ 
+----------+ 
1 row in set (0.19 sec) 

[jhcadmin]

All I get is this message:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in...

I am SOOOO frustrated!

<?php $query = "SELECT COUNT(*) as contract_count FROM contracts WHERE contract_amt is not NULL"; $row = mysql_fetch_object($query); echo $row->contract_count;?>

[dreamcatcher]

I think the is not NULL should be uppercase:

$query = "SELECT COUNT(*) as contract_count FROM contracts WHERE contract_amt IS NOT NULL";

Try that.

[SeanW]

The case shouldn't matter. I think the problem is that you haven't executed the query before retrieving the rows.

Check out the example code in [ca.php.net...]

You have to do something like

$row = mysql_query($query);

and then mysql_fetch_object($row)

Sean