[thelist] SQL Query : using Group By and Order By together

Alliax damiencola at wanadoo.fr
Fri Jan 4 17:25:00 CST 2002


I have a PHP script allowing people to submit their "logo + link + comment"
their logo is then displayed on my homepage for a minimum of X minutes and
then another person can submit their own "logo + link + comment"

I keep every entry in the database and use the time of submission in order
to display the last submitted logo.

In a 'gallery' page my goal is to list all submitted logos, by chronological
order, but grouping them by the logo's url since people can submit several
time the same logo..

So I use this SQL request:
SELECT aplien, aplogo, apnom, aptime FROM table GROUP BY aplogo ORDER BY
aptime DESC

the problem I am encountering is that one of the logo which has first been
submitted early is appearing on top of the list

Although it has been submitted recently as well, and it's unique key
(automatic counter) is high, when I look at my database via phpmyadmin, it
shows on the first page.
So I concluded that the problem may come from:
1. the index of the mysql database, is determining the order of the logo and
not my supposed chronological order via "ORDER BY aptime DESC"
2. a misunderstanding of the functionning of GROUP BY

Can you see what might be the issue here?

Cordialement,

__ Alliax         ~CV : http://LingoParadise.com/cv.php
Un site pour Toulon : http://www.ToulonParadise.com
Un site pour Renaud : http://www.rfaucilhon.com
Un site pour Director : http://www.LingoParadise.com

-----Message d'origine-----
De : thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]De la part de .jeff
Envoyé : samedi 5 janvier 2002 00:00
À : thelist at lists.evolt.org
Objet : RE: [thelist] RegEx Fun


anthony,

><><><><><><><><><><><><><><><><><><><><><><><><><><><><><
> From: Anthony Baratta
>
> > i need some assistance from those of you with more
> > confidence with regular expressions than i have.  i'm
> > looking to create a couple that will strip substrings
> > from a longer string.  i have 3 patterns i'd like to
> > match and remove, if found.  the one caveat is that
> > i'm using coldfusion and am therefore limited to it's
> > regex rules.
>
> Wouldn't it be easier to use the built in Query String
> parsing? With ASP, Perl, PHP there are built in or
> customizable functions that will parse the Query String
> into it's respective key=>value pairs. I don't know what
> ColdFusion has.
><><><><><><><><><><><><><><><><><><><><><><><><><><><><><

i'm not looking to analyze the actual query string.  rather, i'm passing a
string via a form element.  this string happens to be a url, complete with
query string.  this string, if present, is then used by the processing page
to know what url to return the user to once the processing is complete.
however, there are some elements i need to be able to strip from this string
so they don't get doubled-up, like the "pageresponse" name/value piece i was
referring to.

to see what i'm talking about, check out the project i'm working on right
now:

http://documents.pape.com/

to see the problem occur, follow these steps:

 1) add any item to your basket
 2) take notice of the url once it's added

http://documents.pape.com/index.cfm?pageresponse=Item%20added&cfid=2127482&c
ftoken=32024994
 3) add another item to your basket
 4) take notice of the url again:

http://documents.pape.com/index.cfm?pageresponse=Item%20added&cfid=2127482&c
ftoken=32024994&pageresponse=Item%20added&cfid=2127482&cftoken=32024994
 5) click the search button (top right)
 6) do a search for "s" (without the quotes)
 7) take notice of the url again:

http://documents.pape.com/search/?keywords=s&option=any&maxrows=10&cfid=2127
482&cftoken=32024994
 8) add another item to your basket
 9) take notice of the url again:

http://documents.pape.com/search/?keywords=s&option=any&maxrows=10&cfid=2127
482&cftoken=32024994&pageresponse=Item%20added&cfid=2127482&cftoken=32024994
10) add another item to your basket
11) take notice of the url once again:

http://documents.pape.com/search/?keywords=s&option=any&maxrows=10&cfid=2127
482&cftoken=32024994&pageresponse=Item%20added&cfid=2127482&cftoken=32024994
&pageresponse=Item%20added&cfid=2127482&cftoken=32024994

do you see what i'm trying to avoid now?

there are other ways of achieving a fix to this problem[1] that don't
involve regular expressions.  however, i know they'll work in this instance
and was hoping to use this as an opportunity to learn something new.


[1]
instead of creating the return url string from cgi.script_name and
cgi.query_string, i could construct it manually using only those url
variables i know the page needs to have to display the data correctly and
leaving out those url variables that are going to cause problems on the
process page.

thanks,

.jeff

http://evolt.org/
jeff at members.evolt.org
http://members.evolt.org/jeff/



--
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !






More information about the thelist mailing list