Help for php & mysql

[mungra]

Hello , Well i have a function whose aim is to get the sum of the field "Times" and return the value..I have implemented it as below.

function GetSumViewed($Category)
{include("dbinfo.inc.php");

$query="SELECT Times from mytable where name = 'people'";
$result=mysql_query($query);

$num=mysql_numrows($result);

if($num==0)

		echo "No Records founds";
		else

		{$i=0;
$total=0;

while ($i < $num) {

$count=mysql_result($result,$i,"Times");
$total+=$count;
$i++;
}//while



}//else
echo $total;

mysql_close();
}

My Question is how can i avoid the while loop and use the SQL function SUM directly and assign it to a variable in php?
Thanks

regards
Mungra

[Shrike]

SUM is a function in the same vein of PHP functions, it takes an input and returns a value. Assignment of the return value is done with SQL's AS keyword. So we can say

SELECT SUM( Times ) AS sum_times ...

[thephpguy]

I would suggest creating another table containing a field for the total 'Times'...

So when you are inserting new records into the database, you just add the current Times you're inserting to the the Times value from the Total Table.

[rincewind456]

Like this

function GetSumViewed($Category){
	include("dbinfo.inc.php");

$query="SELECT SUM(Times) from mytable where name = 'people'";
$result=mysql_query($query);

$num=mysql_numrows($result);

if($num==0){

	echo "No Records founds";
} else {
	$total = $result;
}
mysql_close();

echo $total;

}

[Shrike]

thephpguy wrote:

I would suggest creating another table containing a field for the total 'Times'...

So when you are inserting new records into the database, you just add the current Times you're inserting to the the Times value from the Total Table.

That is not the way to improve performance. Would you create a new table for each and every column that you wanted to perform sums of? How about row counts? That too? It is just as efficient to add an index to the column which requires such calculations.

[thephpguy]

Shrike wrote:

That is not the way to improve performance. Would you create a new table for each and every column that you wanted to perform sums of? How about row counts? That too? It is just as efficient to add an index to the column which requires such calculations.

It depends on how big his system was. I agree it's not the best solution, but it is a solution nevertheless.

Cheers