[thelist] php/mysql help

[rudy]

>This WILL work
>
>Note:
>1). The GROUP BY clause in the first SQL query. This gets the distinct
>values.
>2). The second SQL query (in the while loop) picks a "distinct" value and
>then loops through the entries using "for"

manish, sorry, that first query will only work in mysql -- when you select
all the columns in a table and then only group on one of them, you are
breaking standard sql

here's the relevant excerpt from the mysql docs --

     MySQL has extended the use of GROUP BY.
     You can use columns or calculations in the
     SELECT expressions that don't appear in the
     GROUP BY part. This stands for any possible
     value for this group. You can use this to get
     better performance by avoiding sorting and
     grouping on unnecessary items. For example,
     you don't need to group on customer.name in
     the following query:

     mysql> select order.custid
                 , customer.name
                 , max(payments)
              from order
                 , customer
             where order.custid = customer.custid
          GROUP BY order.custid;

     In ANSI SQL, you would have to add customer.name
     to the GROUP BY clause. In MySQL, the name is redundant
     if you don't run in ANSI mode.

     DON'T USE THIS FEATURE if the columns you omit
     from the GROUP BY part aren't unique in the group!
     You will get unpredictable results.

note the caps in the above paragraph are actually in bold in their docs

also, the second query is very inefficient

never ever do a database call inside a loop -- there is always a more
efficient way, or i shall eat my hat (i reserve the right to choose the
hat)

shanx, when you said you "would maybe prefer to err on the side of making
*one* trip to the database" you were right on the money, that's no error,
that's good programming

incidentally, sarah's initial example would have required

    order by datefield descending, timefield ascending

except that the example was probably quickly dashed off just to illustrate
the grouping concept

a lot cleaner in cold fusion, ain't it sarah?   ;o)


rudy