gamma1itman Posted June 17, 2013 Share Posted June 17, 2013 (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 June 17, 2013 by gamma1itman Quote Link to comment Share on other sites More sharing options...
Zane Posted June 17, 2013 Share Posted June 17, 2013 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>"; Quote Link to comment Share on other sites More sharing options...
gamma1itman Posted June 17, 2013 Author Share Posted June 17, 2013 Thanks!!! ......I will let you know how it goes, I am just heading out but wanted to thank you for the speedy reply. Quote Link to comment Share on other sites More sharing options...
gamma1itman Posted June 18, 2013 Author Share Posted June 18, 2013 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. Quote Link to comment Share on other sites More sharing options...
gamma1itman Posted June 18, 2013 Author Share Posted June 18, 2013 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 18, 2013 Share Posted June 18, 2013 (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 June 18, 2013 by mac_gyver Quote Link to comment Share on other sites More sharing options...
gamma1itman Posted June 18, 2013 Author Share Posted June 18, 2013 (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 , 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 Quote Link to comment Share on other sites More sharing options...
gamma1itman Posted June 18, 2013 Author Share Posted June 18, 2013 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 Quote Link to comment Share on other sites More sharing options...
taquitosensei Posted June 18, 2013 Share Posted June 18, 2013 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 18, 2013 Share Posted June 18, 2013 (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 June 18, 2013 by mac_gyver Quote Link to comment Share on other sites More sharing options...
Barand Posted June 19, 2013 Share Posted June 19, 2013 (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 June 19, 2013 by Barand Quote Link to comment Share on other sites More sharing options...
Zane Posted June 19, 2013 Share Posted June 19, 2013 (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 June 19, 2013 by Zane Quote Link to comment Share on other sites More sharing options...
gamma1itman Posted June 19, 2013 Author Share Posted June 19, 2013 @ 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.