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

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

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.

Tina OConnell http://writecodeonline.com/php/ is where I test PHP code. It is telling us that: Parse error: syntax error, unexpected ''$_POST[APPLICANT_TO_COMPLETE_' (T_CONSTANT_ENCAPSED_STRING) 15 The T indicates that it is a token. A list of tokens can be found at http://php.net/manual/en/tokens.php. Looking there, it shows that the error means that it found a string in an unexpected place Somewhere around "'$_POST[APPLICANT_ I think its the " or ' but I am by no means a pro. Try removing " and if no..... try replacing it and removing the ' Sorry I could not be more help.

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

If you want to develop in PHP and MySQL I would Install and learn Linux (free)......I recommend starting with Ubuntu and load it with Apache, PHP and MySql. The addition of phpmyadmin is also very nice. Gedit (the default text editor) will automatically color your code once it has been save as *.php There is also the Bluefish editor you can install (also free)

........I looked at your page but it seems to work fine, I answered a question, clicked next and was asked the next question. Is that not what it should do or are you trying to ask all the questions on a single page?

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.

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

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!

In Response to Barand.... Thx for the MYSQL statement....... From PHPmyadmin SQL Tab #1052 - Column 'cf_id' in where clause is ambiguous I understand this to mean that ID and/or Title exist in both tables - The fix was......instead of: SELECT gender, COUNT(*) as gender_count FROM dmc_stats JOIN ReportRange ON encdate BETWEEN bdate AND edate WHERE cf_id = 1 GROUP BY gender; I used SELECT gender, COUNT(*) as gender_count FROM `DMC_Stats` JOIN `ReportRange` ON encdate BETWEEN bdate AND edate WHERE ReportRange.cf_id = 1 GROUP BY gender; Must use the table name or alias name then column name like: Note this WHERE statement uses table.field instead of just field Of course you would need to see the tables to know that was going to be an issue so......... Great solution just the same.

AbraCadaver......You let me sleep! THANKS I went with option 2 because I am using Joomla with Chronoforms to populate the tables and then building the reports with php via sourcerer as articles. If I was coding from scratch I would have used the first option because I think it would be less repetitive coding. This is the single file used to produce the required output. <?php // Conection Info $con=mysqli_connect("localhost","******","*******","******"); // Check connection if (mysqli_connect_errno()) echo "Failed to connect to MySQL: " . mysqli_connect_error(); // Query $result = mysqli_query($con, " SELECT gender, encdate, COUNT(gender) AS gender_count FROM `DMC_Stats` WHERE encdate BETWEEN (SELECT bdate FROM `ReportRange` WHERE cf_id=1) AND (SELECT edate FROM `ReportRange` WHERE cf_id=1) GROUP BY gender"); // Output to html echo "<table border='1'> <tr> <th>Gender</th> <th>Total</th> </tr>"; while($row=mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['gender'] . "</td>"; echo "<td>" . $row['gender_count'] . "</td>"; echo "</tr>"; } echo "</table>"; // Close Connection mysqli_close($con); ?> Once Again, Many Thanks!!!

Awsome! Thanks for the speedy reply.....I will try this shortly and post my findings.

Hi, I am trying to produce a result that requires 2 seperate queries to get the values required to produce the result. It works if I hard code the BETWEEN date values but when I try to pull them from the `Reportrange` table no results are returned. I am using includes to set variables, open and close the connection. (also works if BETWEEN dates are hard coded) See below: The idea is that it can search the `DMCstats` table for a count of each distinct 'gender' value WHERE the 'encdate' matches the range set in `Reportrange` record#1(---->bdate and edate) I think the issue is with the way I am calling the variables $bdat , $edat for $result My apologies in advance if this post does not conform to this forums expectations, its my first post here, feel free to enlighten me! Thanks in advance to all those donating their time for the greater good! Open Connection Include: <?php //Conection Info $con=mysqli_connect("localhost","******","******","******"); // Check connection if (mysqli_connect_errno()) echo "Failed to connect to MySQL: " . mysqli_connect_error(); ?> Close Connection Include: <?php mysqli_close($con); ?> Date Variables include: <?php include 'openconnection.php'; $bdat=mysqli_query($con,"SELECT bdate, FROM `ReportRange` WHERE cf_id=1"); $edat=mysqli_query($con,"SELECT edate, FROM `ReportRange` WHERE cf_id=1"); include 'closeconnection.php'; ?> Gender Query: //Open Connection include 'openconnection.php'; //Set Variables include 'datevars.php'; //Query $result=mysqli_query($con,"SELECT gender,encdate, COUNT(gender) AS gender_count FROM `DMC_Stats` WHERE encdate BETWEEN " . $bdat . " AND " . $edat . " GROUP BY gender"); //Output to html echo "<table border='1'> <tr> <th>Gender</th> <th>Total</th> </tr>"; while($row=mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['gender'] . "</td>"; echo "<td>" . $row['gender_count'] . "</td>"; echo "</tr>"; } echo "</table>"; //Close connection include 'closeconnection.php'; ?> Regards