[thelist] Incorrect SQL?

[rudy]

> Can anyone see what's wrong with it?

yes, the GROUP BY is incorrect --

  SELECT entryTitle
       , DATE_FORMAT(entryDate,'%d-%m-$Y') as entryDate
       , DATE_FORMAT(entryDate,'%W, %M %D') as entryLongDate
       , DATE_FORMAT(entryDate,'%T') as entryTime
       , entryEntry
       , count(*) as entryComments
    FROM blogEntries, blogComments
   WHERE blogEntries.entryId = blogComments.entryId
GROUP BY entryTitle, entryDate, entryEntry
ORDER BY entryDate DESC LIMIT 7

the GROUP BY must list all the non-aggregate fields in the SELECT

not sure if mysql will allow it, but based on the above query, it should be

 GROUP BY entryTitle
       , DATE_FORMAT(entryDate,'%d-%m-$Y')
       , DATE_FORMAT(entryDate,'%W, %M %D')
       , DATE_FORMAT(entryDate,'%T')
       , entryEntry

or alternatively

 GROUP BY entryTitle
       , entryDate
       , entryLongDate
       , entryTime
       , entryEntry

count(*), joshua, counts rows in each group

> a) If I'm putting in now() for the value of datetime,
> how do I tell it to add n hours to the time?

use the mysql DATE_ADD function

> b) how many hours are evolt's servers relative to gmt,
> ie how much will I need to adjust the hours for australian time?

good question

evolt's servers are gmt-6, i believe, but i'm not sure how you should
calculate dates and times over the day boundary

and don't forget there's daylight saving time to contend with, north
america springs forward tonight


rudy