Sum a set of numbers from an mysql query

[shinomen]

I keep getting resource ID #2 when trying to run this code

$link=mysql_connect("localhost", "myuser", "mypass");
mysql_selectdb("radius");

$sumtime = mysql_query("SELECT SUM(sessiontime) FROM accounting WHERE 1 AND UserName='annsflorist' AND date > '2004-11-29' AND date < '2004-12-01' AND SessionTime > 0");

echo $sumtime;

I suspect it has something to do with array's which I am not familiar with yet. Any help would be appreciated.

Thanks

[coopster]

Welcome to WebmasterWorld, shinomen.

Yes, the result set is handled by fetch processes. After assembling the query and executing it, you need to fetch rows from the result set for processing in your PHP script. There is a nice little tutorial in our PHP Library [webmasterworld.com] that explains Basics of extracting data from MySQL [webmasterworld.com]

BTW, you have a function misspelled, it should be mysql_select_db() [php.net].

[shinomen]

thanks for the quick response, I will try that in just a minute.

Does the mis-spelling really affect anything? Because I have three different php files with the same mis-spelling and they all are working for me. Does this mean that I may have a mis-configuration somewhere whith mysql or is it just good practice to type it the way you have corrected?

I'm just curious about this cause I'm new and all.

Thanks again

[coopster]

It has been deprecated, however, for downward compatibility it will still work. Click the mysql_select_db() [php.net] for more information. I just don't want to see you continue using a deprecated function ;)

[shinomen]

I'm really not getting this. I've tried reading some things and can't seem to get it to work out for me. What I'm thinking is if I do the following code:

$sumtime = mysql_query("SELECT SUM(sessiontime) FROM accounting WHERE 1 AND UserName='annsflorist' AND date > '2004-11-29' AND date < '2004-12-01' AND SessionTime > 0");

echo $sumtime;

I should get a result of the sum of the times that I specified in the above query without having to do anything else, but I'm still getting a Resource ID#2.

You don't have to give me any code but could you explain why the above is not working like I think it should?

Also when I read through [webmasterworld.com...] I didn't understand what the following codes' purpose was:

for ($i=0; $i <$num_results; $i++)

I couldn't find anything that told me what "for" was meant to mean.

Thanks for your help

[coopster]

You don't have to give me any code but could you explain why the above is not working like I think it should?

Sure. It's because you have not fetched any data from the result set. mysql_query() [php.net] simply returns a result set, even if it is only one value. Think of the result set as a spreadsheet of data coming back to you. You need to fetch a row (sequentially starting with the first row) of data, then grab the column or columns from that row that you want to work with. I'll give you an example:

$sql = " 
 SELECT 
 SUM(SessionTime) AS SessionTime 
 FROM accounting  
 WHERE 1  
 AND UserName='annsflorist'  
 AND mydate > '2004-11-29'  
 AND mydate < '2004-12-01'  
 AND SessionTime > 0 
"; 
$rows = mysql_query($sql); 
$row = mysql_fetch_assoc [php.net]($rows); 
$sumtime = $row['SessionTime']; 
echo $sumtime;

A couple of other tips:

Although it's not a problem here I still tend to watch case sensitivity [dev.mysql.com] at all times, even column names (i.e. sessiontime vs SessionTime)

Although MySQL allows some keywords to be used as unquoted identifiers, using 'date' as a column name probably isn't a good practice. More here:
Treatment of Reserved Words in MySQL [dev.mysql.com]

[baze22]

coopster gave you a good reference. mysql_query doesn't actually return the result of the query, but returns a reference to the result. You'd still need to use that reference to get the result.

from the reference given above, in section 4. retrieve the info from mysql:

while ($row = mysql_fetch_array($query)) {
echo "<p>",$row['id'],": ",$row['manufacturer']; }

This will return a row until there are no more results in the set.

For your code it would be:

$result = mysql_query("SELECT SUM(sessiontime) as sumtime FROM accounting WHERE 1 AND UserName='annsflorist' AND date > '2004-11-29' AND date < '2004-12-01' AND SessionTime > 0"); 
while ($row = mysql_fetch_array($result)) {
echo "<p>" . $row['sumtime'] . "</p>";
}

baze

[edited by: baze22 at 4:44 pm (utc) on Dec. 3, 2004]

[baze22]

coopster, you beat me to it. That's 3 times today I've been in the middle of replying only to find I've been beat to the submit button. ;)

baze

[coopster]

Happens to all of us, baze22. I often go back and check the thread one last time in a separate tab (browser) before I submit a post that takes a bit more time to write...

...and when I finally do click submit -- wouldn't you know it! hehe.

I missed this part though:

for ($i=0; $i <$num_results; $i++)

I couldn't find anything that told me what "for" was meant to mean.

for [php.net] is just another control structure that allows you to loop through a result set. You'll probably see while [php.net] loops used more often though.

[shinomen]

Sorry for taking so long to respond. Thanks alot for the help everyone. It was pretty easy once I started understanding how arrays work and the different ways in which you can grab data from those arrays.

Thanks again for your help