Highlight last entryitem from within a query

[RichyVN]

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?

[Owainnorth]

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.

[RichyVN]

Many thanks for the rappid reply!!

I will have a go with this.

Regards

Richard

[mack_ · Accepted solution]

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

[RichyVN]

Hi mack_

That's exactly it!!!!

Thanks man, Brilliant.

Never thought it was that easy.

Regards

Richard

[Dan_Bracuk]

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.

[RichyVN]

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.

[yui8979]

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

[RichyVN]

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