af83

Retrouver par code postal tous les lieux qui sont du même département, de la même région ou de la même ville

EN: Imagine you have a google maps application (or anything else that deals with places) with data in France, now often you would want to get places that are in the same area as one chosen by the user. Here is a bit of PHP code to help you with that… It will help you select all the places from your database that have the same postal code (easy) are in the same city, are in the same department or are in the same region (a bit harder). All of this is extremely French and of no use anywhere else… Don't forget to download and import french_cities_zipcodes.zip into your MySQL database…

CAVEAT: This code has not been tested yet… There may be some typos in here…

FR: Vous voulez faire une petite application Google Maps ou n'importe quel code PHP o๠vous avez besoin de trouver pour un lieu les autres lieux qui sont de la même région, du même département ou de la même ville… Voilà  un bout de code qui pourra vous être utile. N'oubliez pas de télécharger et d'importer la table french_cities_zipcodes.zip dans votre base MySQL

CAVEAT: Ce code n'a pas encore été testé du tout… il peut y avoir de fautes de frappe…

/**
 * get Departments For Region By Postal Code
 * EN: This function will return all the French deprtments that are in the same region for a given
 * Postal code or the name of the region if $retournerNomRegion=True is passed
 * This code is VERY French specific.
 * FR: Cette fonction retourne tous les départements Français qui sont dans la même region qu'un code postal donné
 * Ou le nom de la région si la valeur $retournerNomRegion=true lui est passée
 * <b>USAGE</b>:
 * <code>
 * //Exemple:
 *   getDepartementsForRegionByPostalCode('75005');
 *  result:'75','77','78','91','92','93','94','95'
 *
 *  getDepartementsForRegionByPostalCode('75005');
 *  result: 'Ile de France'
 * </code>
 * @param   $zipcode string (we need to keep leading zeros) zipcode to search for
 * @return  always returns true
 * @author  ori@af83.com
 * @since   Wed March 19 2007 18:09:09 GMT+0200
 * @version v 0.01 Wed May 22 2007 18:09:09 GMT+0200
 * For an up to date version go to http://dev.af83.com
 */

function getDepartementsForRegionByPostalCode($zipCode, $retournerNomRegion=false)

{
$liste_regions = array (
    "Alsace" => array("67","68"),
    "Aquitaine" => array("24","33","40","47","64"),
    "Auvergne" => array ("03","15","43","63"),
    "Basse-Normandie" => array ("14","50","61"),
    "Bourgogne" => array ("21","58","71","89"),
    "Bretagne" => array ("22","29","35","56"),
    "Centre" => array ("18","28","36","37","41","45"),
    "Champagne-Ardenne" => array ("08","10","51","52"),
    "Corse" => array("20"),
    "DOM-TOM" => array("97"),
    "Franche-Comté" => array ("25","39","70","90"),
    "Haute-Normandie" => array ("27","76"),
    "Ile de France" => array("75","77","78","91","92","93","94","95"),
    "Languedoc-Roussillon" => array("11","30","34","48","66"),
    "Limousin" => array("19","23","87"),
    "Lorraine" => array ("54","55","57","88"),
    "Midi-Pyrénées" => array("09","12","31","32","46","65","81","82"),
    "Nord / Pas-de-Calais" => array("59","62"),
    "Pays de la Loire" => array ("44","49","53","72","85"),
    "Picardie" => array ("02","60","80"),
    "Poitou-Charentes" => array ("16","17","79","86"),
    "PACA" => array("04","05","06","13","83","84"),
    "Rhône-Alpes" => array ("01","07","26","38","42","69","73","74")
);
        $departement = substr($zipCode,0,2);

        foreach($liste_regions as $region => $liste_dep)
        {
            if (in_array($departement, $liste_dep))
            {
                return $retournerNomRegion ? $region: implode (',',$liste_dep);
            }
        }
}

/**
 * get SQL For Search By zipcode
 * EN: This function will return an sql statement to search for either all places that are in the same postal code,
 * All places that are in the same town, all places that are in the same deparment or all places that are from the same region
 * We assume here you have a table "places" that has a column for the french zipcode. Change to meet your needs...
 * This code is VERY French specific.
 * To use this you will need to import the table (mysql format) joined to this blog post (french_cities_zipcodes) it contains the
 * correspondance between cities and zipcodes
 * FR: Cette fonction retourne une requête SQL pour retrouver par code postal tous les lieux qui sont du même code postal, de la même
 * Ville du même Déprtement ou de la même Région
 * Le code prend comme postulat l'existence d'une table "places" qui a une colonne "zipcode" pour le code postal.
 * Pour l'utiliser vous devez importer la table french_cities_zipcodes jointe à  ce billet de blog qui contient la correspondance entre les code postaux et les villes.
 *
 * <b>USAGE</b>:
 * <code>
 * //Exemple:
 *   getSQLForSearchByzipcode('75005', 'zipcode');
 * </code>
 * @param   $zipcode string  zipcode to search for (we need to keep leading zeros)
 * @return  String Sql Statement
 * @author  ori@af83.com
 * @since   Wed March 19 2007 18:09:09 GMT+0200
 * @version v 0.01 Wed May 22 2007 18:09:09 GMT+0200
 * For an up to date version go to http://dev.af83.com
 */

function $getSQLForSearchByzipcode($zipcode,$resultFrom){
        $sqlSelect=" SELECT * from places as p";
        if($zipcode!='' && is_numeric($zipcode)){
            switch ($resultFrom)
            {   case 'zipcode': //places from the same zipcode
                    $sqlWhere=" WHERE zipcode LIKE '".$zipcode."'  ";
                break;
                case 'region':  //places from the same region
                    $zipSearch= getDepartementsForRegionByPostalCode ($zipcode);
                    $sqlWhere=" WHERE LEFT(p.zipcode,2) in (".$zipSearch.")  ";
                break;
                case 'departement': // in France all zipcodes from the same department start with the same two numbers which is the deprtment identifier
                    $zipSearch=substr($zipcode,0,2);
                    $sqlWhere=" WHERE LEFT(p.zipcode,2) = $zipSearch  ";
                break;
                case 'city': // for this you will need a table containing the correspondance between cities and postal codes in your database
                    $sqlSelect=" SELECT * from places as p left join  `french_cities_zipcodes` as fcz on p.zipcode =fcz.`zipcode` left join `french_cities_zipcodes` as fcz2 on fcz.city=fcz2.city and fcz.departement=fcz2.departement ";
                    $sqlWhere=" WHERE fcz2.zipcode='$zipcode'  ";
                break;
            }
        }
        return $sqlSelect.$sqlWhere;
}

blog comments powered by Disqus