Re: PHP/mySQL question using ORDER BY with logic
| From: | Robert Cummings | Date: | Fri, 24 Oct 2008 04:25:05 +0000 |
| Subject: | Re: PHP/mySQL question using ORDER BY with logic | ||
| References: | 1 2 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-282395@lists.php.net to get a copy of this message | ||
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