count question?

[pouncer]

I got a table called item_rating,

 

fields:

 

item_id | user_who_rated | rating

 

e.g data:

 

7GD | John | 5

7GD | Suzie | 4

7GD | Tim | 5

 

Ratings go from 1-5..

 

how do i get a total number of people who rated 1, who rated 2.. etc etcc

in the above case for item 7GD it should be like this

 

rating 1: 0 people

rating 2: 0 people

rating 3: 0 people

rating 4: 1 people

rating 5: 2 people

 

SELECT * FROM item_rating WHERE item_id='7GD'

..what next guys?

[suzzane2020]

 

$res1=SELECT * FROM item_rating WHERE item_id='7GD'

while($row1=mysql_fetch_array($res1))

{

  for($i=1;$i<=5;$i++)

  {

    $res2=select * from item_rating where rating='$i' and item_id='$row1['item_id']

    while($row2=mysql_fetch_array($res2))

    {

          $num=count($row2);

          }

}

}

 

kinda indirect but wud work!!!

 

[lachild]

Just change your SQL query to something like this

 

SELECT rating, COUNT(rating) AS count FROM item_rating WHERE item_id='7GD' GROUP BY rating;

 

 

Additional info can be found http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html

[jitesh]

select count(*) AS tot_peple_per_rating FROM tablename GROUP BY rating

[pouncer]

select count(*) AS tot_peple_per_rating FROM tablename GROUP BY rating

 

what about the echos?

 

thanks for the replies guys

[pouncer]

hey guys, you gave me the 1 line sql strings

 

 

but i have no clue how to output the results :s

 

please can someone help

[jitesh]

For Example

 

$sql ="select count(*) AS tot_peple_per_rating,rating FROM item_rating GROUP BY rating";
$result = mysql_query($sql);

while(list($tot_rating,$rating) = mysql_fetch_row($result)){
         echo "For Rating ".$rating ." - ".$tot_rating." Peoples";
         echo " <br> ";

}

[pouncer]

thanks alot jitesh, i got this

 

$item_id = $_GET['item_id'];

$sql ="select count(*) AS tot_peple_per_rating,rating FROM item_rating WHERE item_id='$item_id' GROUP BY rating";
$result = mysql_query($sql);

while(list($tot_rating,$rating) = mysql_fetch_row($result)){
         echo "For Rating ".$rating ." - ".$tot_rating." Peoples";
         echo " <br> ";
}

 

and it echos

 

For Rating 5 - 2 Peoples

 

is there a way to also make echo like

 

For Rating 4 - 0 Peoples

..

..

For Rating 1 - 0 Peoples

[jitesh]

$item_id = $_GET['item_id'];

$sql ="select count(*) AS tot_peple_per_rating,rating FROM item_rating WHERE item_id='$item_id' GROUP BY rating";
$result = mysql_query($sql);
             $list_all = array(1,2,3,4,5);
              $list_sel = array();
while(list($tot_rating,$rating) = mysql_fetch_row($result)){
             echo "For Rating ".$rating ." - ".$tot_rating." Peoples";
             echo " <br> ";
             $list_sel[] = $rating;
}
            $arr_diff = array_diff($list_all,$list_sel);
           for($i=0;$i<count($arr_diff);$i++){
              echo "For Rating ".$arr_diff[$i] ." - 0 Peoples";
              echo " <br> ";
        }

[pouncer]

thanks jitesh. almost there bit a slight error

 

For Rating 1 - 1 Peoples

For Rating 5 - 2 Peoples

For Rating - 0 Peoples

For Rating 2 - 0 Peoples

For Rating 3 - 0 Peoples

 

 

not sure why it does this:

For Rating - 0 Peoples

 

:s, should be '4'?

[jitesh]

I do not know exactly why the troubles is.

By the way debug like this

 

$arr_diff = array_diff($list_all,$list_sel);

 

// check,Is this displaying a arrays of ratings which has zero peoples

echo "<pre>";
print_r($arr_diff);

           for($i=0;$i<count($arr_diff);$i++){
              echo "For Rating ".$arr_diff[$i] ." - 0 Peoples";
              echo " <br> ";
        }

[pouncer]

require_once("../php_classes/class_login.php");
$item_id = $_GET['item_id'];

echo "<pre>";
print_r($arr_diff);

$sql ="select count(*) AS tot_peple_per_rating,rating FROM item_rating WHERE item_id='$item_id' GROUP BY rating";
$result = mysql_query($sql);

    $list_all = array(1,2,3,4,5);
    $list_sel = array();

while(list($tot_rating,$rating) = mysql_fetch_row($result)){
   		//echo "For Rating ".$rating ." - ".$tot_rating." Peoples";
        //echo " <br> ";
        //$list_sel[] = $rating;
}
            
$arr_diff = array_diff($list_all,$list_sel);
    for($i=0;$i<count($arr_diff);$i++){
	   echo "For Rating ".$arr_diff[$i] ." - 0 Peoples";
       echo " <br> ";
    }

 

that way, it echos

For Rating 1 - 0 Peoples 

For Rating 2 - 0 Peoples 

For Rating 3 - 0 Peoples 

For Rating 4 - 0 Peoples 

For Rating 5 - 0 Peoples

 

when i uncomment the lines it echos

 

For Rating 1 - 1 Peoples

For Rating 5 - 2 Peoples 

For Rating  - 0 Peoples 

For Rating 2 - 0 Peoples

For Rating 3 - 0 Peoples

[pouncer]

ok i tried this

 

$item_id = $_GET['item_id'];

$sql ="select count(*) AS tot_peple_per_rating,rating FROM item_rating WHERE item_id='$item_id' GROUP BY rating";
$result = mysql_query($sql);

    $list_all = array(1,2,3,4,5);
    $list_sel = array();

while(list($tot_rating,$rating) = mysql_fetch_row($result)){
   		echo "For Rating ".$rating ." - ".$tot_rating." Peoples";
        echo " <br> ";
        $list_sel[] = $rating;
}

    

$arr_diff = array_diff($list_all,$list_sel);

echo "<pre>";
print_r($arr_diff);

    for($i=0;$i<count($arr_diff);$i++){
	   echo "For Rating ".$arr_diff[$i] ." - 0 Peoples";
       echo " <br> ";
    }

 

and it echos

 

For Rating 1 - 1 Peoples

For Rating 5 - 2 Peoples

 

Array

(

    [1] => 2

    [2] => 3

    [3] => 4

)

For Rating  - 0 Peoples 

For Rating 2 - 0 Peoples 

For Rating 3 - 0 Peoples

 

[pouncer]

i think i sorted it

 

for($i=1;$i<= count($arr_diff);$i++)

[jitesh]

for($i=0;$i<count($arr_diff);$i++){ // Remove this

and keep

foreach($arr_diff AS $key => $ratining)

	   echo "For Rating ".$ratining ." - 0 Peoples";
       echo " <br> ";
    }