Solved

Show count of 0

Posted on 2011-02-19
6
221 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

746 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

12 Experts available now in Live!

Get 1:1 Help Now