counting and looping

[peter_b]

Hi everyone,

I am trying to count data from an array but seem to be having a problem. What I have is an array with 2 columns, year and response sorted by year.

The data looks like:
1960 yes
1960 yes
1960 no
1960 pending
etc... with multiple years and responses.

I have written the following (sorry for the format newbie):

$new_year = "1950";
$cnt = 0;

test_db_connect()
   or die ("Cannot connect to server");

$result1 = mysql_query ("select year, response from test 
where response = 'yes' order by year")
    or exit ();

if ($result1 <= 0)
echo("The query has failed probably due to lack of data!");

$numrows1 = mysql_num_rows($result1);
if ($numrows1 <= 0)
  echo("No results found matching your query...");

 while ($row = mysql_fetch_array($result1))
{

for ($i="1960"; $i <= "1965"; $i++)
{
if($row[year] = $i)
    $cnt = $cnt +1;
                echo $row[fn04];echo $cnt;echo "<br />";

if($row[fn04] > $i)
    break;
}

} 

All I get is the year and a sequential number count???

What I should be getting is something like this:
1960 total yes = 4
1960 total pending = 6
1960 total no = 8

1961 total yes = 4
1961 total pending = 6
1961 total no = 8

Thanks for your help.

[Weedpacket]

Please clarify: what do you want it to do?

[kula]

Your information is not clear, However I guess
You have two filed data which are year and response.
Try this way. I do not think this is a good solution
You may can get some idea

// this is for respone=yes

for($i=1960; $i<1965; $i++)
{
$count=0;
$result1= mysql_query ("select * from vendors where year =$i and response=’yes’ “);

while ($row = mysql_fetch_array($result1)) {
$count=$count + 1;
}
echo “yes”;
echo $i; echo $count ;

// this is for respone=no

for($i=1960; $i<1965; $i++)
{
$count=0;
$result1= mysql_query ("select * from vendors where year =$i and response=’no’ “);

while ($row = mysql_fetch_array($result1)) {
$count=$count + 1;
}
echo”no <br>”;
echo $i; echo $count ;

// this is for respone=pending

for($i=1960; $i<1965; $i++)
{
$count=0;
$result1= mysql_query ("select * from vendors where year =$i and response=’pending’ “);

while ($row = mysql_fetch_array($result1)) {
$count=$count + 1;
}
echo”pending <br>” ;
echo $i; echo $count ;

good luck

[hand]

I didn't read all details, but why not easier?

mysql> select * from vendors;
+------+----------+
| year | response |
+------+----------+
| 1960 | yes      |
| 1960 | yes      |
| 1960 | no       |
| 1960 | pending  |
| 1961 | total    |
| 1961 | pending  |
| 1961 | no       |
| 1961 | no       |
| 1961 | no       |
| 1961 | no       |
| 1961 | no       |
| 1961 | pending  |
| 1961 | total    |
+------+----------+
13 rows in set (0.00 sec)

mysql> select year,response,count(*) as count from vendors group by year,response;
+------+----------+-------+
| year | response | count |
+------+----------+-------+
| 1960 | no       |     1 |
| 1960 | pending  |     1 |
| 1960 | yes      |     2 |
| 1961 | no       |     5 |
| 1961 | pending  |     2 |
| 1961 | total    |     2 |
+------+----------+-------+
6 rows in set (0.00 sec)

mysql>

Or with php:

<?php
    mysql_connect("localhost", "", "") or die("could not connect");
    mysql_select_db("test"); 

$select = "select year, response, count(*) as count from vendors group by year, response";

$test = mysql_query($select);
while ($row = mysql_fetch_array($test)) {
      print "$row[year] total $row[response] = $row[count]<br>\n";
}

mysql_free_result($test);
?>

Result

1960 total no = 1
1960 total pending = 1
1960 total yes = 2
1961 total no = 5
1961 total pending = 2
1961 total total = 2

[peter_b]

Hi and thanks to all of you for your help, especially Hand.

This example worked nicely and I am now trying to modify it as follows:

$select = "select Year, count(*) as count, Response, count(*) as count2 from bdhsa group by Year, Response";
$result1 = mysql_query ($select);

////////////////////////////////////////////////////////////////
// main code
////////////////////////////////////////////////////////////////

// Display table by row using array and format into html table
    while ($row = mysql_fetch_array ($result1) ) {

// limit data output to responses with yes for testing
if ($row[Response] = "yes") {
	print "<td>$row[Year]  </td>";
    print "<td>$row[count] </td>";
	print "<td>$row[count2]</td>";
    }
} 
////////////////////////////////////////////////////////////////

The problem now is that I should be displaying the year once per line
with a total number of records per year and the total of "yes" responses.

I have tried to use a second WHILE statement as an internal loop as well as
a FOR and IF statements but can't seem to get it right.

The results I received shown here display year twice, except when I remove
the "RESPONSE" in the group by, and also the totals should not be equal:

YEAR TOTAL_CNT YES_CNT
1950 4 4
1950 1 1
1951 1 1
1951 2 2
1952 1 1
1952 2 2
1952 1 1
1953 6 6
1953 4 4
1954 3 3
1955 8 8
1955 1 1
1955 2 2

The output should have been this for example:
YEAR TOTAL_CNT YES_CNT
1950 10 4
1951 12 1
1952 21 1
1953 16 6
1954 30 3
1955 28 8

I am still trying to use arrays as best as possible.

Thanks again,
Peter

[Lars_Berg]

$select = "select Year, count(*) as count, sum(case when response = 'Yes' then 1 else 0 end ) as count2 from bdhsa group by Year";

[peter_b]

Hi and thanks.

That works great. I tried to find the use of SUM in the docs but I guess that it comes with wisdom.

Peter