5 Replies Latest reply on May 7, 2007 11:25 AM by namtax

    grouping output

    namtax Level 1
      hi i have an update page, which shows all the activity of your friends on my website..

      here it is

      http://www.musicexplained.co.uk/delete/updates.cfm?u=namtax&friends"

      at the moment its displaying what your friends have done at any date....so you see for example

      artists your friends have added to the database

      gas added Whitehouse to the musicexplained database on 18-Apr-07

      blanko added to the musicexplained database on 21-Apr-07

      stag added atest to the musicexplained database on 22-Apr-07

      what i would like to achieve...is to group the activity by date...so instead you would get


      artists your friends have added to the database

      18/April/07

      gas added Whitehouse to the musicexplained database

      21/April/07

      blanko added to the musicexplained database

      22/Apr/07

      stag added atest to the musicexplained database

      i have tried to group the output of the query, so you get

      <cfoutput query="friends_activity" group="date_fav(date the person added an artist as a friend)">
      on #DATEFORMAT(date_fav)#

      <p>
      <cfoutput>
      #name_usr#(user) added
      #name_art#(artist they added to their favourites) to their favourites
      </cfoutput>
      </p>
      </cfoutput>

      but the info is not grouping by day properly..

      the issue i think, is that the date info initially being input into the database contains the time as well as the date, so the date_fav column im trying to group by isnt grouping by day like i want it to...its grouping by the date and the time...

      is there anyway i can group by the day itself not the time...

      i hope this makes sense..

      any help would be appreciated
        • 1. Re: grouping output
          Dan Bracuk Level 5
          What does the order by clause in your query look like?
          • 2. Re: grouping output
            namtax Level 1
            hi dan, this is my current query

            <cfquery name="art_fav" datasource="#application.datasource#">
            SELECT name_usr, name_art, date_fav
            FROM (user_usr
            LEFT OUTER JOIN artists_fav
            ON user_usr.id_usr = artists_fav.id_usr_fav)
            LEFT OUTER JOIN artist_art
            ON artist_art.id_art = artists_fav.id_artkey_fav
            WHERE id_usr_fav IN
            (SELECT id_usr_friend
            FROM user_friend
            WHERE id_main_friend = #session.auth.idnumber#
            AND update_friend = 1)
            AND id_usr <> #session.auth.idnumber#
            ORDER BY date_fav
            </cfquery>

            thanks again
            • 3. Re: grouping output
              Dan Bracuk Level 5
              You probably identified the cause correctly when you mentioned the time portion of the date field. Many dbs have functions that convert dates to text. An example would be oracle's to_char function.
              • 4. Re: grouping output
                craigkaminsky Level 3
                Along the lines of Dan's last post, here's a Transact SQL statement you can run in a select clause to

                SELECT CAST(date_fav AS smalldatetime) as mydate

                One quick note, if you don't need to record the time in your date column, you can use the SQL Server data type: smalldatetime. This records only the date and not the time (i.e., Jan 1 2007).

                • 5. Re: grouping output
                  namtax Level 1
                  ok thanks
                  i will have a look at a similar function for my mysql database

                  cheers