Professional Web Applications Themes

RecordCount & Grouping, I think? - Coldfusion Database Access

Hi all, I'm hoping someone here will be able to help me out with a little query that I've not been able to get working (and I've asked on two other CF forums). What I would like to achieve is to do a recordcount of the photo's that are contained within each album. The current Access DB contains the following two tables: tbl_albums: album_id | album_title | album_description tbl_photos: photo_id | photo_album | ... What exactly does the query need to be? This is what I've tried so far, but it's not quite working: <cfquery name="q_get_albums_item" datasource="#application.dsn#"> SELECT tbl_albums.album_id, tbl_albums.album_title, ...

  1. #1

    Default RecordCount & Grouping, I think?

    Hi all,

    I'm hoping someone here will be able to help me out with a little query that
    I've not been able to get working (and I've asked on two other CF forums).

    What I would like to achieve is to do a recordcount of the photo's that are
    contained within each album. The current Access DB contains the following two
    tables:

    tbl_albums:

    album_id | album_title | album_description

    tbl_photos:

    photo_id | photo_album | ...

    What exactly does the query need to be? This is what I've tried so far, but
    it's not quite working:

    <cfquery name="q_get_albums_item" datasource="#application.dsn#">
    SELECT tbl_albums.album_id, tbl_albums.album_title, count(tbl_photos.photo_id)
    AS photo_count
    FROM tbl_albums, tbl_photos
    WHERE tbl_albums.album_id = tbl_photos.photo_album
    GROUP BY tbl_albums.album_id, tbl_albums.album_title
    </cfquery>

    The result of the above is the same number when used as shown below:

    <table border="0" cellpadding="0" cellspacing="5" width="600">
    <tr>
    <th>Album</th>
    <th>Description</th>
    <th>Photos</th>
    </tr>
    <cfloop query="q_get_albums">
    <tr valign="top">
    <td><p><a
    href="photos.cfm?aid=<cfoutput>#album_id#</cfoutput>"><cfoutput>#album_title#</c
    foutput></a></p></td>
    <td><p><cfoutput>#album_description#</cfoutput></p></td>
    <td><p><cfoutput>#q_get_albums_item.photo_count# </cfoutput></p></td>
    </tr>
    </cfloop>
    </table>

    I hope someone can point me in the right direction!

    Many thanks in advance for any replies received.


    neilsytner Guest

  2. #2

    Default Re: RecordCount & Grouping, I think?

    Hi

    Can you post your error please.?

    I think in the query you have missed the dexcription field, is that is the
    error you are getting.

    SELECT tbl_albums.album_id, tbl_albums.album_title,album_description,
    count(tbl_photos.photo_id) AS photo_count
    FROM tbl_albums, tbl_photos
    WHERE tbl_albums.album_id = tbl_photos.photo_album
    GROUP BY tbl_albums.album_id, tbl_albums.album_title,album_description


    try to post your error if it not the above.

    vkunirs Guest

  3. #3

    Default Re: RecordCount & Grouping, I think?

    You didn't say what was wrong with the current query.

    Anyway, the attached version might work better for you.

    -- MikeR



    <CFQUERY name="q_get_albums_item" datasource="#application.dsn#">
    SELECT
    tbl_albums.album_id,
    tbl_albums.album_title,
    COUNT (tbl_photos.photo_id) AS photo_count
    FROM
    tbl_albums
    LEFT JOIN
    tbl_photos ON tbl_photos.photo_album = tbl_albums.album_id
    GROUP BY
    tbl_albums.album_id,
    tbl_albums.album_title
    ORDER BY
    tbl_albums.album_title
    </CFQUERY>

    MikerRoo Guest

  4. #4

    Default Re: RecordCount & Grouping, I think?

    there's no cf error - it just displays the record count as 18 for each album,
    and there's actually 4, 10 & 18 in each respective category.

    i'll try out both of your suggestions.

    thanks for the replies :)

    neilsytner Guest

  5. #5

    Default Re: RecordCount & Grouping, I think?

    OK MikerRoo - your query did the job! many thanks!

    could you explain to me why it worked, so that i can now learn how the query
    worked? this would really help me (and others i'm sure) for future reference.

    much appreciated.

    neilsytner Guest

  6. #6

    Default Re: RecordCount & Grouping, I think?

    OK mark this question as answered and I'll give a brief rundown (it's past my
    bedtime).

    Also Access is quirky and I haven't used it for a long time, so I may get some
    of the details slightly off...

    Anyway, your code would probably work if you defined a relationship in Access
    (Primary key, Foreign key index).

    If you DON'T have a built in relationship, Access queries seem to need an
    explicit join (the join statment). You can change the "LEFT JOIN" to "INNER
    JOIN" and it will work with your current data set.

    But suppose you add a new album and it doesn't yet have any photos. Your
    original code and the "inner join" would not fetch that album at all.

    Left join includes it and the count yields zero just like it should.

    So, you can look up joins and learn more.

    I suggest you also look up relationships in Access help and build them up in
    your table. Relationships and indexes speed things up and stop bad data
    before it can be entered.

    Cheers,
    -- MikeR

    MikerRoo Guest

  7. #7

    Default Re: RecordCount & Grouping, I think?

    thanks mikeroo - your answer, explantion and time are all great appreciated! i shall mark this as answered :)
    neilsytner Guest

Similar Threads

  1. Dynamic RecordCount
    By H3ath0r in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: June 13th, 08:43 PM
  2. recordCount property
    By fu-meng in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: May 16th, 03:48 PM
  3. Getting a recordcount
    By Andy Levy in forum PHP Development
    Replies: 3
    Last Post: October 29th, 08:55 AM
  4. recordcount -1
    By middletree in forum ASP
    Replies: 8
    Last Post: October 16th, 02:06 PM
  5. Why does the RecordCount property always = -1
    By George Mizzell in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 13th, 01:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139