0

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

{exp:channel:entries
  channel="{channel}"
  limit="1"
  require_entry="yes"
  status="open"
  disable="{disable_default}"
  orderby="title"
  sort="asc"
  }

    <!-- 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;
        "}
        {row_number}
    {/exp:query}

    /

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

{/exp:channel:entries}

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.

0

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';"}
    {row_number}
{/exp:query}

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)...

Edit

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

| improve this answer | |
  • That doesn't work either.. changed the double quotes in the original query also left me with the same result. – Robbie Post Apr 29 '15 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 '15 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 '15 at 13:52
  • Also, you should use backticks in a SQL query, not quotes, right? – jrothafer Apr 29 '15 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 '15 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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