Thread

I’m using the following code to display a custom list of channel entries:

{exp:query limit="20" paginate="bottom" sql="SELECT entry_id FROM exp_channel_titles WHERE entry_id NOT IN([...list of entry IDs…]) AND channel_id = '1' ORDER BY entry_id DESC"}
   {exp:channel:entries channel="XYZ" entry_id="{entry_id}"}
      ...stuff…
   {/exp:channel:entries}

   {paginate}...{/paginate}

{/exp:query}

The query returns the expected results, but the number of results per page varies. In my code snippet above, I have the limit set to 20, but on most of the pages I get fewer than 20 results per page. Put another way, I should have 11 pages of results, with 20 per page (excepting the last page), but instead I have 14 pages of results, with variable numbers of results per page.

It’s like the entries I don’t want to show are still “there” for pagination purposes, even though they are neither pulled from the database nor displayed on the page.

How can I adjust my code to display the full 20 results per page?

Hi Ryan,

Can I asked why you are not using the regular entry_id= parameter to do this?

{exp:channel:entries channel="XYZ" entry_id="not 45|534|807"}
      ...stuff…
   {/exp:channel:entries}

John, good question. Your question made me think of something, which I’ll present below.

This is a rather custom implementation that works like this:

- I have a custom lookup table that tracks the IDs of questions that users have already answered, by matching a question_id with a member_id. The question_id is the same as the entry_id in exp_channel_titles.
- To show a list of answered questions, I can just pull the IDs from this lookup table
- To show a list of unanswered questions, I have to pull all questions, then exclude those question IDs that are present in the lookup table. That’s what my query above is trying to do.

I suppose I could grab a list of question IDs from the lookup table (the answered questions), then assign the results to a string or array, then do the following?

{exp:channel:entries channel="XYZ" entry_id="not <?php echo $excluded_ids; ?>"}
   ...stuff…
{/exp:channel:entries}

I haven’t tried this yet (will do) but I suspect parse order might require me to embed something.

That sounds reasonable; keep us posted!

Thanks for the help guys—you got my gears turning, and the following does exactly what I need:

<?php
   /* Grab all custom IDs we want to exclude, then create a string of those                 
      custom IDs to exclude from the exp:channel:entries tag.
      */

   $answered = '';
   $query = mysql_query ( "SELECT custom_id FROM exp_my_custom_table WHERE member_id = '" . $this->EE->session->userdata['member_id'] . "' ORDER BY custom_id" );

   while ( $row = mysql_fetch_assoc ( $query ) ) {
      $answered .= $row['custom_id'] . '|';
   }

   // remove trailing pipe
   $answered = substr ( $answered, 0, -1 );
?>

   {exp:channel:entries channel="XYZ" entry_id="not <?php echo $answered; ?>" limit="20" paginate="bottom"}
      ...stuff…
   {/exp:channel:entries}

I don’t know if it’s good practice to use PHP like that in a template, but it’s the only way to query the database, get a value in a PHP var, and then use that PHP var in the exp:channel:entries tag without using an embed.

PHP parsing must be set to input for the above to work.

Hi Ryan,

Glad that you found a way forward. The only comment I would make is that you might want to use the CodeIgniter Active Record functions instead of PHP MySQL functions. The only change you would have to make to those listed in the CI documentation would be to access them via the EE super global like this:

$this->EE->db->where('member_id', $this->EE->session->userdata['member_id'])'
...etc

Though, what you’ve done will work as well 😊

Cheers

Greg

Greg,

Thanks for the tip. Would you mind explaining why the Active Record (AR) method is preferred over PHP/MySQL? I see a note about things being escaped automatically using the AR method; are there other reasons? I’d like to be doing things as best I can 😊

If I understand you right, I’d rewrite the above like this:

$query = $this->EE->db->where('member_id', $this->EE->session->userdata['member_id']);

foreach ( $query->result() as $row ) {
   $answered .= $row->member_id . '|';
}

Thanks in advance for your help.

P.S. This seems related, so I thought I’d ask here. There is an Active Record plugin that lets you query the DB using the AR method. What is the benefit to using this over the {exp:query} module?

Hi Ryan,

Active Record is a more well-defined pattern, and the automatic escaping is always good 😉  Either will work and I suppose there is some personal preference mixed in. I have not used that AR plugin myself, but it looks pretty cool.

Cool. Thanks Brandon. Feel free to close this one out!

Closing this one out…