Re: PHP/mySQL question using ORDER BY with logic

Rob Gould schrieb:

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'; Hi,

Try to solve your Logic on your programming language and to select Data with your Database... Try to normalize more your Information on the Database. Regars Carlos