Hi guys,
How do i add all the numbers in a column in a database? is there any command in mysql/php to do it.
My db structure is as followes:
[php]
Name Points Reputation
Test1 10 1
Test2 20 4
Test3 20 9[/php]
I want to add all the numbers in the points column and display a result of 50.
Thanks in advance.
Hi there,
Try the following method in your SQL statement:
SELECT
`name`
,`points`
,SUM(`points`) AS total
,`reputation`
FROM....
i tried but i get an error:
[php]Notice: Undefined index: points in E:\xampp\htdocs\php\3points\points_result.php on line 4
0[/php]
[php]include “config.php”;
$totalpoints = mysql_query(“SELECT SUM(points) AS total FROM pointstable”);
while($row = mysql_fetch_array($totalpoints))
{
echo number_format($row[‘points’]);
}[/php]
That’s because the result is being returned “AS total”, so you need to call it with $row[‘total’]
Thankyou Smokey PHP.It’s working now.
Also is it possible to add some sort of timestamp,so that i can sort the points accumulated in the last 24 hrs or any given time in that matter??
That depends on whether or not you have an added or modified field in the relevant table.
My table is what i ahve showed you before.What field should i modify in the “Points” table?
[php]
Name Points Reputation
Test1 10 1
Test2 20 4
Test3 20 9
[/php]
You would need to add a timestamp or datetime field that gets updated whenever that row gets points added. That way you could use the mysql/php functionality to produce a more complex narrowed down set of results based on time.
I have added a time(datetime type) field and used now() to insert the date and time
[php]<?php
include “config.php”;
$name = $_POST[‘name’];
$points = $_POST[‘points’];
$reputation = $_POST[‘reputation’];
//$time = time();
//$date = date(“Y-m-d H:m:s”);
$sql = “INSERT INTO test1 (name, points, reputation, time) VALUES (’$name’, ‘$points’, ‘$reputation’, NOW())”;
if (!mysql_query($sql)) {
die('Error: ’ . mysql_error());
}
?>[/php]
But don’t know how to sort the points accumulated based on the last 24 hrs activity. I know something has to be changed in the bellow code, but cannot figure out what??
[php]include “config.php”;
$totalpoints = mysql_query(“SELECT SUM(points) AS total FROM pointstable”);
while($row = mysql_fetch_array($totalpoints))
{
echo number_format($row[‘points’]);
}[/php]
Pls Help .
Try the following:
SELECT SUM(points) AS total FROM pointstable WHERE UNIX_TIMESTAMP(`time`)>TIMESTAMP(NOW())-86400