Hi!

I’m wondering if someone can help me with a MySQL query for getting the top 10 best rated movies for an app I’m working on.

The DB fields are:

movie_id
movie_title
user_id
points

I’m trying with this:

but is not working, it shows the this error:

An Error Was Encountered

Error Number: 1111

Invalid use of group function

SELECT MAX(SUM(points)/COUNT(user_id)) FROM peliculas LIMIT 10

Can someone help me with this query? Thx

try adding a GROUP BY movie_id

try adding a GROUP BY movie_id

is still the same error

and using:

I’m getting this error:

A PHP Error was encountered

Severity: Notice

Message: ob_end_clean() [ref.outcontrol]: failed to delete buffer. No buffer to delete.

Filename: libraries/Loader.php

Line Number: 648

This should work:

Bart - 18 November 2006 08:47 PM

This should work:

SELECT movie_id, (SUM(points) / COUNT(*)) AS average
FROM movies
GROUP BY movie_id
ORDER BY average DESC
LIMIT 10;

with that query I’m getting this same error:

A PHP Error was encountered

Severity: Notice

Message: ob_end_clean() [ref.outcontrol]: failed to delete buffer. No buffer to delete.

Filename: libraries/Loader.php

Line Number: 648

I think it can be a CI problem

Yes, that is not an SQL error.

nooby, your SELECT isn’t returning movie_title, so you should be getting an error along the lines of “$row->movie_title is undefined”.
If fixing your SELECT gets your desired result, then we’re in similar territory to this thread, whereby a bad or missing variable doesn’t generate the expected error type.

I responded to a post you made in the bug forum.  I think you’re seeing “ob_end_clean() [ref.outcontrol]: failed to delete buffer. No buffer to delete.”  Because you have compress_output set to true.  There actually is another error in there, but the compress output is getting triggered first.

Martin Hall - 19 November 2006 06:36 AM

nooby, your SELECT isn’t returning movie_title, so you should be getting an error along the lines of “$row->movie_title is undefined”.
If fixing your SELECT gets your desired result, then we’re in similar territory to this thread, whereby a bad or missing variable doesn’t generate the expected error type.

Why you think isn’t returning movie_title? The error is in the query, if I change the query to something simple like “SELECT * FROM peliculas LIMIT 10” it works OK

[quote author=“Derek Allard”]I responded to a post you made in the bug forum.  I think you’re seeing “ob_end_clean() [ref.outcontrol]: failed to delete buffer. No buffer to delete.” Because you have compress_output set to true.  There actually is another error in there, but the compress output is getting triggered first.

$config[‘compress_output’] is setted to false, so the error is not because of that, I think is because CI can not handle that kind of query

Why you think isn’t returning movie_title? The error is in the query, if I change the query to something simple like “SELECT * FROM peliculas LIMIT 10” it works OK

Actually, Martin seems to be right, based on what you posted.  Here is your SQL

notice that movie_title isn’t in there?  What if you make it

Oh you’re right! That’s the error in the SQL query!

Thank you very much for helping!