help with 'count'

[davieboy]

I have a database of images each which gets it own number of hits.

how do i then 'add ' all these hits together to get a total number of hits?

 

SELECT *, COUNT(hits) FROM photos GROUP BY hits DESC LIMIT 5

 

is my attempt but i know its wrong

 

david

[PC Nerd]

um well you ca do it in SQL, but ill have to look it up

 

in php youd go

 

SQL = "Select image, hits from etc...."

 

loop through query results

 

while $result = ,mysql_fetch_array{

$total_hits = $result['hits'] + $total_hits;

}

ehco $total_hits

 

 

SQL would be easier...

 

 

ill look it up l8a, remind me with a PM

[Lumio]

<?php
   $result = mysql_query('SELECT `hits` FROM `photos`;');
   $hits = 0; //define $hits
   while ($row = mysql_fetch_assoc($result)) $hits += intval($row['hits']); //add $hits
   echo $hits;
?>

[davieboy]

cant find a way to get wither codes posted to work

 

David

[prabhuksmani]

Hi david,

      Use this SQL Query:

        Select SUM(hits) from photos;

 

Thanx

Prabhu SM

[davieboy]

how would i then get it with the totals

 

$query = "SELECT *, SUM(hits) FROM photos GROUP by hits DESC LIMIT 5"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
while ($r=mysql_fetch_array($result))
{  
$id=$r["id"];
$copyright =$r["copyright"];
$hits= $r["hits"];
$airline = $r["airline"];
echo "<a href=\"photographer.php?photographer=$copyright\">$hits / $copyright<br>";
//

}

[Lumio]

Use print_r() for getting Array content.

For instance:

<?php
   $arr['key'] = 'value';
   print_r($arr);
?>
You see something like
[code]Array (
   [key] => value
)

 

Now you can get the result of query like that:

<?php
   //...
   $result = mysql_query($query) or die(mysql_error());
   $row = mysql_fetch_assoc($result);
   print_r($row);
?>

and now you'll know how to use that query.[/code]

[davieboy]

figured part of it out

 

$query = "SELECT *, COUNT(hits) AS COUNT FROM photos GROUP BY copyright LIMIT 5"; 
//$query = "SELECT device_id, COUNT(week) AS count FROM failed WHERE week = '$week' GROUP BY device_id";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) 

{ 
echo $row['id'] . ' <a href=\"/photographer.php?photographer=' . $row['copyright'] .'\">' . $row['copyright'] . '</a><br>'; 
$copyright = $row['copyright'];
$hits = $row['hits'];

 

giving an odd number at the top thought

2460

3111 user1

2477 user2

2094 user3

2062 user4

 

not sure why or where from

[davieboy]

actually ive changed it a we bit.

 

trying to find out the total number of images in the database from certain people ordered by the most number first.

 

SELECT *, COUNT(id) AS COUNT FROM photos WHERE status ='accepted' and in_screening = '1' GROUP BY copyright DESC LIMIT 5

 

and its not working