Trying to produce a current entry count / total entries within a channel_entry tag by using a SQL query.


    <!-- entry number (row number) -->
    {exp:query sql="
        SET @row_number:=0;
        SELECT @row_number:=@row_number+1 AS row_number,title FROM exp_channel_titles
        WHERE channel_id = 10 AND status ="open"
        ORDER BY title;


    <!-- total entries -->        
    {exp:stats channel="{channel"}{total_results}{/exp:stats}


The Query works perfect while running it directly on the DB in the Navicat query editor. However it doesn't run (outputs nada) from within the template.

1 Answer 1


Could you not use a simpler query? I notice that your query is actually 2 queries (note, the 2 semi-colons)...

Does the following work (done using MySql native) :

{exp:query sql="SELECT COUNT(*) AS row_number FROM exp_channel_titles WHERE channel_id = 10 AND status ='open';"}

Also, you've got some double quote hell going on, use double quote for the EE attribute and single quote inside the query (as per example)...


Seems I'm also missing the 'FROM' clause in my example, I have corrected the above.

  • That doesn't work either.. changed the double quotes in the original query also left me with the same result. Apr 29, 2015 at 13:31
  • Ok, let me try this out on my sandbox and update my answer, you're using the latest and greatest EE right? I see you're using NaviCat which Im not familiar with, but this is esentually a workbench for MySql isn't it?
    – Blatant
    Apr 29, 2015 at 13:47
  • Hi Robbie, I've edited my example above, I forgot the FROM clause (face-palm), I tried this exact example out on my EE install (EE2.8.3) and it works fine for me. Can you try with the single query + From clause?? Also, you're DB prefix is 'out of the box' right? i.e. exp_ ??
    – Blatant
    Apr 29, 2015 at 13:52
  • Also, you should use backticks in a SQL query, not quotes, right?
    – jrothafer
    Apr 29, 2015 at 13:53
  • isn't it backticks around db/table/schema names and single quotes around string literals?? in the example the only quote we're using is to describe the string literal to compare statuses, ergo single straight quotes right?
    – Blatant
    Apr 29, 2015 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.