Jump to content

gamma1itman

Members
 View Profile  See their activity

  • Posts

    14

  • Joined

  • Last visited

 Content Type 

Posts posted by gamma1itman

    Output count result to html even if 0

    in PHP Coding Help

    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: 

    Output count result to html even if 0

    in PHP Coding Help

    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

    Output count result to html even if 0

    in PHP Coding Help

    Posted · Edited by 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 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.

    Help, Pretty Please stuck

    in PHP Coding Help

    Posted

    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.

    Output count result to html even if 0

    in PHP Coding Help

    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:

    Help, Pretty Please stuck

    in PHP Coding Help

    Posted · Edited by gamma1itman

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

     

    There is also the Bluefish editor you can install (also free)

    Output count result to html even if 0

    in PHP Coding Help

    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

    Output count result to html even if 0

    in PHP Coding Help

    Posted · Edited by 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!

    PHP Mysqli set variables from 2 different tables to use in result query

    in PHP Coding Help

    Posted

    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.  

    PHP Mysqli set variables from 2 different tables to use in result query

    in PHP Coding Help

    Posted

    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!!!

    PHP Mysqli set variables from 2 different tables to use in result query

    in PHP Coding Help

    Posted · Edited by gamma1itman

    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:
    post-157986-0-84908100-1371059312_thumb.png

     

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

     

    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

×
×
  • 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.