Php in MYSQL aggrate function not working
$result = mysql_query("SELECT avg(r.rate) FROM rate r where ImgName='1'");
th开发者_如何学运维is php is not working.
Originally my code is
<?php
$con = mysql_connect("localhost","root","sql");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("photogallery", $con);
$result = mysql_query("SELECT avg(r.rate) FROM rate r ");
echo "<table border='0' cellspacing='5'>";
echo "<th> Average Rating </td>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td> " . $row['rate'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
the above is not showing any out put.
but modify code i.e. then its workin.
$result = mysql_query("SELECT r.rate FROM rate r ");
but i want to aggregate function
thanks in advance
you can use an alias:
SELECT avg(r.rate) AS rate_average
FROM rate r
WHERE ImgName='1'
and then output:
echo "<td> " . $row['rate_average'] . "</td>";
Your query is producing a scalar rather than a set of rows. If you want to get the average rate per item then you should do something like:
SELECT avg(r.rate) FROM rate r GROUP BY ItemIdColumn
And yes, if you want to fetch the value by column name, you should use an alias, like knittl mentioned.
精彩评论