Solved

Show count of 0

Posted on 2011-02-19
6
225 Views
Last Modified: 2013-12-13
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
Comment
Question by:phillyrob817
6 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34933635
Is this a school assignment or something?  Why would you want to do this?
0
 

Author Comment

by:phillyrob817
ID: 34933664
A need for a website I am working on.
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 100 total points
ID: 34934136
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
 
LVL 4

Expert Comment

by:Fugas
ID: 34937656
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 39686707
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now