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!
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;
}
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]
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.
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.