Pagination - How to skip a record if a certain condition is met

Question

I have various entries set to 'online' and 'offline' in the database. how do I skip the next record in a pagination setup when the next record is offline.

The CF Query works great, its the pagination that isn't working.

I have 6 rows, of which 2 are set to offline in a field so in total there are 4 rows set to online. The pagination works well, it outputs 'displaying 1 of 4 (and not 6) but looking at the results, when I click on next, it takes me to the next record even if its off or online.

So, i'm looking for a way to check the filed and ignore if its set to offline.

<cfquery name="getNews" datasource="#session.odbcname#">
SELECT  *
FROM    news
WHERE   news_id = #url.newsid#
AND     news_status = 'online'
</cfquery>

<cfif not getNews.recordcount>
<cflocation url="#defurl#" addtoken="No">
</cfif>

<cfquery name="getNewsPag" datasource="#session.odbcname#">
SELECT  *
FROM    news
WHERE   news_status = 'online'
ORDER by news_id asc
</cfquery>

<cfset perpage = 1>
<cfparam name="url.start" default="#url.newsid#">
<cfif not isNumeric(url.start) or url.start lt 1 or url.start gt getNewsPag.recordCount or round(url.start) neq url.start>
<cfset url.start = #url.newsid#>
</cfif>
<cfset totalPages = ceiling(getNewsPag.recordCount / perpage)>
<cfset thisPage = ceiling(url.start / perpage)>

<div>
<cfif getNewsPag.recordcount>
<cfoutput>#getNewsPag.recordcount#</cfoutput><br>
<div>

<p>
<cfoutput>#lang_pagination_info# #thisPage# #lang_pagination_of# #totalPages#.</cfoutput>&nbsp;
[
<cfif url.start gt 1>
<cfset link = cgi.script_name & "?newsid=" & (url.start - perpage)>
<cfoutput><a href="#link#">Previous</a></cfoutput>
<cfelse>
Previous
</cfif>
/
<cfif (url.start + perpage - 1) lt getNewsPag.recordCount>
<cfset link = cgi.script_name & "?newsid=" & (url.start + perpage)>
<cfoutput><a href="#link#">Next</a></cfoutput>
<cfelse>
Next
</cfif>

Any help would be most appreciated

Answer 1

Updated with different code

Notes

• It will not work with perpage set to > 1. This means you could change instances of perpage to 1 (and get rid of the line dividing by 1).
• It uses array functions on the column news_id to ascertain the position and pageinate based on that..
• You seem to not actually need url.start anymore. I commented out the code that uses it.
• You could cache the GetNewsPag query if you were willing too and refresh the query on pages that add/update/delete news.
• Formerly, the query getNewsPag used "Select *". I saw no other use of getNewsPag than news_id, so I changed the * the news_id.
• Using this method, you shouldn't need any "TOP 1 .... where >=" as my earlier suggestion said, so I reverted getNews to its original form with <cfqueryparam>

Hope this Helps

<!--- <cfparam name="url.start" default="#url.newsid#"> --->
<cfquery name="getNews" datasource="#session.odbcname#">
     SELECT *
       FROM news
      WHERE news_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.newsid#">
        AND news_status = 'online'
      ORDER BY news_id
</cfquery>
    
<cfif not getNews.recordcount>
    <cflocation url="#defurl#" addtoken="No">
</cfif>
    
<cfquery name="getNewsPag" datasource="#session.odbcname#">
    SELECT  news_id
    FROM    news
    WHERE   news_status = 'online'
    ORDER by news_id asc
</cfquery>

<cfset perpage = 1>

<!--- <cfset url.start="#max(val(url.newsid),val(getNews.news_id))#">
<cfif not isNumeric(url.start) or url.start lt 1 or url.start gt getNewsPag.recordCount or int(url.start) neq url.start>
    <cfset url.start = #url.newsid#>
</cfif> --->
<cfset totalPages = getNewsPag.recordCount>
<cfset thisPage = ArrayFind(getNewsPag["news_id"],url.news_id)>
    
<div>
<cfif getNewsPag.recordcount>
    <cfoutput>#getNewsPag.recordcount#</cfoutput><br>
    <div>
        <p>
            <cfoutput>#lang_pagination_info# #thisPage# #lang_pagination_of# #totalPages#.</cfoutput>&nbsp;
            [
                <cfif ArrayMin(GetNewsPag["news_id"]) lt getNews.news_id>
                    <cfset link = cgi.script_name & "?newsid=" & (GetNewsPag.news_id[ArrayFind(GetNewsPag["news_id"],getNews.news_id)+perpage])>
                    <cfoutput><a href="#link#">Previous</a></cfoutput>
                <cfelse>
                    Previous
                </cfif>
            /
                <cfif ArrayMax(GetNewsPag["news_id"]) gt getNews.news_id>
                    <cfset link = cgi.script_name & "?newsid=" & (GetNewsPag.news_id[ArrayFind(GetNewsPag["news_id"],getNews.news_id)-perpage])>
                    <cfoutput><a href="#link#">Next</a></cfoutput>
                <cfelse>
                    Next
                </cfif>
            ]

First Effort, saved for whatever potential use someone might find from it

This should be the necessary query change, it searches for the first row greater than or equal to the url.newsID.

(I actually don't work with mysql, but this is a pretty basic query. The syntax should be right, but please let me know if it did need an adjustment.)

<cfquery name="getNews" datasource="#session.odbcname#">
 SELECT TOP 1 *
   FROM    news
  WHERE   news_id >= #url.newsid#
    AND     news_status = 'online'
  ORDER BY news_id
</cfquery>

You'll need to change some of your pagination to use getNews.news_id rather than url.newsid.

<cfparam name="url.start" default="#max(val(url.newsid),val(getNews.news_id))#">

This line may help you get there. If url.start is not set, it finds the greater (integer) argument between url.newsID, and getNews.news_ID. The val() functions just make sure the passed arguments are both numbers, or convert it to a zero if not. Handy if, say, getnews.news_id returned 0 results.

About <cfqueryparam>

At this moment, your query (in your Question) is especially hackable.

If I change the url so that it reads news.cfm?newsid=4; DROP TABLE News, your news table is gone. I could then target other common table names like Users or Members. Now, I wouldn't do this, but it's an easy target for a malicious user.

Good news, there's an easy fix: <cfqueryparam> I"ll let you read up about the tag, but I'll fix this query for you. CFQUERYPARAM should be in literally every query that takes #variable# input of any type, in any place that it does.

Because your other query uses no #variable# input, it's not susceptible to any kind of injection attack.

String values input from variables are just as vulnerable.

Any case where you use cfqueryparam, no quotes should be placed around the tag. The tag discerns whether quotes are needed from the cfsqltype.

And yes, a person could still change the url as I did above, but all they would do is get a page error, and the sql script would have no effect. In the case of a string, they wouldn't get an error, but they'd get incorrect results.

<cfquery name="getNews" datasource="#session.odbcname#">
 SELECT *
   FROM    news
  WHERE   news_id >= <cfqueryparam cfsqltype="cf_sql_integer" value="#url.newsid#">
    AND     news_status = 'online'
  ORDER BY news_id
</cfquery>

Edit: And yes, it's no coincidence that my s/n is cfqueryparam. It's such an important issue that I tend to illustrate it frequently. CFqueryparam is a wonderful tool and should (must) be used to its full effect.