Page 1 of 1

PHP SQL Averages

Posted: Thu Jul 20, 2006 9:54 am
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]

Posted: Thu Jul 20, 2006 10:13 am
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]

Posted: Thu Jul 20, 2006 10:14 am
by pickle
In your original code, the line where you assigned a value to $sum was missing a closing single-quote.

Posted: Thu Jul 20, 2006 10:19 am
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.

Posted: Thu Jul 20, 2006 10:24 am
by NotOnUrNelly
Everah,

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

Many Thanks
jamie

Posted: Thu Jul 20, 2006 10:36 pm
by RobertGonzalez
You're welcome. Glad I could help.

Posted: Fri Jul 21, 2006 3:28 am
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.