16 Replies Latest reply on Jul 8, 2006 12:08 PM by BKBK

    No Repeat Region

    Level 7
      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


        • 1. Re: No Repeat Region
          BKBK Adobe Community Professional & MVP
          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#".



          • 2. Re: No Repeat Region
            Level 7
            ?? 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?


            • 3. Re: No Repeat Region
              Dan Bracuk Level 5
              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.
              • 4. Re: No Repeat Region
                Level 7
                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)?


                • 5. Re: No Repeat Region
                  Level 7
                  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)?
                  >
                  >
                  • 6. Re: No Repeat Region
                    Level 7
                    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.


                    • 7. Re: No Repeat Region
                      Level 7
                      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.
                      >
                      >
                      • 8. Re: No Repeat Region
                        Level 7
                        Yeah, I am using MySQL 5


                        • 9. Re: No Repeat Region
                          Level 7
                          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
                          >
                          >
                          • 10. Re: No Repeat Region
                            Dan Bracuk Level 5
                            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.
                            • 11. Re: No Repeat Region
                              Level 7
                              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.


                              • 12. Re: No Repeat Region
                                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.
                                • 13. Re: No Repeat Region
                                  Dan Bracuk Level 5
                                  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.
                                  • 14. No Repeat Region
                                    BKBK Adobe Community Professional & MVP
                                    ?? 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>



                                    • 15. Re: No Repeat Region
                                      Level 7
                                      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.


                                      • 16. Re: No Repeat Region
                                        BKBK Adobe Community Professional & MVP
                                        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.