Thread

I have a project using EE 1.6.7 where I’ve implemented use of some tables outside EE to store some user activity, and I’m using the Query module to fetch that stored data. Unfortunately, the query I’m using to test always returns no_results in the EE template, but when I run the exact same query in phpMyAdmin I get 2 rows. Here’s the EE code:

{exp:query sql="SELECT categoryGroup, COUNT(*) totalMetrics FROM quiz_answer_data WHERE quizID = '{segment_3}' AND userID = '{logged_in_member_id}' AND points > 0 GROUP BY categoryGroup ORDER BY categoryGroup ASC"}
{if no_results}
NO RESULTS
{/if}
{embed="assessment/.results-group" category_group="{categoryGroup}" total_metrics="{totalMetrics}"}
{/exp:query}

In my testing, I have it outputting the sql line just before this code so I can c/p the exact SELECT into phpMyAdmin.

Maybe something in my sql is a reserved word in EE? Does anyone have any tips on debugging this? I don’t really have the option of upgrading EE to 1.6.8 right now, either, unless I know for sure it would resolve this issue.

Thanks so much for the assistance.

As an update, I turned on the output of queries for super-admins, and the query from the above code is output as:

SELECT categoryGroup, COUNT(*) totalMetrics FROM quiz_answer_data WHERE quizID = ‘23’ AND userID = ‘{logged_in_member_id}’ AND points > 0 GROUP BY categoryGroup ORDER BY categoryGroup ASC

Does this mean the query is being executed before the {logged_in_member_id} variable is replaced?

Answered my own question, but here’s the solution I arrived at in case it helps anyone else. It turns out that the answer to my last question is… Yes, logged_in_member_id is not evaluated before the sql statement is run.

My new code is to change the template to allow PHP and set it to Input Stage parsing, then add the code:

<?php
global $SESS;
$member = $SESS->userdata['member_id']; 
?>
                            
{exp:query sql="SELECT categoryGroup, COUNT(*) totalMetrics FROM quiz_answer_data WHERE quizID = '{segment_3}' AND userID = '<?php echo $member; ?>' AND points > 0 GROUP BY categoryGroup ORDER BY categoryGroup ASC"}
{if no_results}
    NO RESULTS
{/if}
{embed="assessment/.results-group" category_group="{categoryGroup}" total_metrics="{totalMetrics}" user_id="<?php echo $member; ?>"}
{/exp:query}

Glad you are up and running. I will actually move this to the How to forum as your solution is more likely to help people there than here.