Jump to content

Output count result to html even if 0

gamma1itman

By gamma1itman
in PHP Coding Help

 Share

Recommended Posts

gamma1itman Newbie

gamma1itman

Posted
Posted (edited)

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 by gamma1itman
Link to comment
Share on other sites
Zane Prolific Member

Zane

Posted
Posted

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>";
Link to comment
Share on other sites
gamma1itman Newbie

gamma1itman

Posted
  • Author
Posted

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.

 

post-157986-0-32667300-1371564007_thumb.png

Link to comment
Share on other sites
gamma1itman Newbie

gamma1itman

Posted
  • Author
Posted

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 :happy-04:

Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)

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 by mac_gyver
Link to comment
Share on other sites
gamma1itman Newbie

gamma1itman

Posted
  • Author
Posted (edited)

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 post-157986-0-32667300-1371564007_thumb.png, 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

post-157986-0-63189500-1371585462_thumb.png
 
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 by gamma1itman
Link to comment
Share on other sites
gamma1itman Newbie

gamma1itman

Posted
  • Author
Posted

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

post-157986-0-63189500-1371585462_thumb.png
 
Report Info
post-157986-0-32667300-1371564007_thumb.png
 
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

post-157986-0-57978600-1371587530_thumb.png

 

HTML output with zero for count values

post-157986-0-60702800-1371587539_thumb.png

 

Thank you in advance for your time and Knowledge

Link to comment
Share on other sites
taquitosensei Regular Member

taquitosensei

Posted
Posted

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.

Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted (edited)

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 by mac_gyver
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

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 by Barand
Link to comment
Share on other sites
Zane Prolific Member

Zane

Posted
Posted (edited)

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 by Zane
Link to comment
Share on other sites
gamma1itman Newbie

gamma1itman

Posted
  • Author
Posted

@ 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

 

::)  :happy-04:  ::)  :happy-04:  ::)  :happy-04:  ::)  :happy-04: 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.