...

View Full Version : Resolved add up numbers from a field using php



sonny
01-31-2012, 05:14 PM
Hi, not sure what section to post in

I'm trying to add up all the numbers and display the total from a field named hrs
I tried this below but does not work, can someone show how to accomplish this?.
its to be displayed inside my php code.


SELECT SUM(hrs) AS hr_total FROM $db_table WHERE year = $year


Thanks
Sonny

Keleth
01-31-2012, 05:26 PM
The query looks fine. I assume you've tried the regular debugging of echoing the query to make sure it looks right?

If you remove SUM(hrs) and replace with hrs, does the query give you back what you expect?

sonny
02-01-2012, 12:32 AM
Hi, can someone show what I'm doing wrong this does not work
trying to add all numbers in the hrs field and display the total.


$year=$_POST['year'];
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'");
while($row = mysql_fetch_assoc($results))
{
//$total=$row['total'];
$total=$row['hrs'];
echo $total;
}

Thanks
Sonny

BluePanther
02-01-2012, 12:54 AM
Hi, can someone show what I'm doing wrong this does not work
trying to add all numbers in the hrs field and display the total.


$year=$_POST['year'];
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'");
while($row = mysql_fetch_assoc($results))
{
//$total=$row['total'];
$total=$row['hrs'];
echo $total;
}

Thanks
Sonny

You should be using $row['SUM(hrs)'] I'm pretty sure - you can make sure by inserting a var_dump($row) just before you assign $total. Or, you could change your query to alias the sum - SUM(hrs) as total - then use $row['total'].

sonny
02-01-2012, 01:11 AM
This adding up stuff is confusing the heck out of me all day, what do you mean by "Or, you
could change your query to alias the sum - SUM(hrs)"? can you show, do I just switch hrs with as total?

I'm just looking to display the total sum from a "hrs" field based on a year value.

Thanks
Sonny

PS your signature just helped me out with a form I had.

BluePanther
02-01-2012, 01:22 AM
This adding up stuff is confusing the heck out of me all day, what do you mean by "Or, you
could change your query to alias the sum - SUM(hrs)"? can you show, do I just switch hrs with as total?

I'm just looking to display the total sum from a "hrs" field based on a year value.

Thanks
Sonny

PS your signature just helped me out with a form I had.

Have you edited your posts any? Or have I just not been paying attention haha. You're already using an alias in your query. An alias is a nickname for something else in MySQL. So, your query - SELECT SUM(hrs) AS total FROM $table WHERE year='$year' - selects the SUM of hrs and calls that value total. So, you would access it using $row['total']. The problem is your PHP, not your MySQL - although it would seem you don't know much about aliases.

sonny
02-01-2012, 01:39 AM
Hi
Nope, I had no edits to my original post which has the alias in it that was my first attempt I tried earlier today

as far as the php part goes, there's not much to that it's exactly as posted below,
there is only a echo and that its. so you say nothing is wrong with the mysql syntax
so I must be close I guess.



// I'm connecting fine
$year="2012";
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'");
while($row = mysql_fetch_assoc($results))
{
echo $row['total'];
}

BluePanther
02-01-2012, 01:46 AM
Hi
Nope, I had no edits to my original post which has the alias in it that was my first attempt I tried earlier today

as far as the php part goes, there's not much to that it's exactly as posted below,
there is only a echo and that its. so you say nothing is wrong with the mysql syntax
so I must be close I guess.



// I'm connecting fine
$year="2012";
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'");
while($row = mysql_fetch_assoc($results))
{
echo $row['total'];
}


That should be correct. You're not doing any error reporting on your mysql_query though, which you really should be. Try adding a check for $result being false, or just adding 'or die(mysql_error())' to the end of the mysql_query() function.

But, regardless, you still have a problem which I can't believe I didn't notice. Your query result is saved into $result, but you're fetching the rows from $results - make them the same :).

Just another wee note as well, you should indent your code to make it more readable. It may not seem important just now, but for large scripts it makes a huge difference.

sonny
02-01-2012, 02:23 AM
Hi good idea I forgot to add error checking, don't whats wrong now I get this


PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

I'm connecting and the table and columns exist, I see nothing wrong with the syntax



<?php
require("connect.php");
$year="2012";
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'");
while($row = mysql_fetch_assoc($result)) or die(mysql_error());
{
echo $row[total];
}

BluePanther
02-01-2012, 02:45 AM
Hi good idea I forgot to add error checking, don't whats wrong now I get this


PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

I'm connecting and the table and columns exist, I see nothing wrong with the syntax



<?php
require("connect.php");
$year="2012";
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'");
while($row = mysql_fetch_assoc($result)) or die(mysql_error());
{
echo $row[total];
}


You're error reporting the wrong bit.


<?php
require("connect.php");
$year="2012";
$table="test";
$result = mysql_query("SELECT SUM(hrs) AS total FROM $table WHERE year='$year'") or die(mysql_error());
while($row = mysql_fetch_assoc($result));
{
echo $row[total];
}

Also, why are you putting year and table into variables, then putting those variables into the query? Why not just put the values into the query to begin with?

sonny
02-01-2012, 04:20 AM
Still not working, but get no errors, now

the db has 3 entry's in that column named (hrs), numbers "2' "4" and "5"
$row[total]; should echo "11" right?, I just get a white page.
I even removed the year column from the query. this is the
complete page I'm using. I'm stumped


<?php

require("connect.php");

$result = mysql_query("SELECT SUM(hrs) AS total FROM test") or die(mysql_error());

while($row = mysql_fetch_assoc($result));
{
echo $row[total];
}
?>


Thanks
Sonny

BluePanther
02-01-2012, 04:25 AM
Still not working, but get no errors, now

the db has 3 entry's in that column named (hrs), numbers "2' "4" and "5"
$row[total]; should echo "11" right?, I just get a white page.
I even removed the year column from the query. this is the
complete page I'm using. I'm stumped


<?php

require("connect.php");

$result = mysql_query("SELECT SUM(hrs) AS total FROM test") or die(mysql_error());

while($row = mysql_fetch_assoc($result));
{
echo $row[total];
}
?>


Thanks
Sonny

Try this:


<?php

require("connect.php");

$result = mysql_query("SHOW COLUMNS FROM test") or die(mysql_error());

while($row = mysql_fetch_assoc($result));
{
var_dump($row);
}
?>

And tell me what it spits out (probably better viewing source when checking the output).

sonny
02-01-2012, 04:50 AM
I get bool(false)

the hrs column is (int)5
defined as 0

Sonny

BluePanther
02-01-2012, 05:19 AM
I get bool(false)

the hrs column is (int)5
defined as 0

Sonny

Copy and paste the exact output.

sonny
02-01-2012, 05:26 AM
that's everything


bool(false)

sonny
02-01-2012, 06:04 AM
Its what I thought, the sum query is not right
for some reason, the basic select returns all
the years and hrs with no problem.
2012 2
2012 4
2012 5
2012 2
example SELECT * return



<?php
require 'connect.php';
/*
$result = mysql_query("SELECT SUM(hrs) AS total FROM test") or die(mysql_error());//

while($row = mysql_fetch_assoc($result));
{
echo $row[total];
}
*/
$result = mysql_query("SELECT * FROM test");//WORKS
while($row = mysql_fetch_array($result))
{
echo $row['year'] . " " . $row['hrs'];
echo "<br />";
}
?>

BluePanther
02-01-2012, 06:05 AM
Odd. Try:


<?php

require("connect.php");

$result = mysql_query("SHOW COLUMNS FROM test") or die(mysql_error());

var_dump($result);
?>

sonny
02-01-2012, 06:26 AM
Heres what I get


$result = mysql_query("SHOW COLUMNS FROM test") or die(mysql_error());
var_dump($result);
// DISPLAYS --> resource(4) of type...


thanks
Sonny

BluePanther
02-01-2012, 06:37 AM
If test is the right table name, the sum query should work fine. Try this:


$result = mysql_query("SELECT SUM(hrs) as total FROM test") or die(mysql_error());
$info = mysql_fetch_assoc($result);
var_dump($info);

sonny
02-01-2012, 06:45 AM
Finlay got it, in case anyone needs to do something like this, works
perfect.



$result = mysql_query('SELECT SUM(hrs) AS total FROM test');
$row = mysql_fetch_assoc($result);
echo = $row['total'];


thanks BluePanther for staying with me, your alright.

Sonny



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum