Solved

Show count of 0

Posted on 2011-02-19
6
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 110

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

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

627 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