PHP SQL Averages

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
NotOnUrNelly
Forum Commoner
Posts: 61
Joined: Wed Mar 24, 2004 4:45 pm

PHP SQL Averages

Post by NotOnUrNelly »

pickle | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi all,

Im struggling to extract the variables out of sql to find averages and sum.

I have the following code and I cannot seems to extract the values into variables.

Many Thanks in Advance 

Jamie

[syntax="php"]
$sql = 'select count(*) as cnt, SUM(Score) as sum, (SUM(Score)/count(*)) as avg FROM tblChart;';
$sql = sql_return($sql);
 $sql = mysql_query($sql); 
$sql_result = @mysql_fetch_array($sql);
  
$avg=$sql_result['avg'];
$cnt=$sql_result['cnt'];
$sum=$sql_result['sum'];


//sql_return() is a function that I send all of my sql statements to and is displayed below

function sql_return($sql_text)
{
$conn = db_connect();
$sql = sql_text;
$query = $sql_text;
//echo $sql_text;
$results = @mysql_query($query,$conn);
$result = db_result_to_array($results);
return $result;
}
pickle | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Please use the appropriate bbCode tags when posting code blocks. If you are using PHP, use the

Code: Select all

tag. For html, use [syntax="html"]. It makes it way easier for us to see your code.

Your function is going to have problems. First off, the var $conn is unknown to the function because it is not globalized. Second, you are calling mysql_query in the function, then again in the code. You may want to change things a bit.

[syntax="php"]<?php
$sql = 'select count(*) as cnt, SUM(Score) as sum, (SUM(Score)/count(*)) as avg FROM tblChart'; 
if (!$result = mysql_query($sql)) {
    die('There was a problem with my query: ' . mysql_error());
}

while ($row = mysql_fetch_array($result)) {
    $avg=$row['avg']; 
    $cnt=$row['cnt']; 
    $sum=$row['sum']; 
    // Now echo these out ... or something
}
?>
[/syntax]
Last edited by RobertGonzalez on Thu Jul 20, 2006 10:18 am, edited 2 times in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

In your original code, the line where you assigned a value to $sum was missing a closing single-quote.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
NotOnUrNelly
Forum Commoner
Posts: 61
Joined: Wed Mar 24, 2004 4:45 pm

Post by NotOnUrNelly »

Thanks for your reply

I have added this to sum, I still cannot extract the average and sum values into the variables though there still seems to be problem with my code.
NotOnUrNelly
Forum Commoner
Posts: 61
Joined: Wed Mar 24, 2004 4:45 pm

Post by NotOnUrNelly »

Everah,

That seems to have worked, I had my database connection from the top of the page.

Many Thanks
jamie
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You're welcome. Glad I could help.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Any reason why you're not using MySQL's AVG() function? It finds averages. There's lots of cool grouping functions in MySQL..

Code: Select all

select COUNT(score) as cnt, SUM(score) as sum, AVG(score) as average, MAX(score) as maxScore, MIN(score) as minScore, STDDEV(score) as standardDeviationScore, VARIANCE(score) as varianceScore from tblChart 
Those last two are probably not going to be particularly useful unless you're analysing the results in some depth .. and they're only available in MySQL 4.1 or higher.
Post Reply