Query work with localhost and not work in live server

[thara]

Hi.. everyone!!

 

My this mysql query is working properly in localhost

 

$qry = "SELECT GROUP_CONCAT(subject.subjects SEPARATOR ', ') AS subjects
            FROM subject 
            INNER JOIN tutor_category_subject ON tutor_category_subject.subject_id = subject.subject_id 
            WHERE tutor_category_subject.tutor_id = $tutorId";

 

But it is uploaded to live server. it is not working and I can this error message.

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

can anybody tell me what has happen to this query?

 

Thank you

[mikosiko]

your live server is running with the sql mode 'ONLY_FULL_GROUP_BY' enabled... probably running in ANSI mode if the mysql version is before 5.0.3

 

the error means that you are using an aggregate function (GROUP_CONCAT()) without a GROUP BY clause.

 

more to read here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

 

and here:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

[thara]

Thanks for response.

 

Sorry I have made a mistake in my last posted query. This is my real one which I am trying .

 

				$query3 = "SELECT tutor_category_subject.subject_id, GROUP_CONCAT( DISTINCT subject.subjects SEPARATOR ', ') AS teaching_subjects  
							FROM tutor_category_subject
							INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id
							WHERE tutor_category_subject.tutor_id = $teacherId";

[DavidAM]

Yeah, I was going to say that there is noting wrong with that (first) query. If there are no non-Aggregate columns in the SELECT list, the aggregate runs across the entire result set.

 

With the second query you have a non-Aggregate column so you need a GROUP BY.

 

SELECT nameColumn, SUM(valueColumn)
FROM someTable
WHERE something = whatever
GROUP BY nameColumn;

[Barand]

As I have told you before, it makes no sense to select subject_id and GROUP_CONCAT(subjects).

 

If your looking for the subjects for a tutor, select the tutor_id and then GROUP BY tutor_id

[fenway]

I'm wondering what you actually expect to get with that query -- if mysql were to allow it -- because as Barand said, the output would be completely meaningless.