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. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
