[thelist] php/mysql help
rudy
r937 at interlog.com
Tue Feb 19 21:03:01 CST 2002
>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
More information about the thelist
mailing list