[SOLVED] sum mysql data

[blueman378]

hi guys ,

i have a table with a column called gPlays,

i was wondering is it possible to add all the values together and echo it?

 

btw they are all smallint

[tapos]

Use this:

<?php
// Make a MySQL Connection

$query = "SELECT SUM('fieldname') FROM 'tablename'"; 


?>

 

Thanks

--

Tapos Pal

[blueman378]

would this echo it?

$q = "SELECT sum( gPlays ) FROM " . Games . " ";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    

 

database connect ect is done elsewhere

[raku]

From the code you entered, it looks like you're using a framework. I'm not sure which you're using, but this is how you could do it:

 

$result = mysql_query($query);
if($row = mysql_fetch_array($result))
{
// access $row array to get info from database, set it to a variable (i.e. $var)
}
echo $var;

 

Hope this helps, it would probably be better to find out how your specific framework does it though, since it may automatically sanitize your queries and what not. Not really necessary for this query, but may be for others you're doing.

 

[blueman378]

well for example heres a working query:

<?php
$game = $_GET['game'];
    global $database;
    $q = "SELECT * FROM " . Games . " //Games is defined in a constants page
          WHERE gName = '$game'
          ORDER BY `gName` ASC
         ";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_numrows($result);
    if( $num_rows == 0 ){
      return 'Game Not Found!';
    }
    while( $row = mysql_fetch_assoc($result) ) {
    echo $row[gName];
    }

?>

 

basically this follows this

 

does that give you enough info?

[blueman378]

ok so this works to retrieve the values:

$q = "SELECT * FROM " . Games . " ";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_numrows($result);
    if( $num_rows == 0 ){
      return 'Game Not Found!';
    }

    while( $row = mysql_fetch_assoc($result) ) {
     echo $row[gplays];
    }

but the moment i try

$q = "SELECT SUM('gplays') FROM " . Games . " ";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_numrows($result);
    if( $num_rows == 0 ){
      return 'Game Not Found!';
    }

    while( $row = mysql_fetch_assoc($result) ) {
     echo $row[gplays];
    }

i get a blank,

any ideas?

[blueman378]

well i gave up on that and just put this together

 

<?php
    global $database;
    $q = "SELECT * FROM " . Games . " ";   
    $result = $database->query($q) or die("Error: " . mysql_error());
    /* Error occurred, return given name by default */
    $num_rows = mysql_numrows($result);
    if( $num_rows == 0 ){
      return 'Game Not Found!';
    }
    $rate = 0;
    while( $row = mysql_fetch_assoc($result) ) {
     $rate = $rate + $row[gplays];
    }
echo $rate;
?>

[hitman6003]

MySQL will return the field name as "SUM(gplays)" when the sum operation is used, rather than just "gplays".  Use the "AS" SQL command to give it a name:

 

    $q = "SELECT SUM('gplays') AS total_plays FROM " . Games . " ";   
    $result = $database->query($q);  // no need for the "die" here, there is error checking below
    
    /* check for errors */
    if ( mysql_num_rows($result) != 1 ) {
        return 'Unable to get total plays';
    } else {
        return mysql_result($result, 0, 'total_plays');
    }