Sucao
asked on
MySQL MAX(), AVG(), and MIN() not working in PHP
Hey,
Whenever I run this script something is going wrong because the max(), avg(), and min() values are coming up as 0. I have run is_null() on all of them and they are not NULL. They are just coming up as 0, but they shouldn't be. 1 vote has been "4" and 1 vote has been "2" so I should be coming up with an avg of 3, max of 4 and min of 2. I can only get COUNT() to work in the statement below, which is giving me the correct answer of 2.
Thanks. Any help is appreciated.
Whenever I run this script something is going wrong because the max(), avg(), and min() values are coming up as 0. I have run is_null() on all of them and they are not NULL. They are just coming up as 0, but they shouldn't be. 1 vote has been "4" and 1 vote has been "2" so I should be coming up with an avg of 3, max of 4 and min of 2. I can only get COUNT() to work in the statement below, which is giving me the correct answer of 2.
Thanks. Any help is appreciated.
$get_advice_sql = "SELECT a.ID, a.person_ID, a.type, a.keywords, a.title, a.information, a.effect_start, Count(v.vote), Avg(v.vote), Max(v.vote), Min(v.vote), u.clsUserName
FROM articles as a
LEFT JOIN USERTABLE as u ON u.clsUserID = a.person_id
LEFT JOIN votes as v ON a.ID = v.article_ID
WHERE a.type = 'advice'
AND a.ID = (SELECT max(ID)
FROM articles
WHERE type = 'advice')
GROUP BY a.ID";
if ($result = mysqli_query($mysqli, $get_advice_sql)) {
while ($row = mysqli_fetch_row($result)) {
$advice_id = $row[0];
$title = $row[4];
$information = $row[5];
$effect_start = $row[6];
$votes_count = $row[7];
$votes_average = $row[8];
$votes_max = $row[9];
$votes_min = $row[10];
$author = $row[11];
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.