Output count result to html even if 0

[gamma1itman]

Hi,

 

I am trying to build a table with counted results.  My code works unless the count value is 0 (albeit there is likely a much cleaner way to do this and if you have time feel free to share your way) here is what I have:

 

How do I get a count of 0 if the query returns no results?

 

I should mention I get an empty table when the result is 0

<?php

// Conection Info
$con=mysqli_connect("localhost","*****","*****","*****");

// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();

// Querys

$c_nc_t1 = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t1_type_count FROM `Stats`
WHERE type='Type 1' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

$c_nc_t2d = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2d_type_count FROM `Stats`
WHERE type='Type 2 diet' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

$c_nc_t2a = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2a_type_count FROM `Stats`
WHERE type='Type 2 ADA' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

$c_nc_t2ai = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2ai_type_count FROM `Stats`
WHERE type='Type 2 ADA and Insulin' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

$c_nc_t2i = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2i_type_count FROM `Stats`
WHERE type='Type 2 Insulin' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

// Output to html

//Build Headers

echo "<center><h3>Report for date range:</h3></center>";
echo "<table border='1' align='center'>
<tr>
<th></th>
<th></th>
<th>Type 1</th>
<th>Type 2 Diet</th>
<th>Type 2 ADA</th>
<th>Type 2 ADA + Insulin</th>
<th>Type 2 Insulin</th>
</tr>";


//Build Data output to html


while($row_c_nc_t1=mysqli_fetch_array($c_nc_t1)
 and $row_c_nc_t2d=mysqli_fetch_array($c_nc_t2d)
 and $row_c_nc_t2a=mysqli_fetch_array($c_nc_t2a)
 and $row_c_nc_t2ai=mysqli_fetch_array($c_nc_t2ai)
 and $row_c_nc_t2i=mysqli_fetch_array($c_nc_t2i))
  {
  echo "<tr>";
  echo "<td>Caseload</td><td>New Clients</td>
<td><center>" . $row_c_nc_t1['c_nc_t1_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2d['c_nc_t2d_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2a['c_nc_t2a_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2ai['c_nc_t2ai_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2i['c_nc_t2i_type_count'] . "</center></td>
";
  echo "</tr>";
  }
echo "</table>";

// Close Connection
mysqli_close($con);
?>

Thank you in advance for your time and wisdom!

Edited June 17, 2013 by gamma1itman

[Zane]

Try using one query instead of five, especially since all of the queries you have are only different in that they have a different type.

• $typeCount = mysqli_query($con, "
• SELECT actcaseld, type, encdate, COUNT(*) AS thecount FROM `Stats`
• WHERE type IN ("Type 1", "Type 2 diet", "Type 2 ADA", "Type 2 ADA and Insulin", "Type 2 Insulin")
• AND actcaseld='New Client' AND encdate BETWEEN
• (SELECT bdate FROM `ReportRange` WHERE cf_id=1)
• AND
• (SELECT edate FROM `ReportRange` WHERE cf_id=1")
• GROUP BY type");

That should give you four rows, each with a thecount field of the count.

echo "<table border='1' align='center'>
<tr>
<th></th>
<th></th>
<th>Type 1</th>
<th>Type 2 Diet</th>
<th>Type 2 ADA</th>
<th>Type 2 ADA + Insulin</th>
<th>Type 2 Insulin</th>
</tr>\n";
echo "<tr>\n";
while( $row = mysqli_fetch_array($typecount) ) {
echo "<td><center>{$row['thecount']}</center></td>\n";
}
echo "</tr></table>";

[gamma1itman]

Thanks!!!

 

......I will let you know how it goes, I am just heading out but wanted to thank you for the speedy reply.

[gamma1itman]

Hi Zane,

 

Thanks for helping, I don't think this will not work for what I am trying to do. I may have been too vague.  All the data comes from 1 table except the date range for the where statement, it comes from its own table.  I need to display the zero if the count is less than 1.  Could I use some sort of an if then else statement.  

 

ie: $resulta=if $result<1 then="0" else=$result

 

The final table should look like the attached photo:

 

Thanks again for your assistance.

 

[gamma1itman]

The entire table display blanks when count value is 0 but displays all values if no counts are 0

 

I suspect it is the way i did this:

echo "<td>Caseload</td><td>New Clients</td>
<td><center>" . $row_c_nc_t1['c_nc_t1_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2d['c_nc_t2d_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2a['c_nc_t2a_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2ai['c_nc_t2ai_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2i['c_nc_t2i_type_count'] . "</center></td>
";

Could I use an if then else statement somehow here to echo zero if count is zero?  Or is this block of code stopping the other values from being echoed when the count is 0?

 

If I am leaving out any info pls let me know and accept my apologies in advance.

 

Thanks

[mac_gyver]

the query that Zane posted should give you one row for each type, with the type and thecount, even if the count is a zero.

 

what is your current code?

 

edit: additionally, if those 5 types are all the types in the table, you don't need to include them in the WHERE clause, but if you want to display them in the order shown, you need to add an ORDER BY clause that forces that order - ORDER BY FIELD(type,'Type 1', 'Type 2 diet', 'Type 2 ADA', 'Type 2 ADA and Insulin', 'Type 2 Insulin')

Edited June 18, 2013 by mac_gyver

[gamma1itman]

Hi Mac_Gyver,
 
Thanks for helping me.  My code when I try the way Zane suggested is :

<?php

// Define how to display/report errors
   ini_set("display_errors", "1");
   error_reporting(-1);

// Conection Info
$con=mysqli_connect("localhost","*****","*****","*****");

// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();

// Querys

$typeCount = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS thecount FROM `Stats`
WHERE type IN ('Type 1', 'Type 2 diet', 'Type 2 ADA', 'Type 2 ADA and Insulin', 'Type 2 Insulin', 'Pre-Diabetes', 'Other')
AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

// Output to html

//Build Headers

echo "<table border='1' align='center'>
<tr>
<th></th>
<th></th>
<th>Type 1</th>
<th>Type 2 Diet</th>
<th>Type 2 ADA</th>
<th>Type 2 ADA + Insulin</th>
<th>Type 2 Insulin</th>
<th>Pre-Diabetes</th>
<th>Other</th>
</tr>\n";


//Output Results

echo "<tr>\n";
while( $row = mysqli_fetch_array($typecount))
{
echo "<td><center>" . $row['thecount'] . "</center></td>\n";
}
echo "</tr></table>";


// Close Connection
mysqli_close($con);
?>

I am trying to recreate the attached spreadsheet , I have many more counts to echo, I don't think this will allow me to complete as there many different combinations of results to query, not just type to query.

 

This is the main table, the other table just holds the report date range

 
For the sake of a better understanding I have tried Zanes approach but  I get 

Notice: Undefined variable: typecount in /path/to/webroot/php/report3.php on line 46 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /path/to/webroot/php/report3.php on line 46

Which I understand is referencing this block of code

echo "<tr>\n";
while( $row = mysqli_fetch_array($typecount))
{
echo "<td><center>" . $row['thecount'] . "</center></td>\n";
}
echo "</tr></table>";

Which to my untrained eye appears okay but is failing.

Edited June 18, 2013 by gamma1itman

[gamma1itman]

For simplicity and to summarize my request, although I appreciate the other ways of querying the results, I would like to use the below code (Desired Code) to output the results to html even if the value is zero. The code below works as long as no counts = 0.  If any = 0 the count for all shows empty.

 

System info:

Database Version 	        5.1.69-0ubuntu0.10.04.1
Database Collation 	        utf8_general_ci
PHP Version 	                5.3.2-1ubuntu4.19
Web Server 	                Apache/2.2.14 (Ubuntu)
WebServer to PHP Interface 	apache2handler 

DB info

 

Report Info

 

Desired code (just need to echo "0" (Zero) when count is <1 and not loose all the other counts.

 

<?php
 
// Conection Info
$con=mysqli_connect("localhost","*****","*****","*****");
 
// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();
 
// Querys
 
$c_nc_t1 = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t1_type_count FROM `Stats`
WHERE type='Type 1' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");
 
$c_nc_t2d = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2d_type_count FROM `Stats`
WHERE type='Type 2 diet' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");
 
$c_nc_t2a = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2a_type_count FROM `Stats`
WHERE type='Type 2 ADA' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");
 
$c_nc_t2ai = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2ai_type_count FROM `Stats`
WHERE type='Type 2 ADA and Insulin' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");
 
$c_nc_t2i = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS c_nc_t2i_type_count FROM `Stats`
WHERE type='Type 2 Insulin' AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");
 
// Output to html
 
//Build Headers
 
echo "<center><h3>Report for date range:</h3></center>";
echo "<table border='1' align='center'>
<tr>
<th></th>
<th></th>
<th>Type 1</th>
<th>Type 2 Diet</th>
<th>Type 2 ADA</th>
<th>Type 2 ADA + Insulin</th>
<th>Type 2 Insulin</th>
</tr>";
 
 
//Build Data output to html
 
 
while($row_c_nc_t1=mysqli_fetch_array($c_nc_t1)
 and $row_c_nc_t2d=mysqli_fetch_array($c_nc_t2d)
 and $row_c_nc_t2a=mysqli_fetch_array($c_nc_t2a)
 and $row_c_nc_t2ai=mysqli_fetch_array($c_nc_t2ai)
 and $row_c_nc_t2i=mysqli_fetch_array($c_nc_t2i))
  {
  echo "<tr>";
  echo "<td>Caseload</td><td>New Clients</td>
<td><center>" . $row_c_nc_t1['c_nc_t1_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2d['c_nc_t2d_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2a['c_nc_t2a_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2ai['c_nc_t2ai_type_count'] . "</center></td>
<td><center>" . $row_c_nc_t2i['c_nc_t2i_type_count'] . "</center></td>
";
  echo "</tr>";
  }
echo "</table>";
 
// Close Connection
mysqli_close($con);
?>

HTML output if no zero for count values

 

HTML output with zero for count values

 

Thank you in advance for your time and Knowledge

[taquitosensei]

You could try this

 

$resulta=($result<1)?0:$result;

 

Hi Zane,

 

Thanks for helping, I don't think this will not work for what I am trying to do. I may have been too vague.  All the data comes from 1 table except the date range for the where statement, it comes from its own table.  I need to display the zero if the count is less than 1.  Could I use some sort of an if then else statement.  

 

ie: $resulta=if $result<1 then="0" else=$result

 

The final table should look like the attached photo:

 

Thanks again for your assistance.

[mac_gyver]

the issue is that if there are no rows for any type, there's nothing to group by. you need to use the list of types to define the output, then if there's none for any type, take an appropriate action. try this -

<?php

// Define how to display/report errors
ini_set("display_errors", "1");
error_reporting(-1);

// Connection Info
$con=mysqli_connect("localhost","*****","*****","*****");

// Check connection
if (mysqli_connect_errno())
    echo "Failed to connect to MySQL: " . mysqli_connect_error();

// Querys
$typeCount = mysqli_query($con, "
SELECT actcaseld, type, encdate, COUNT(type) AS thecount FROM `Stats`
WHERE type IN ('Type 1', 'Type 2 diet', 'Type 2 ADA', 'Type 2 ADA and Insulin', 'Type 2 Insulin', 'Pre-Diabetes', 'Other')
AND actcaseld='New Client' AND encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY type");

// pre-processes the data, storing the count using the type as an index/key
$data = array();
while($row = mysqli_fetch_assoc($typeCount)){
    $data[row['type']] = $row['thecount'];
}

// array of type values and the corresponding html table legends (in case the legend differs from the stored value)
$types = array('Type 1'=>'Type 1','Type 2 diet'=>'Type 2 Diet','Type 2 ADA'=>'Type 2 ADA',
 'Type 2 ADA and Insulin'=>'Type 2 ADA + Insulin','Type 2 Insulin'=>'Type 2 Insulin',
 'Pre-Diabetes'=>'Pre-Diabetes','Other'=>'Other');

// Output to html
//Build Headers
echo "<table border='1' align='center'>
<tr>
<th></th>
<th></th>\n";
foreach($types as $legend){ // dynamically produce the headings using the array of values
    echo "<th>$legend</th>\n";
}
echo "</tr>\n";

//Output Results
echo "<tr>\n";
foreach($types as $type=>$not_used){
    $count = isset($data[$type]) ? $data[$type] : 0; // get the count, default to zero if none
    echo "<td><center>$count</center></td>\n";
}
echo "</tr></table>";

// Close Connection
mysqli_close($con);
?>

the reason for suggesting ONE query is to make your code and query(ies) more efficient. it only takes slightly longer to run the one single query that gets all 7 results at once than it takes to run ONE of your previous queries and the overall amount of code is less as well.

Edited June 18, 2013 by mac_gyver

[Barand]

If you had normalized your data instead of storing the type descriptions in every record you would have had a table of type descriptions with ids.

 

IF you had this table you could LEFT JOIN to your data table ensuring all types were present even if there was no data for a type (ie your zero totals)

Edited June 19, 2013 by Barand

[Zane]

For the sake of a better understanding I have tried Zanes approach but  I get 

Notice: Undefined variable: typecount in /path/to/webroot/php/report3.php on line 46 Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given in /path/to/webroot/php/report3.php on line 46

Which I understand is referencing this block of code

echo "<tr>\n";
while( $row = mysqli_fetch_array($typecount))
{
echo "<td><center>" . $row['thecount'] . "</center></td>\n";
}
echo "</tr></table>";

$typecount should be $typeCount

 

variables are case-sensitive.

 

So, while( $row = mysqli_fetch_array($typeCount))

 

 

Barrands suggestion is the most ideal and most flexible solution.  It only requires though that you re-organize your table structure.  If you are willing to do that, then I would definitely go for it.  If you cannot, then reference my above solution.

Edited June 19, 2013 by Zane

[gamma1itman]

@ Zane

My Bad I should have noticed that (typeCount) THX.  

Once the typeCount was fixed the code worked but if a zero value then fileds shift from headers and that well as you can imagine that is an issue.  With that in mind, I am both  willing to change my tables and agree that Barands solution make the most sense.  A little work to do but well worth it.  I will update you once I have a chance to test.  Once again THX.

 

@ Barand

Thanks for the great suggestion.  My Project started out a little simpler but I realized that with the help of good folks like you guys & gals I could set the bar a little higher for myself.  I am looking forward to implementing your suggestion.

 

@taquitosensei

I am definetly going to try your suggestion if for no other reason than to better understand the use of if-then-else in PHP

 

@mac_guyver

I Agree, Thanks for the code, I will test it and let you know how it goes.

 

@Everyone who read my post and especially those who contributed.

THANK YOU and I will update this post with my findings and final code ASAP