Member Avatar for justted

Hello,

I was wondering if anyone could help me as I am trying to add all the rows of a particular field in a MySQL table using PHP.

I have a table named members2 which has a field named points! What I want to be able to do is display the total number of points currently in the site that all members have!

But I have no idea how to do this and cannot seem to find anything when googling it!

Any help is much appreciated!

Justin

  • 3 Contributors
  • 4 Replies
  • 237 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by justted

Dani AI

Generated

The thread shows two common approaches: summing in PHP (the loop shown by ) and asking the database for the total (the aggregate approach suggested by ). Letting the database do the work is almost always preferable — fewer round trips, less memory used by PHP, and much faster on large tables.

A few important practical notes that complement the replies already here:

  • MySQL’s SUM ignores NULL values and will return NULL if there are no matching rows, so wrap it with COALESCE or IFNULL to get 0 instead of NULL.
  • Ensure the points column is stored as a numeric type (INT/DECIMAL). Text columns cause implicit conversion and unexpected results.
  • The examples in the thread use the old ext/mysql API; that extension has been removed from modern PHP versions. Use PDO or mysqli and prepared statements for safety and forward-compatibility.
  • For per-group totals use GROUP BY (as mentioned), and for filtered sums add a WHERE clause.

Example (modern, safe PDO usage):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->query("SELECT COALESCE(SUM(points), 0) FROM members2");
$totalPoints = (int)$stmt->fetchColumn();
echo "Total points: $totalPoints\n";
?>

Performance & reliability tips: a full-table SUM can be expensive on very large tables; consider maintaining a small summary table updated on writes or caching the total in Redis if reads are far more frequent than updates. Use BIGINT for totals that may exceed 32-bit limits. When updating a running total, use atomic updates or transactions to avoid race conditions.

Recommended Answers

All 4 Replies

Member Avatar for ryan_vietnow

you can try something like this:

$query="Select points from members2";
$result=mysql_query($query);

$total=0;
while($row=mysql_fetch_array($result))
{
$total=$total + $row['points'];
}

echo "Total is:" . $total;
Member Avatar for mcd

Easier:

$query="SELECT SUM(points) AS `total` from members2";

$result=mysql_query($query);
while($row=mysql_fetch_assoc($result))
  $total = $row['total'];

If you want to limit the sum to certain sets of data, you will have to use the GROUP BY function in mysql. Learning about grouping and its associated functionality will help you avoid a lot of tedious, repetitive coding.

Member Avatar for ryan_vietnow

Easier:

$query="SELECT SUM(points) AS `total` from members2";

$result=mysql_query($query);
while($row=mysql_fetch_assoc($result))
  $total = $row['total'];

If you want to limit the sum to certain sets of data, you will have to use the GROUP BY function in mysql. Learning about grouping and its associated functionality will help you avoid a lot of tedious, repetitive coding.

much easier:

$query="SELECT SUM(points) AS `total` from members2";
$result=mysql_query($query);
$total=mysql_result($result,0,"total");
commented: interesting. haven't had much use for just pulling one field per query, but it piqued my interest +2
Member Avatar for justted

Thank you so much! :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.