The Sum Of All Rows: Use PHP or MySQL to Do The Math?
Firefox Wins Offline
Member
***
Posts: 164
Joined: Mar 2008
Post: #1
The Sum Of All Rows: Use PHP or MySQL to Do The Math?
Which is better / faster / less server load* for adding up the value of all rows in one field:
*Generally speaking: not knowing how many rows. Tongue

Use Mysql, into a single $result variable:

Code:
SELECT sum( data_field ) FROM `somewhere`


or
Something similar to this*
*html table optional, just asking about the PHP part

PHP Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<table border=1>
<?php
// source = http://www.wellho.net/forum/Writing-PHP/math-in-php-mysql.html

$DBhost = "localhost";
$DBuser = "mymy";
$DBpass = "knotpass";
$DBName = "yummydata";
$table = "fred";
$sum = 0;
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
mysql_select_db("$DBName") or die("Unable to select database $DBName");
$result = mysql_query("SELECT * FROM $table");
while ($row = mysql_fetch_row($result)) {
   print ("<tr><td>$row[0]</td><td>$row[1]</td></tr>");
   $sum += $row[1];
   }
?>

</table>

<?php
print "Total is $sum";
?>


  • Note: I almost decided MySQL would obviously be faster, and deleted the post, but... Is PHP possibly much better for calculations, while MySQL is optimized for i/o?
ty ZB/Y ...and / or anyone else.
(This post was last modified: 09-25-2010 04:11 PM by Firefox Wins.)
09-25-2010 04:09 PM
Find all posts by this user Quote this message in a reply
ZiNgA BuRgA Offline
Fag
*******
Posts: 3,357
Joined: Jan 2008
Post: #2
RE: The Sum Of All Rows: Use PHP or MySQL to Do The Math?
As you guessed, MySQL will always be faster.
PHP, being a fully fledged scripting language is more versatile.

My Blog
09-25-2010 05:24 PM
Find all posts by this user Quote this message in a reply
Firefox Wins Offline
Member
***
Posts: 164
Joined: Mar 2008
Post: #3
RE: The Sum Of All Rows: Use PHP or MySQL to Do The Math?
Well it works  Smile

PHP Code:
1
2
3
4
5
6
7
8
9
10
11
<?php

// db connect
$sql = "SELECT sum( points ) FROM `mybb_users` ";
$result=mysql_query($sql) or die('Error query failed');

$row = mysql_fetch_array( $result );
echo $row[0];
mysql_close($link);

?>


A simple:  echo $result; doesn't work...
I wasn't expecting to use 'mysql_fetch_array' to finish off this time, but it does work. Is there a  better way to turn the "sum()" type of ( $result ) into a usable variable?

(This post was last modified: 09-25-2010 06:42 PM by Firefox Wins.)
09-25-2010 06:41 PM
Find all posts by this user Quote this message in a reply
ZiNgA BuRgA Offline
Fag
*******
Posts: 3,357
Joined: Jan 2008
Post: #4
RE: The Sum Of All Rows: Use PHP or MySQL to Do The Math?
mysql_query returns a resource, if you look at the documentation for it.
You need to fetch data from this resource using mysql_fetch_array or similar, as you have done.

My Blog
09-25-2010 07:02 PM
Find all posts by this user Quote this message in a reply

« Next Oldest | Next Newest »

 Standard Tools
Forum Jump: