mysql query syntax error

Question

I want to pull only the quizzes where the maxscore = score in the below query, can you please tell me what is wrong on the syntax?

$database->setQuery('SELECT distinct qui.title AS name,' .
        ' ( SELECT GROUP_CONCAT(profiles.title) 
              FROM #__jquarks_users_profiles AS users_profiles
              LEFT JOIN #__jquarks_profiles AS profiles ON users_profiles.profile_id = profiles.id
              WHERE users_profiles.user_id = sessionWho.user_id ) AS profile, ' .
        ' ( SELECT sum(score)  
              FROM #__jquarks_quizzes_answersessions
              WHERE quizsession_id = quizSession.id
              AND status <> -1 ) AS score,' .
        ' ( SELECT count(distinct(question_id))
              FROM #__jquarks_quizzes_answersessions 
              WHERE quizsession_id = quizSession.id ) AS maxScore,' .

            ' ( SELECT count(id)
              FROM #__jquarks_quizzes_answersessions 
              WHERE status=-1
              AND quizsession_id = quizSession.id ) AS evaluate,' .
' quizSession.finished_on,sessionWho.email' .     
        ' FROM #__jquarks_quizsession AS quizSession' .
        ' LEFT JOIN #__jquarks_users_quizzes AS users_quizzes ON users_quizzes.id = quizSession.affected_id' .
        ' LEFT JOIN #__jquarks_quizzes AS qui ON users_quizzes.quiz_id = qui.id' .
        ' LEFT JOIN #__jquarks_quizzes_answersessions AS quizSessAns ON quizSessAns.quizsession_id = quizSession.id' .
        ' LEFT JOIN #__jquarks_sessionwho AS sessionWho ON sessionWho.session_id = quizSession.id' .
        ' LEFT JOIN #__jquarks_users_profiles AS users_profiles ON users_profiles.user_id = sessionWho.user_id' .
' LEFT JOIN #__jquarks_profiles AS profiles ON profiles.id = users_profiles.profile_id '.

' WHERE sessionWho.user_id =' .$id  AND score = maxScore) ;

can you go straight to phpmyadmin and check the sub-queries one by one? — WooDzu
– WooDzu, Commented Aug 13, 2011 at 15:17

GolezTrol · Accepted Answer · 2011-08-12 23:49:05Z

2

The last part, AND score = maxScore is not put in quotes. This should also be part of your SQL string.

You can easily see this even in the highlighting that is applied to the code in your question, but certainly the highlighter of your editor should reveil it too. Do you use an editor like NetBeans or even Notepad++?

$database->setQuery('SELECT distinct qui.title AS name,' .
        ' ( SELECT GROUP_CONCAT(profiles.title) 
              FROM #__jquarks_users_profiles AS users_profiles
              LEFT JOIN #__jquarks_profiles AS profiles ON users_profiles.profile_id = profiles.id
              WHERE users_profiles.user_id = sessionWho.user_id ) AS profile, ' .
        ' ( SELECT sum(score)  
              FROM #__jquarks_quizzes_answersessions
              WHERE quizsession_id = quizSession.id
              AND status <> -1 ) AS score,' .
        ' ( SELECT count(distinct(question_id))
              FROM #__jquarks_quizzes_answersessions 
              WHERE quizsession_id = quizSession.id ) AS maxScore,' .

            ' ( SELECT count(id)
              FROM #__jquarks_quizzes_answersessions 
              WHERE status=-1
              AND quizsession_id = quizSession.id ) AS evaluate,' .
' quizSession.finished_on,sessionWho.email' .     
        ' FROM #__jquarks_quizsession AS quizSession' .
        ' LEFT JOIN #__jquarks_users_quizzes AS users_quizzes ON users_quizzes.id = quizSession.affected_id' .
        ' LEFT JOIN #__jquarks_quizzes AS qui ON users_quizzes.quiz_id = qui.id' .
        ' LEFT JOIN #__jquarks_quizzes_answersessions AS quizSessAns ON quizSessAns.quizsession_id = quizSession.id' .
        ' LEFT JOIN #__jquarks_sessionwho AS sessionWho ON sessionWho.session_id = quizSession.id' .
        ' LEFT JOIN #__jquarks_users_profiles AS users_profiles ON users_profiles.user_id = sessionWho.user_id' .
' LEFT JOIN #__jquarks_profiles AS profiles ON profiles.id = users_profiles.profile_id '.

' WHERE sessionWho.user_id =' .$id . ' AND score = maxScore') ;

PS: Do your table names actually contain a #? Maybe you should use back-quotes around those table names then, like:

LEFT JOIN `#__jquarks_users_profiles`

answered Aug 12, 2011 at 23:49

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tony77 Over a year ago

thanks for your help GolezTrol, would kindly help, now i get and error stating:Warning: Invalid argument supplied for foreach()

Tony77 Over a year ago

code that outputs the query:

echo '<table style="' . $tableStyle . '" cellpadding="7" cellspacing="7">';  echo "<tr> <th> Quiz Title </th><th> Score </th><th>Maximum Score </th><th> Unanswered </th> <th>Finished On </th></tr>";   $row = $database->loadRowList(); foreach($row as $valuearray) { echo '<tr style=" align="center">'; foreach($valuearray as $field) {  echo "<td>$field</td>"; } echo "</tr>"; } echo "</table>";  ?>

ypercubeᵀᴹ Over a year ago

score and maxScore are not fields but evaluated columns. they can't be put in WHERE clause.

Tony77 Over a year ago

so what can i do instead, I only need to render the score which is equal to the max score from all the scores, how can change the code to do this , meabe a variable?

GolezTrol Over a year ago

@TonyR, that feels like a new question. Maybe you should post it as such, instead of pasting that code in a comment which renders it completely unreadable.

ypercubeᵀᴹ · Accepted Answer · 2011-08-15 21:17:44Z

1

$database->setQuery(
    "SELECT *
     FROM
     ( SELECT distinct qui.title AS name,
          ( SELECT GROUP_CONCAT(profiles.title) 
              FROM #__jquarks_users_profiles AS users_profiles
              LEFT JOIN #__jquarks_profiles AS profiles ON users_profiles.profile_id = profiles.id
              WHERE users_profiles.user_id = sessionWho.user_id ) AS profile, 
          ( SELECT sum(score)  
              FROM #__jquarks_quizzes_answersessions
              WHERE quizsession_id = quizSession.id
              AND status <> -1 ) AS score,
          ( SELECT count(distinct question_id)
              FROM #__jquarks_quizzes_answersessions 
              WHERE quizsession_id = quizSession.id ) AS maxScore,   
          ( SELECT count(id)
              FROM #__jquarks_quizzes_answersessions 
              WHERE status=-1
              AND quizsession_id = quizSession.id ) AS evaluate,
          quizSession.finished_on, 
          sessionWho.email     
       FROM #__jquarks_quizsession AS quizSession
         LEFT JOIN #__jquarks_users_quizzes AS users_quizzes ON users_quizzes.id = quizSession.affected_id
         LEFT JOIN #__jquarks_quizzes AS qui ON users_quizzes.quiz_id = qui.id
         LEFT JOIN #__jquarks_quizzes_answersessions AS quizSessAns ON quizSessAns.quizsession_id = quizSession.id
         LEFT JOIN #__jquarks_sessionwho AS sessionWho ON sessionWho.session_id = quizSession.id
         LEFT JOIN #__jquarks_users_profiles AS users_profiles ON users_profiles.user_id = sessionWho.user_id
         LEFT JOIN #__jquarks_profiles AS profiles ON profiles.id = users_profiles.profile_id 
       WHERE sessionWho.user_id = " . $id . 
    ")
     WHERE score = maxScore" ) ;

edited Aug 15, 2011 at 21:17

answered Aug 13, 2011 at 0:34

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

2 Comments

Tony77 Over a year ago

hi thanks for your output,, i receive again.. Warning: Invalid argument supplied for foreach()

ypercubeᵀᴹ Over a year ago

@TonyR: Have you tried the query in PHPMyAdmin (to be sure that it at least that is OK)? If the query is fine, then the problem is probably in your PHP code. But you haven't posted that. Edit your question and add the PHP code. I see no foreach() in the code you have posted.

Collectives™ on Stack Overflow

mysql query syntax error

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related