Solved

Show count of 0

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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php checkbox validation 5 27
php simple error message 4 22
Curl & PHP Command Help 4 22
Decrypt string by php 7 27
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

813 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

17 Experts available now in Live!

Get 1:1 Help Now