Professional Web Applications Themes

Need help with a query - MySQL

Hey guys, maybe you can help me out. I have two tables: articles, and comments. The following query selects the newest 12 articles (plus extra article data) along with the number of comments accompanying them. SELECT COUNT(c.comment) as numcoms, a.localurl, a.title, a.ts, a.source, a.url, a.text, a.thumb, DATE_FORMAT(a.ts, '%M %D') as date FROM comments c RIGHT JOIN articles a ON a.localurl = c.localurl GROUP BY a.localurl ORDER BY a.ts DESC LIMIT 12 This is all well and good, but now i don't want the newest 12 articles. I want some in a specified time range. Example i want to throw in: ...

  1. #1

    Default Need help with a query

    Hey guys, maybe you can help me out. I have two tables: articles, and
    comments. The following query selects the newest 12 articles (plus
    extra article data) along with the number of comments accompanying
    them.

    SELECT COUNT(c.comment) as numcoms, a.localurl, a.title, a.ts,
    a.source, a.url, a.text, a.thumb, DATE_FORMAT(a.ts, '%M %D') as date
    FROM comments c RIGHT JOIN articles a ON a.localurl = c.localurl
    GROUP BY a.localurl
    ORDER BY a.ts DESC
    LIMIT 12


    This is all well and good, but now i don't want the newest 12
    articles. I want some in a specified time range. Example i want to
    throw in:

    WHERE (a.ts > '.20070625000000.' AND a.ts < '.20070626000000.')

    However adding this additional line in there causes the query to
    fail. I think it is because i'm trying to put the WHERE clause on
    "articles" but really have to select from comments (because i'm
    grouping them). Anyone know how to solve this problem?

    rplobue@gmail.com Guest

  2. #2

    Default Re: Need help with a query

    com wrote: 

    SELECT COUNT(c.comment) as numcoms, a.localurl, a.title, a.ts,
    a.source, a.url, a.text, a.thumb, DATE_FORMAT(a.ts, '%M %D') as date
    FROM comments c RIGHT JOIN articles a ON a.localurl = c.localurl
    WHERE (DATE_FORMAT(a.ts,'%Y%m%d') > '20070625'
    AND DATE_FORMAT(a.ts,'%Y%m%d') < '20070626')
    GROUP BY a.localurl
    ORDER BY a.ts DESC
    LIMIT 12

    You can use LIMIT to move in that result list , say you want the 13th to 24th
    result:

    SELECT COUNT(c.comment) as numcoms, a.localurl, a.title, a.ts,
    a.source, a.url, a.text, a.thumb, DATE_FORMAT(a.ts, '%M %D') as date
    FROM comments c RIGHT JOIN articles a ON a.localurl = c.localurl
    WHERE (DATE_FORMAT(a.ts,'%Y%m%d') > '20070625'
    AND DATE_FORMAT(a.ts,'%Y%m%d') < '20070626')
    GROUP BY a.localurl
    ORDER BY a.ts DESC
    LIMIT 12,12



    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: Need help with a query

    J.O. Aho wrote: 
    >
    > SELECT COUNT(c.comment) as numcoms, a.localurl, a.title, a.ts,
    > a.source, a.url, a.text, a.thumb, DATE_FORMAT(a.ts, '%M %D') as date
    > FROM comments c RIGHT JOIN articles a ON a.localurl = c.localurl
    > WHERE (DATE_FORMAT(a.ts,'%Y%m%d') > '20070625'
    > AND DATE_FORMAT(a.ts,'%Y%m%d') < '20070626')
    > GROUP BY a.localurl
    > ORDER BY a.ts DESC
    > LIMIT 12
    >
    > You can use LIMIT to move in that result list , say you want the 13th to 24th
    > result:
    >
    > SELECT COUNT(c.comment) as numcoms, a.localurl, a.title, a.ts,
    > a.source, a.url, a.text, a.thumb, DATE_FORMAT(a.ts, '%M %D') as date
    > FROM comments c RIGHT JOIN articles a ON a.localurl = c.localurl
    > WHERE (DATE_FORMAT(a.ts,'%Y%m%d') > '20070625'
    > AND DATE_FORMAT(a.ts,'%Y%m%d') < '20070626')
    > GROUP BY a.localurl
    > ORDER BY a.ts DESC
    > LIMIT 12,12
    >
    >
    >[/ref]
    To add to J.O. Aho, I wouldn't convert the ts column for matching, do it
    the other way around. The way you were doing it makes the database run
    the function for every row unless, of course, you have an index based on
    the function. Running the conversion function on the parameters will
    happen only twice.

    Adam
    Adam Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 AM

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