Robert Cummings wrote:
On Fri, 2008-10-24 at 00:18 -0400, Rob Gould wrote:
Question about mySQL and PHP, when using the mySQL ORDER BY method...
Basically I've got data coming from the database where a wine
producer-name is a word like:
Château Bahans Haut-Brion
or
La Chapelle de La Mission Haut-Brion
or
Le Clarence de Haut-Brion
but I need to ORDER BY using a varient of the string:
1) If it begins with Château, don't include Chateau in the
string to order by.
2) If it begins with La, don't order by La, unless the first
word is Chateau, and then go ahead and order by La.
Example sort: Notice how the producer as-in comes before the
parenthesis, but the ORDER BY actually occurs after a re-ordering of
the producer-string, using the above rules.
Red: Château Bahans Haut-Brion (Bahans Haut-Brion, Château )
Red: La Chapelle de La Mission Haut-Brion (Chapelle de La Mission
Haut-Brion, La )
Red: Le Clarence de Haut-Brion (Clarence de Haut-Brion, Le )
Red: Château Haut-Brion (Haut-Brion, Château )
Red: Château La Mission Haut-Brion (La Mission Haut-Brion, Château )
Red: Domaine de La Passion Haut Brion (La Passion Haut Brion,
Domaine de )
Red: Château La Tour Haut-Brion (La Tour Haut-Brion, Château )
Red: Château Larrivet-Haut-Brion (Larrivet-Haut-Brion, Château )
Red: Château Les Carmes Haut-Brion (Les Carmes Haut-Brion, Château )
That logic between mySQL and PHP, I'm just not sure how to
accomplish? I think it might involve a mySQL alias-technique but I
could be wrong.
Right now, my PHP call to generate the search is this:
$query = 'SELECT * FROM wine WHERE MATCH(producer, varietal,
appellation, designation, region, vineyard, subregion, country,
vintage) AGAINST ( ' . $searchstring . ') ORDER BY producer LIMIT
0,100';
Maybe there's a good way to do it with the table as is... but I'm
doubtful. I would create a second field that contains a pre-processed
version of the name that performs stripping to achieve what you want.
This could be done by a PHP script when the data is inserted into the
database, or if not possible like that, then a cron job could run once
in a while, check for entries with this field empty and generate it.
Yeah I'd suspect that the storage overhead is nothing compared to the
speed increase you'll get during the read operations if you don't have
to dick around with the data :)
(yes I'm comparing bits to time, but I don't have time to explain that bit).
Col
--
Colin Guthrie
gmane(at)colin.guthr.ie
http://colin.guthr.ie/
Day Job:
Tribalogic Limited [http://www.tribalogic.net/]
Open Source:
Mandriva Linux Contributor [http://www.mandriva.com/]
PulseAudio Hacker [http://www.pulseaudio.org/]
Trac Hacker [http://trac.edgewall.org/]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
