Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Show count of 0

I received answer on how to use count and  group by in mysql and it helped with my last question. But a new problem has arisen that I can't figure out how to overcome.  In my table i have 3 fields id,num,diff. For every num I want to count diff. I use the group by and that is working. 2 issues though: I am looping through by table using an array ( 1 to 56) and using diff to compare to array.
The table looks like this:
id       num     diff
1          1        1
2          1        1
3          1        4
4          2        6
6          2        1
7          2        3
8          2        5
9          3       1
10        3       4
11        3       9
etc


I want to count diff for each num 1 thru 56. For example for each 1 in num, 1 appears 2  times in diff . I want to  also show if count = 0 ; But it always returns 1 or 0 depending on my if statement.

Here is script:

$myar = range(1, 56);
foreach ($myar as $b) {
  Print "<th>$b</th>";


  $rs2 = mysql_query("select num,diff,count(diff) as mycount from ccc where  (diff = $b) group by num,diff");
  while ($row2 = mysql_fetch_array($rs2)) {
    $num_rows2 = mysql_num_rows($rs2);
    $num2 = $row2['num'];
    $diff = $row2['diff'];
    $mycount2 = $row2['mycount'];
    if ($mycount2 > 1) {
      Print "<td><font size=3>$num2<br /> $mycount2</font></td>";
    }
    else {
      Print "<td><font size=3>0<br />0</font></td>";
    }

Thx in advance
 
0
phillyrob817
Asked:
phillyrob817
1 Solution
 
Ray PaseurCommented:
Is this a school assignment or something?  Why would you want to do this?
0
 
phillyrob817Author Commented:
A need for a website I am working on.
0
 
Beverley PortlockCommented:
OK, this should do it. I put your data into an array of arrays so I could load my class for test purposes, but you could alter your code to change the load like so


  $rs2 = mysql_query("select num,diff,count(diff) as mycount from ccc where  (diff = $b) group by num,diff");
  while ($row2 = mysql_fetch_array($rs2)) {
    $num_rows2 = mysql_num_rows($rs2);
    $num2 = $row2['num'];
    $diff = $row2['diff'];

    $nd->add( $row2['id'], $row2['num'], $row2['diff']);
}


then display with

$nd->countNums();

Obviously countNums can be changed to return a series of table rows rather than echo a string if that is what's required.


<?php

// Test data
//
$data = array( 
               array( 1 ,         1,        1 ),
               array( 2 ,         1,        1 ),
               array( 3 ,         1,        4 ),
               array( 4 ,         2,        6 ),
               array( 6 ,         2,        1 ),
               array( 7 ,         2,        2 ),
               array( 8 ,         2,        5 ),
               array( 9 ,         3,        1 ),
               array( 10,         3,        4 ),
               array( 11,         3,        9 )
);


// ----- code starts -----


class NumDiff {
  
     public $diff;

     function __construct() {
          $this->diff = array();
     }

     function add( $i, $d ) {
          $this->diff[$i] = $d;
     }

     function countNums( $n ) {
          $answer = array_count_values( $this->diff );
          return (isset($answer[$n])) ? intval( $answer[$n] ) : 0;
     }
}



class NumDiffArray {
     public $arr;

     function __construct() {
          $this->arr = array();
     }

     function add( $i, $n, $d ) {
          if ( !isset( $this->arr[$n] ) )
               $this->arr [ $n ] = new NumDiff();

          $this->arr [$n]->add( $i, $d );
     }

     function countNums() {
          foreach( $this->arr as $num => $diff ) {
               $times = $diff->countNums( $num );
               echo "$num appears in diff $times times<br/>";
          }
     }
}

// ----- code ends -----


$nd = new NumDiffArray();

// Load test data
//
foreach( $data as $anArray )
     $nd->add( $anArray[0],  $anArray[1],  $anArray[2] );

// get counts
//
$nd->countNums();

Open in new window

0
 
FugasCommented:
Hi, it's not clear for me, what you really want to do. But I think, taht it could be done with just one query. Do you need maybe sum function instead of count?
0
 
CWS (haripriya)Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now