[SOLVED] How do I get the sum of values

[StanLytle]

I'm new to php, and just understand a few basic functions.  I have a table that records how many times a person's photo(s) has been viewed.  How do I go through the table and add up the number of times that all of the person's photos have been viewed?  The field names are "UserID" and "views".  Any help appreciated.

 

Stan

 

[pocobueno1388]

<?php

$sql = mysql_query("SELECT COUNT(*) FROM tbl_name WHERE UserID='$userID'");
$num = mysql_num_rows($sql);

echo $num;

?>

[boo_lolly]

something like this:

$sql = "SELECT * FROM your_table WHERE user = '{$username}'";
$query = mysql_query($sql) OR die(mysql_error());

$views = 0;
while($row = mysql_fetch_array($query)){
        $views += $row['views'];
}

echo $views;

[Rauldinho]

You can use the SUM() function. This will add every value in a field, not count them.

 

Table Name= mytable

Photo Views

  A        2

  B        2

  C        2

 

 

$sqlCount = "SELECT COUNT(Views) AS TotalViews FROM mytable"  =======>  TotalViews=3

 

$sqlSum = "SELECT SUM(Views) AS TotalViews FROM mytable"  =======>  TotalViews=6

[StanLytle]

I tried the first one:

 

<?

$sql = mysql_query("SELECT COUNT(*) FROM Photos WHERE UserID='$userID'");

$num = mysql_num_rows($sql);

echo $num;

?>

 

All it returns is "1".  I tried substituting a user number for $userID, but it still returned "1".

 

I tried the second one:

 

<?

$sql = "SELECT * FROM Photos WHERE UserID = '{$username}'";

$query = mysql_query($sql) OR die(mysql_error());

$views = 0;

while($row = mysql_fetch_array($query))

{

  $views += $row['views'];

}

echo $views;

?>

 

It returns "0".  I tried changing things around, but I still got "0".

 

The third one:

 

<?

$sqlCount = "SELECT COUNT(Views) AS TotalViews FROM Photos"  =======>  TotalViews=3

$sqlSum = "SELECT SUM(Views) AS TotalViews FROM Photos"  =======>  TotalViews=6

?>

 

Returns, Parse error: syntax error, unexpected T_IS_IDENTICAL.

 

Stan

 

[boo_lolly]

the reason why my code segment didn't work is probably because of your column type in your database. is it varchar or int? it needs to be int because there will never be anything other than digits being placed in that column.

[StanLytle]

I should add, that this table is dynamic, records are constantly being add to and deleted from it.  The table is therefore not in order.  I would think that some sort of SORT would be required, to get the "UsedID" in numerical order, then loop thru the "UsedID" until all had been counted, and then loop thru the next "UsedID", and on and on.  Also, as a note, the "UsedID" are not consecutive and have missing "UsedID" numbers.

 

Stan

 

[StanLytle]

"Views" and "UserID" are both int(11).

 

Stan

 

[per1os]

You can use the SUM() function. This will add every value in a field, not count them.

 

Table Name= mytable

Photo Views

  A        2

  B        2

  C        2

 

 

$sqlCount = "SELECT COUNT(Views) AS TotalViews FROM mytable"  =======>  TotalViews=3

 

$sqlSum = "SELECT SUM(Views) AS TotalViews FROM mytable"  =======>  TotalViews=6

 

That is exactly correct. Try this:

 

<?php

$sql = "SELECT SUM(views) AS Views FROM Photos WHERE UserID = '{$username}'";
$query = mysql_query($sql) OR die(mysql_error());
$view = mysql_fetch_array($query);
$view = $view[0];

echo "There have been " . $view . " Photos viewed.";
?>

[bwochinski]

<?php

$sql = "SELECT UserID, SUM(Views) as totalviews FROM photos GROUP BY UserID";
$result = mysql_query($sql);

while ( $row = mysql_fetch_object($result) ) { $views[$row->UserID] = $row->totalviews; }

echo '<pre>';
print_r($views);
echo '</pre>';
?>

 

This will allow you to get total counts for all users, which I then put into an array with the userID as the key and view count as the value.

[StanLytle]

I got it to work.  This is what it took:

 

<?

$query = "SELECT UserID, SUM(Views) FROM Photos GROUP BY UserID";

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

while($row = mysql_fetch_array($result))

{

echo "There are ". $row['SUM(Views)'] ." views for user ". $row['UserID'] ."";

echo "<br />";

}

?>

 

Thanks to everyone for their help!

 

Stan