SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 26
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    number of comments, next/prev links (was "First post here")

    Hello everyone, this is my first post here and I was told this may be a great place to get help for a few CF questions I have. I'm by no means an expert in CF. I just know enough to get by on most things I need it for.

    This is the website for my shop: http://www.devotiontattoos.net

    On that site I'd like to accomplish 2 things that have really been giving me a hard time so I was hoping someone here could help me out.

    1. I'd like to output the total number of comments for each blog entry next to the comments link on the main page. So far when I try to do this it only outputs the total for all comments together.

    2. In the tattoo artists portfolios I'd like to have next & previous links on each full size image. The images are sorted by the date they are uploaded currently.

    I can post more info on how my database is structured & such is someone thinks they can help me out.

    Thanks!

  2. #2
    SitePoint Zealot Vogelfrei's Avatar
    Join Date
    Jan 2004
    Location
    west of the divide
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. You'll probably be able to accomplish what you want here with an INNER JOIN and a GROUP BY. Post the query you're currently using for the blog post output, and we might be able to help you with more detail.
    2. To generate "next" and "previous" links, you'll need to re-execute the same query you used to generate the list page, in order to know where in the list your current entry resides. (You could fudge it by assuming there are no gaps in the IDs, but you really shouldn't assume that.) Again, show us the relevant parts of your current code so we can make descriptive suggestions.

    And I hate to play backseat moderator, but your post title could stand to be more descriptive. "First Post here" doesn't tell us anything about the nature of your inquiries.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay we'll start with the first one. Displaying the amount of comments per blog post.
    I have a database table called BlogComment that has a Unique ID per comment and also has the ID of the Blog post that it's conected with. I thought maybe that info would help.
    Here's the code for how the blog page is displayed on my page now.

    This queries the blog database:
    Code:
    <cfquery name="rsBlog" datasource="#Request.DSN#">
    SELECT *
    FROM Blog
    ORDER BY BlogDate DESC</cfquery>
    And here's the output of each blog entry:
    Code:
    <cfoutput query="rsBlog" startRow="#StartRow_rsBlog#" maxRows="#MaxRows_rsBlog#">
      <table width="100%" border="0">
        <tr>
          <td width="50%"><div align="left">Posted By: <strong>#rsBlog.UserName#</strong></div></td>
          <td width="50%"><div align="right">On: <strong>#LSDateFormat(rsBlog.BlogDate,'MMMM DD, YYYY')#</strong></div></td>
        </tr>
        <tr>
          <td colspan="2"><div align="left">#textareaformat(rsBlog.BlogContent)#</div></td>
        </tr>
        <tr>
          <td colspan="2"><div align="right"><a href="blogcomment.cfm?BlogID=#rsBlog.BlogID#" target="_self">Comments</a></div></td>
        </tr>
      </table>
      <hr />
    </cfoutput>
    I hope this is enough info to tackle this problem. Let me know if you need more code or a better description of the database structure.

    Thanks a lot!

  4. #4
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sikk66 View Post
    Okay we'll start with the first one. Displaying the amount of comments per blog post.
    I have a database table called BlogComment that has a Unique ID per comment and also has the ID of the Blog post that it's conected with. I thought maybe that info would help.
    Here's the code
    Code:
    <cfquery name="rsBlog" datasource="#Request.DSN#">
    SELECT *
    FROM Blog
    ORDER BY BlogDate DESC</cfquery>
    SELECT *
    , ( SELECT COUNT (*) FROM BlogComment WHERE Blog.BlogPostID = BlogComment.BlogPostID ) AS Comments
    FROM Blog
    ORDER BY BlogDate DESC

    <cfdump var=#rsBlog#>

    It helps if we know your native db.

    If this works, thank r937.. he answered a similar question of mine when I first joined sp.

    And welcome to sp.
    Last edited by mcsolas; Jan 2, 2007 at 14:16.

  5. #5
    Take The Blue Pill neilmerton's Avatar
    Join Date
    Jul 2004
    Location
    Leicester, UK
    Posts
    871
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here's a query that rudy (r937) helped me with ages ago:

    Code:
    <cfquery name="qGetPhoto" datasource="#variables.dsn#">
        SELECT       p.photo_id
        ,            p.photo_file
        ,            p.photo_title
        ,            p.photo_description
        ,            p.photo_keywords
        ,            p.photo_date
        ,            p.photo_views
        ,            a.album_id
        ,            a.album_title
        ,            ( SELECT MIN(photo_id) FROM tbl_photos WHERE photo_album = p.photo_album AND photo_id > p.photo_id ) AS prev_id
        ,            ( SELECT MAX(photo_id) FROM tbl_photos WHERE photo_album = p.photo_album AND photo_id < p.photo_id ) AS next_id
        FROM         tbl_photos AS p
        INNER JOIN   tbl_albums AS a ON p.photo_album = a.album_id
        WHERE        p.photo_id = <cfqueryparam value="#arguments.pid#" cfsqltype="cf_sql_integer" />
    </cfquery>
    this should steer you in the right direction

  6. #6
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats select Min|Max part is quite nice... definitely a snippet worthy of saving.

  7. #7
    SitePoint Zealot Vogelfrei's Avatar
    Join Date
    Jan 2004
    Location
    west of the divide
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    neilmerton: I agree with MCsolas, that min/max is as sweet as anything you'd expect from rudy.

  8. #8
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The comment count code worked flawlessly!!!
    I cant thank you guys enough.
    I'll start messing with the next/prev image issue later today and I'll let you guys know how it's coming along.

    Thanks again!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you guys are too kind
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so I'm trying with the next/previous image links...

    Here's an example of a page I need to use this on.
    http://devotiontattoos.net/James/ful...fm?ImageID=159

    Here's the code I'm trying to write so far. I tried to use some of the select min, select max code that was posted above. Like I said, I'm no expert here. I thought the only thing I would need to query from the database would be the image ID, and the date, since the images are sorted by date. what I'm tring isn't working. I'm not sure what the inner join was so I didn't include that.
    the table name is JamesTattoos. the columns in the table are ImageID, ImageDate, ImageName, ThumbName.

    Code:
    <cfquery name="rsNav" datasource="#Request.DSN#">
    SELECT ImageID
    ,ImageDate
    ,( SELECT MIN(ImageID) FROM JamesTattoos WHERE url.ImageID = ImageID AND url.ImageID > ImageID ) AS prev_id
    ,( SELECT MAX(ImageID) FROM JamesTattoos WHERE url.ImageID = ImageID AND ImageID < url.ImageID ) AS next_id
    FROM JamesTattoos
    ORDER BY ImageDate DESC
    </cfquery>
    anyone have any recommendations?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ImageDate
         , ImageName
         , ThumbName
         , ( SELECT MAX(ImageID) 
               FROM JamesTattoos 
              WHERE ImageDate < T.ImageDate ) AS prev_id
         , ( SELECT MIN(ImageID) 
               FROM JamesTattoos 
              WHERE ImageDate > T.ImageDate ) AS next_id
      FROM JamesTattoos as T
     WHERE ImageID = 159
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for the response.

    Wouldn't I make it ?
    Code:
    WHERE ImageID = #URL.ImageID#
    instead of putting the actual ImageID there.

    also for the next previous link How would I code that. currently I tried:

    Code:
    <cfoutput><a href="fullimage.cfm?ImageID=#next_id#">Next Image</a></cfoutput>
    and I got an error saying Variable NEXT_ID is undefined.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, this is fine --
    Code:
    WHERE ImageID = #URL.ImageID#
    except i would never use a variable from the URL scope directly in my query (do a search on the topic "sql injection")

    as for coding the next_id and prev_id query variables (hint, hint), you would simply scope them properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sikk66 View Post
    Wouldn't I make it ?
    Code:
    WHERE ImageID = #URL.ImageID#
    Yes, but I would suggest using the cfqueryparam tag to secure your url variable from any funny business.

    WHERE ImageID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.ImageID#">

  15. #15
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I seem to have it working correctly now. Thanks to everyone for you help!!! You guys are awesome!

    One last question.
    How do I prevent the previous link from showing up on the first page and the next link from showing up on the last page? I've done this in other situations, but it seems to not work the same with this.

    Thanks again!

  16. #16
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a suggestion ( not quite sure if this would work .. )

    Add this to the query:
    , ( SELECT MAX(ImageID)
    FROM JamesTattoos ) AS last_id
    , ( SELECT MIN(ImageID)
    FROM JamesTattoos ) AS first_id

    Then test <cfif NOT prev_id eq first_id>Show Link</cfif>
    Last edited by mcsolas; Jan 5, 2007 at 19:37.

  17. #17
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmmm, that didn't seem to be working for me...

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, mcsolas, it wouldn't, because first and last are by date, not by id

    however, the answer is similar to what you had in mind

    when the image retrieved by the id in the url happens to be the earliest one, then there won't be an earlier one, so the subquery which produces prev_id would actually return a null

    therefore...
    Code:
    <cfif Len(queryname.prev_id)>
      <a href="fullimage.cfm?ImageID=#queryname.prev_id#">Prev Image</a>
    <cfelse>
      <span class="inactivelink">Prev Image</span>
    </cfif>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You guys are the BEST!!!

    Thank you tons!!

  20. #20
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The solution for both of these worked great with MS Access database, but I'm wanting to convert the database to MySQL and I'm trying it out on my test server and I'm getting errors only on the pages in which I used the code you guys helped me with on here. The comment count & next/prev image.
    Suggestions?
    I can post the code I have if you guys need.
    Thanks

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you'd better post something because otherwise we'd be just guessing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay we'll start with the blog page.
    This is the page I did the comment counts on.
    I haven't changed any code at all, just switched the database to mysql. I've also made sure that I updated the dsn in application.cfm.

    Here's my query:

    Code:
    <cfquery name="rsBlog" datasource="#Request.DSN#">
    SELECT *
    , ( SELECT COUNT (*)
    FROM BlogComment
    WHERE BlogComment.BlogID = Blog.BlogID AND BlogComment.Online = 1 ) AS Comments
    FROM Blog
    ORDER BY BlogDate DESC</cfquery>
    Here's the error I'm recieving:

    Error Executing Database Query.
    Syntax error or access violation: You have an error in your SQL syntax near 'SELECT COUNT (*) FROM BlogComment WHERE BlogComment.BlogID = Blog.BlogID AND B' at line 2

    The error occurred in C:\phpdev\www\devotion\blog.cfm: line 12

    10 : }
    11 : </cfscript>
    12 : <cfquery name="rsBlog" datasource="#Request.DSN#">
    13 : SELECT *
    14 : , ( SELECT COUNT (*)

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please go and read this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aha, I'm on MySQL 4.0.27

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yup, over two years out of date, which is about 20 years on the internet

    are you on a shared host? ask them to upgrade asap, and if they don't, change hosts -- they don't deserve your business
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •