Skip to main content
R
RichyVN
Participating Frequently
March 22, 2010
Answered

Highlight last entryitem from within a query

  • March 22, 2010
  • 3 replies
  • 947 views

Hi there,

I have the following challenge :

I want to highlight the last news item(s) for today on our intranet homepage.

On that page I have created a query that shows the last 5 news items from a database, with a link to each item.

And also a link to all the other items:

Here is a sample to clearify what i mean.

latest News
19-03-2010 - news item 21 (NEW)
18-03-2010 - news item 20
15-03-2010 - news item 19
05-03-2010 - news item 18
01-02-2010 - news item 17
More news..  (Total 20 Items )

This is how it is setup right now:

<cfparam name="PageNum_NieuwsOverzicht" default="1">
<cfquery name="NieuwsOverzicht" datasource="Intranet" cachedwithin="#CreateTimeSpan(0,0,1,0)#">
SELECT NieuwsDatum, NieuwsKop, NieuwsId
FROM Nieuws
ORDER BY NieuwsDatum DESC
</cfquery>
<!---Repeat Region--->
<cfset MaxRows_NieuwsOverzicht=5>
<cfset StartRow_NieuwsOverzicht=Min((PageNum_NieuwsOverzicht-1)*MaxRows_NieuwsOverzicht+1,Max(NieuwsOverzicht.RecordCount,1))>
<cfset EndRow_NieuwsOverzicht=Min(StartRow_NieuwsOverzicht+MaxRows_NieuwsOverzicht-1,NieuwsOverzicht.RecordCount)>
<cfset TotalPages_NieuwsOverzicht=Ceiling(NieuwsOverzicht.RecordCount/MaxRows_NieuwsOverzicht)>
<cfset NieuwsId=NieuwsOverzicht.RecordCount>

How can I select (and somehow format) just the last item from within a query?

    This topic has been closed for replies.
    Correct answer mack_

    It looks like you want to highlight the first row from the query:

    <cfloop query="NieuwsOverzicht">
      #NieuwsKop# - #NieuwsDatum# - <cfif CurrentRow eq  1>(NEW)</cfif><br>
    </cfloop>

    --

    Mack

    3 replies

    R
    RichyVNAuthor
    Participating Frequently
    March 24, 2010

    Hi Guys,

    Thanks too the tips and answers here, I came up with another way to show this highlighted new item:

    I actually wanted it to be highlighted as "new" for a few days. In this example I used 7 days.

    Here what I am using at the moment:

    <cfset DateToday = CreateDate(2010,03,23)> <!--- create a date for testing purposes --->

    <cfset DateToday = Now()> <!--- use this one in the real environment --->

    <cfset NrOfDays = 7 > <!--- this var is used to setup the number of days for how long you want the "(new)" tekst to show at the end of your row--->

    <cfquery name="NieuwsOverzicht" datasource="DSN" cachedwithin="#CreateTimeSpan(0,0,1,0)#">
    SELECT NieuwsDatum, NieuwsKop
    FROM Nieuws
    ORDER BY NieuwsDatum DESC
    </cfquery>

    <cfloop query="NieuwsOverzicht" startrow="1" endrow="5"> <!--- show the last 5 items only --->
       <cfoutput>
         <cfset dagvanjaarDB = #DayOfYear(NieuwsOverzicht.NieuwsDatum)#> <!--- Set a var to hold the dayofyear number for the database date--->
         <cfset dagvanjaarNow = #DayOfYear(DateToday)#> <!--- Set a var to hold the dayofyear number for the actual date --->
         <cfset getal = #dagvanjaarNow# - #dagvanjaarDB#>  <!--- Subtract the 2 dayofyear numbers --->
          #Dateformat(NieuwsDatum)# - #NieuwsKop# <cfif #getal# LTE #NrOfDays# > (New)</cfif><br/> <!--- show date and headline and only show the new tekst when the var "getal" equals Less Then or Equal to NrofDays set at the top --->
      </cfoutput>
    </cfloop>

    I Know this is not the best way but this works for me.

    Hope someone can use this aswell.

    Regards.

    Y
    yui8979
    Inspiring
    March 24, 2010

    Hi RichyVN,

    There is a bug with your code. When it is the new year, all your news items

    from last year will be marked as (New).

    Consider :

    <cfset olddate = createdate(2009,12,12)>
    <cfset today = createdate(2010,01,01)>
    <cfset oldday = DayOfYear(olddate)>  will give you 346
    <cfset nowday = DayOfYear(today)> will give you 1

    your getal, (now - old) day will give you 1-346 which is always some

    negative value that is always LTE 7.

    Consider using the coldfusion function DateDiff @

    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_c-d_28.html

    Cheers

    R
    RichyVNAuthor
    Participating Frequently
    March 29, 2010

    Hi yui8979,

    Thanks for your input.

    You are right about that!

    What I did to solve this is the following:

    I used the DateDiff function and added and AND Statement to the cfif portion: AND #nr# GTE 0

    <cfloop query="NieuwsOverzicht" startrow="1" endrow="5">
    <cfoutput>
        <cfset dateDB = #Dateformat(NieuwsOverzicht.NieuwsDatum)#>
        <cfset dateNow = #Dateformat(DateToday)#>
        <cfset nr = DateDiff("D",DateDB,dateNow)>
        #Dateformat(NieuwsDatum)# - #NieuwsKop# <cfif #nr# LTE #NrOfDays# AND #nr# GTE 0>     (Nieuw)</cfif><br/>
    </cfoutput>
    </cfloop>

    This way It will ignore all the negative numbers when the date changes to the next year!

    But I believe it can also be done with the dayOfYear function.

    Cheers

    Richardvn

    M
    mack_Correct answer
    Participating Frequently
    March 22, 2010

    It looks like you want to highlight the first row from the query:

    <cfloop query="NieuwsOverzicht">
      #NieuwsKop# - #NieuwsDatum# - <cfif CurrentRow eq  1>(NEW)</cfif><br>
    </cfloop>

    --

    Mack

    R
    RichyVNAuthor
    Participating Frequently
    March 22, 2010

    Hi mack_

    That's exactly it!!!!

    Thanks man, Brilliant.

    Never thought it was that easy.

    Regards

    Richard

    D
    Dan_Bracuk
    Inspiring
    March 22, 2010

    There is another way that is equally easy.  Use querysetcell to append (new), plus the necessary html formatting tags to the end of the field in the first record of your query.

    Owainnorth
    Owainnorth
    Inspiring
    March 22, 2010

    If I've ever needed to do anything like this, I've avoided using <cfoutput query=""> and instead done a manual loop:

    <cfloop from="1" to="#NieuwsOverzicht.RECORDCOUNT#" index="i>

      <tr>

        <td>

          <cfoutput>#NieuwsOverzicht.NieuwsDatum#</cfoutput>

          <cfoutput>#NieuwsOverzicht.NieuwsKop#</cfoutput>

          <!--- Only output this if we're on the last/first/whatever --->

          <cfif i EQ NieuwsOverzicht.RECORDCOUNT >(NEW)</cfif>

        </td>

      </tr>

    </cfloop>

    A manual loop gives you far more control over conditional formatting, it also allows for alternate row highlights etc.

    O.

    R
    RichyVNAuthor
    Participating Frequently
    March 22, 2010

    Many thanks for the rappid reply!!

    I will have a go with this.

    Regards

    Richard

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices