CODEWITHSUNDEEP
mysqlcountsum
Travis
Travis

Reputation: 137

Get total from amounts in rows MySQL PHP

I'm working on a lottery system for my game and i'm having issues with the total pot size

current progress: http://playsilab.com/lottery

Anyways, what i'm doing is having it so a user will buy a ticket in the game and that will count as +5 currency to the pot.

This is what the table looks like enter image description here

basically i need it to count all those 5000000's and output 1 number or my other idea, get the amount of people and *5 any of those would be great :)

basically for 8 people with 5000000, it should print out 40000000 (40M)

my attempt:

$pot = mysql_query("SELECT name, SUM(pot), COUNT(name) FROM contestants");
$details = mysql_fetch_array($pot);
$totalpot = $details['SUM($details['pot']'];

Upvotes: 1

Views: 4803

Answers (2)

Mark Byers
Mark Byers

Reputation: 838076

This line is wrong:

$totalpot = $details['SUM($details['pot']'];

The problem seems to be a combination of:

  • unescaped quotes inside a quoted string
  • your dollar reference is not being evaluated since it is inside single quotes
  • a missing parenthesis
  • and just generally not being correct

Give your columns aliases so that it is easier to refer to them in your source code:

SELECT SUM(pot) AS sum_pot, COUNT(name) AS count_name FROM contestants

Then you can just write:

$totalpot = $details['sum_pot'];

Also there are a number of other problems with your code, for example:

  • You are not checking to see if you query failed before trying to access the results.
  • It makes little sense to return both a name from a single row and an aggregated value like a SUM in the same result set. In MySQL it is legal, but in many other databases this would be rejected as an error.

Upvotes: 2

Peter
Peter

Reputation: 16923

my solution:

list($totalpot) = @mysql_fetch_row(mysql_query("SELECT SUM(pot) FROM contestants WHERE pot=5000000"));
list($count) = @mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM contestants WHERE pot=5000000"));

or if you want to have sums by pot amount

$query = mysql_query("SELECT pot, SUM(pot) as sum_pot, COUNT(*) as count_pot FROM contestants GROUP BY pot");
while($row = @mysql_fetch_assoc($query)) {
    // $row['pot'] is 50000
    // $row['sum_pot'] is 40M
    // $row['count_pot'] is 8
}

Upvotes: 0

Related Questions