No Repeat Region

[Newsgroup_User]

I invoked a CFC to give me the results of a query inside the CFC. How to I
make it so only the newest record shows and not all the results? Do I have
to set the repeat region manually to only show one or is there something I
can add to the <cfoutput query="news">?

<cfoutput query="news">
<tr>
<td class="maintext_bold">#DateFormat(news.Date,"mm/
dd/ yyyy")#</td>
</tr>
<tr>
<td class="maintext">#news.Story#</td>
</tr>
</cfoutput>

--
Wally Kolcz
Developer / Support

[BKBK]

You could put your displaying code within

<cfif news.Date GT someDate>
<!--- display news --->
</cfif>

If showing the newest result is all you need, then it might be more efficient to do that already at the query. For example, by selecting news "where date > #someDate#".

[BKBK]

?? You lost me on that one ??

I made two suggestions. Other posters have in fact repeated them.

1) Get all news, but display just the new news (inefficient if this is all there is to it)

<cfset someDate = createdate(2006,4,22)>
<cfoutput query="news">
<cfif news.Date GT someDate>
<tr>
<td class="maintext_bold">#DateFormat(news.Date,"mm/
dd/ yyyy")#</td>
</tr>
<tr>
<td class="maintext">#news.Story#</td>
</tr>
</cfif>
</cfoutput>

2) (a) code the query to select the latest news since a given date

<cfset someDate = createdate(2006,4,22)>
<cfquery name="news" datasource="#Request.MainDSN#">
SELECT News_ID, Story,Date
FROM ms411.news
WHERE Date > #createodbcdatetime(someDate)#
ORDER BY Date DESC
</cfquery>

(b) code the query to select the latest news item (more or less Dan Bracuk's suggestion)

<cfquery name="news" datasource="#Request.MainDSN#">
SELECT News_ID, Story,Date
FROM ms411.news
WHERE Date = (SELECT MAX(date) FROM ms411.news )
</cfquery>

[Newsgroup_User]

Maybe I am wrong about your post..but there is NO comparison date. The site
news may be updated once a day, once every 6 months, or once every 16 years.
I just need the last item shown.

However it has been solved using a MySQL SQL string.

Thanks.

[BKBK]

I just need the last item shown
I actually showed you an example of code "to select the latest news item" - 2(b). In any case, it's nice you've found a solution.

[Newsgroup_User]

?? You lost me on that one ??

It pulls the correct record, but the code wants to create a repeat region
for all the records. i only want it to show the latest record since it is
pulling decendingly.

Did I miss something?

[Dan_Bracuk]

If you have control over the cfc, enhance it so that it gives you what you need. Otherwise, do a query of queries on your return variable.

[Newsgroup_User]

I am new to components. Here is what I have.

<cfcomponent>
<cffunction name="LatestNews" access="public" returntype="query">
<cfquery name="news" datasource="#Request.MainDSN#">
SELECT News_ID, Story,Date FROM ms411.news ORDER BY Date DESC
</cfquery>
<cfreturn news>
</cffunction>
</cfcomponent>

How can I get that to show 1 result (the newest)?

[Dan_Bracuk]

To get the most recent record, do this

select News_ID, Story, Date
FROM ms411.news
where somefield = (select max(somfield) from ms411.news)

Somefiled can be either news_id or date, depending on whether you want the last record added, or the one with the most recent date.

[Newsgroup_User]

Nothing to do with being a component. Just put a where clause in your
query so that it only returns the one record you want to display.

If you want to to do both, then use <cfarguments...> to pass is a
parameter to dynamically determine when to return one record and when to
return many.

Wally Kolcz wrote:
> I am new to components. Here is what I have.
>
> <cfcomponent>
> <cffunction name="LatestNews" access="public" returntype="query">
> <cfquery name="news" datasource="#Request.MainDSN#">
> SELECT News_ID, Story,Date FROM ms411.news ORDER BY Date DESC
> </cfquery>
> <cfreturn news>
> </cffunction>
> </cfcomponent>
>
> How can I get that to show 1 result (the newest)?
>
>

[Newsgroup_User]

What do you put in a WHERE clause to get it to only show the newest result?

The news is not time sensitive so I cannot compare it to anything. The
client may only put one news update every 60 day or 6 years.

[Newsgroup_User]

So then you just want one record returned.

SELECT TOP 1 works for MS databases, I'm not sure for Oracle and mySQL
or others.

Wally Kolcz wrote:
> What do you put in a WHERE clause to get it to only show the newest result?
>
> The news is not time sensitive so I cannot compare it to anything. The
> client may only put one news update every 60 day or 6 years.
>
>

[Newsgroup_User]

Yeah, I am using MySQL 5

[Newsgroup_User]

A quick goggle looks like for MySql it is limit

SELECT *
FROM aTable
LIMIT 1

Here are other dbms versions that may also work.

--SQL:
TOP 3 * from mytable

--ACCESS:
select top 3 * from (select * from mytable)

--ORACLE:
ROWID <=3

Wally Kolcz wrote:
> Yeah, I am using MySQL 5
>
>

[Newsgroup_User]

Thanks. The LIMIT 1 works fine if you put it on the very end. I put is
before the ORDER BY and it blew up.

[ghartong]

why not just do it in the <cfoutput>

<cfoutput query="whatever" startrow="#recordcount# maxrows="1">
</cfoutput>

That will give you last record of query.

[Dan_Bracuk]

quote:

---

Originally posted by: ghartong
why not just do it in the <cfoutput>
<cfoutput query="whatever" startrow="#recordcount# maxrows="1">
</cfoutput>
That will give you last record of query.

---

Because it is very inefficient.