Archive for the sql Category

21 06 2007

Geo-Data (france), bis repetita the Bears are here!

Posted by: Ori Pekelman in google maps, php, sql

Our good and extremly competent friends at Bearstech saw my post about Geo encoding (Retrouver par code postal tous les lieux qui sont du même dépratement, de la même région ou de la même ville (mysql, php)) of French data and decided, this was no way to go around this! So Vincent Caron decided to create for all those interested a great GPLed package to help you will all your geocoding woes for France! so go over to the Bearstech forge (http://forge.bearstech.com/) where you can download the package or browse the php source code through the trac SVN interface.

Geo-Data (France) ¶
Features ¶

This is a compilation of simple administrative and geographic informations for France, up to the city and district level:

* 32,856 cities with their official zipcode and préfecture flag. Paris, Lyon and Marseille are split into arrondissements.
* 96 départements, 6 DOM and 3 TOM (within the same hierarchical level). Proper keying is used (ie. 2A for Corse du Sud, no “20″ or “97″ shortcuts).
* 22 régions, with two supplementary to classify départements from DOM and TOM.

Redundant data in all tables help make you sure you can all information available in a single SELECT lookup:

* asking for a département gives its région
* asking for a city also gives its corresponding département and région

Note: the city database might be incomplete, our source was not properly time stamped.
History ¶

This compilation of PHP and SQL code was extracted from a production real-estate web site, which uses extensively this geographical data to search, filter and match people wishes with other’s desires.

The data was collected from various public french sources and meticulously, patiently reworked and modified by Bearstech according to professionnals and users feedback while using the data.
Copyright ¶

Click to continue reading

Comments No Comments »

30 05 2007

Use parameters for field and table names in a MySQL Prepare statement to create truly dynamic SQL

Posted by: Ori Pekelman in sql

The mysql prepare statement sucks heavy time. You can not (!?!!!) use it to template field and table names. So it is mostly useless for any heavy duty stored procedures that want to address different columns.

Here is a small function that will take care of that :)

PLAIN TEXT
SQL:
  1. DELIMITER //
  2. DROP FUNCTION  IF EXISTS substrCount//
  3. CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
  4. RETURN (length(x)-length(REPLACE(x, delim, '')))/length(delim);//
  5.  
  6. DROP FUNCTION  IF EXISTS charsplit//
  7. CREATE FUNCTION charsplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
  8. RETURN REPLACE(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');//
  9.  
  10. DROP FUNCTION  IF EXISTS replacefirst//
  11. CREATE FUNCTION replacefirst(haystack varchar(255), needle varchar(255),replacestr varchar(255)) returns varchar(255)
  12. BEGIN
  13.     IF LOCATE(needle,haystack)>0 THEN
  14.         SET @replaced=concat(LEFT(haystack,LOCATE(needle,haystack)-LENGTH(needle)),replacestr,RIGHT(haystack,LENGTH(haystack)-LOCATE(needle,haystack)));
  15.     ELSE
  16.         SET @replaced=haystack;
  17.     END IF;
  18.     RETURN @replaced;
  19. END;//
  20.  
  21. DROP FUNCTION  IF EXISTS properprepare//
  22. CREATE FUNCTION properprepare(template varchar(255), args varchar(255)) returns varchar(255)
  23. BEGIN
  24.  SET @i=0;
  25.  SET @numargs = substrCount(args,',');
  26.   WHILE @i <= @numargs DO
  27.       SET @i= @i+ 1;
  28.       SET template=replacefirst(template,'?',charsplit(args,',',@i));
  29.   END WHILE;
  30. RETURN template;
  31. END;//
  32.  
  33. DELIMITER ;

Test with

PLAIN TEXT
SQL:
  1. SELECT properprepare('SELECT ? FROM ? ', '*,user');
  2. SELECT properprepare('SELECT ? FROM ? ??', '*,user');
  3. SELECT properprepare('SELECT ? FROM ?', '*,user,test,test1');
  4. SELECT properprepare('SELECT ? FROM ? WHERE ?=?', '*,user,user_id,25');

-- So now you can in your stored procedures (at last!) do:

PLAIN TEXT
SQL:
  1. PREPARE update_query FROM properprepare('SELECT ? FROM ?', '*,mytable');
  2. EXECUTE update_query;
  3. PREPARE update_query FROM properprepare('SELECT ? FROM ?', 'myfield,mytable');
  4. EXECUTE update_query;

The first argument is the template with the question mark '?' as placeholder, the second is a comma separated string with the variables to replace. If you pass too few variables, the remaining placeholders will not be modified so they can be treated with the mysql PREPARE FROM, EXECUTE USING afterwards...

PLAIN TEXT
SQL:
  1. SET @a=25;
  2. SET @b=properprepare('SELECT ? FROM ? WHERE ?=?', '*,user,id_user');
  3. PREPARE stmnt FROM  @b;
  4. EXECUTE stmnt USING @a;

CAVEAT: Hardly tested, do not use in production without really testing this one

Click to continue reading

Comments 1 Comment »

25 05 2007

Retrouver par code postal tous les lieux qui sont du même dépratement, de la même région ou de la même ville (mysql, php)

Posted by: Ori Pekelman in php, sql

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 importfrench_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.zipdans votre base MySQL

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

PLAIN TEXT
PHP:
  1. /**
  2. * get Departments For Region By Postal Code
  3. * EN: This function will return all the French deprtments that are in the same region for a given
  4. * Postal code or the name of the region if $retournerNomRegion=True is passed
  5. * This code is VERY French specific.
  6. * FR: Cette fonction retourne tous les départements Français qui sont dans la même region qu'un code postal donné
  7. * Ou le nom de la région si la valeur $retournerNomRegion=true lui est passée
  8. * <b>USAGE</b>:
  9. * <code>
  10. * //Exemple:
  11. *   getDepartementsForRegionByPostalCode('75005');
  12. *  result:'75','77','78','91','92','93','94','95'
  13. *
  14. *  getDepartementsForRegionByPostalCode('75005');
  15. *  result: 'Ile de France'
  16. * </code>
  17. * @param   $zipcode string (we need to keep leading zeros) zipcode to search for
  18. * @return  always returns true
  19. * @author  ori@af83.com
  20. * @since   Wed March 19 2007 18:09:09 GMT+0200
  21. * @version v 0.01 Wed May 22 2007 18:09:09 GMT+0200
  22. * For an up to date version go to http://dev.af83.com
  23. */
  24.  
  25. function getDepartementsForRegionByPostalCode($zipCode, $retournerNomRegion=false)
  26.  
  27. {
  28. $liste_regions = array (
  29.     "Alsace" => array("67","68"),
  30.     "Aquitaine" => array("24","33","40","47","64"),
  31.     "Auvergne" => array ("03","15","43","63"),
  32.     "Basse-Normandie" => array ("14","50","61"),
  33.     "Bourgogne" => array ("21","58","71","89"),
  34.     "Bretagne" => array ("22","29","35","56"),
  35.     "Centre" => array ("18","28","36","37","41","45"),
  36.     "Champagne-Ardenne" => array ("08","10","51","52"),
  37.     "Corse" => array("20"),
  38.     "DOM-TOM" => array("97"),
  39.     "Franche-Comté" => array ("25","39","70","90"),
  40.     "Haute-Normandie" => array ("27","76"),
  41.     "Ile de France" => array("75","77","78","91","92","93","94","95"),
  42.     "Languedoc-Roussillon" => array("11","30","34","48","66"),
  43.     "Limousin" => array("19","23","87"),
  44.     "Lorraine" => array ("54","55","57","88"),
  45.     "Midi-Pyrénées" => array("09","12","31","32","46","65","81","82"),
  46.     "Nord / Pas-de-Calais" => array("59","62"),
  47.     "Pays de la Loire" => array ("44","49","53","72","85"),
  48.     "Picardie" => array ("02","60","80"),
  49.     "Poitou-Charentes" => array ("16","17","79","86"),
  50.     "PACA" => array("04","05","06","13","83","84"),
  51.     "Rhône-Alpes" => array ("01","07","26","38","42","69","73","74")
  52. );
  53.         $departement = substr($zipCode,0,2);
  54.        
  55.         foreach($liste_regions as $region => $liste_dep)
  56.         {
  57.             if (in_array($departement, $liste_dep))
  58.             {
  59.                 return $retournerNomRegion ? $region: implode (',',$liste_dep);
  60.             }
  61.         }      
  62. }
  63.  
  64. /**
  65. * get SQL For Search By zipcode
  66. * EN: This function will return an sql statement to search for either all places that are in the same postal code,
  67. * 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
  68. * We assume here you have a table "places" that has a column for the french zipcode. Change to meet your needs...
  69. * This code is VERY French specific.
  70. * To use this you will need to import the table (mysql format) joined to this blog post (french_cities_zipcodes) it contains the
  71. * correspondance between cities and zipcodes
  72. * 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
  73. * Ville du même Déprtement ou de la même Région
  74. * Le code prend comme postulat l'existence d'une table "places" qui a une colonne "zipcode" pour le code postal.
  75. * 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.
  76. *
  77. * <b>USAGE</b>:
  78. * <code>
  79. * //Exemple:
  80. *   getSQLForSearchByzipcode('75005', 'zipcode');
  81. * </code>
  82. * @param   $zipcode string  zipcode to search for (we need to keep leading zeros)
  83. * @return  String Sql Statement
  84. * @author  ori@af83.com
  85. * @since   Wed March 19 2007 18:09:09 GMT+0200
  86. * @version v 0.01 Wed May 22 2007 18:09:09 GMT+0200
  87. * For an up to date version go to http://dev.af83.com
  88. */
  89.  
  90. function $getSQLForSearchByzipcode($zipcode,$resultFrom){
  91.         $sqlSelect=" SELECT * from places as p";
  92.         if($zipcode!='' && is_numeric($zipcode)){
  93.             switch ($resultFrom)
  94.             {   case 'zipcode': //places from the same zipcode
  95.                     $sqlWhere=" WHERE zipcode LIKE '".$zipcode."'  ";
  96.                 break;
  97.                 case 'region':  //places from the same region
  98.                     $zipSearch= getDepartementsForRegionByPostalCode ($zipcode);
  99.                     $sqlWhere=" WHERE LEFT(p.zipcode,2) in (".$zipSearch.")  ";
  100.                 break;
  101.                 case 'departement': // in France all zipcodes from the same department start with the same two numbers which is the deprtment identifier
  102.                     $zipSearch=substr($zipcode,0,2);
  103.                     $sqlWhere=" WHERE LEFT(p.zipcode,2) = $zipSearch  ";
  104.                 break;
  105.                 case 'city': // for this you will need a table containing the correspondance between cities and postal codes in your database
  106.                     $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 ";
  107.                     $sqlWhere=" WHERE fcz2.zipcode='$zipcode'  ";
  108.                 break;
  109.             }
  110.         }
  111.         return $sqlSelect.$sqlWhere;
  112. }

Click to continue reading

Comments 5 Comments »

15 05 2007

Validate email addresses in Mysql with a pure select

Posted by: Ori Pekelman in sql

My javascript validated the e-mail addresses, my PHP code validated them why not let mysql have a go at it too?

Here is a view for mysql (don't even think about trying to use this with MySQL < 5.0.1) that will validate email addresses with a select statement, no need for UDF or anything fancy
this is based on code by Narayana Vyas Kondreddi http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htm

PLAIN TEXT
SQL:
  1. CREATE VIEW `invalid_emails` AS SELECT `table_with_email_column`.`email` AS
  2. `invalidemail` FROM `table_with_email_column` WHERE ((locate(_latin1'
  3. ', ltrim(rtrim(`table_with_email_column`.`email`))) <> 0) OR
  4. (LEFT(ltrim(`table_with_email_column`.`email`), 1) = _latin1'@') OR
  5. (RIGHT(rtrim(`table_with_email_column`.`email`), 1) = _latin1'.') OR
  6. ((locate(_latin1'.', `table_with_email_column`.`email`,locate(_latin1'@', `table_with_email_column`.`email`))
  7. - locate(_latin1'@', `table_with_email_column`.`email`)) <= 1) OR
  8. ((length(ltrim(rtrim(`table_with_email_column`.`email`))) -
  9. length(REPLACE(ltrim(rtrim(`table_with_email_column`.`email`)), _latin1'@', _latin1'')))
  10. <> 1) OR
  11. (locate(_latin1'.', reverse(ltrim(rtrim(`table_with_email_column`.`email`)))) <3) OR (locate(_latin1'.@', `table_with_email_column`.`email`) <> 0) OR
  12. (locate(_latin1'..', `table_with_email_column`.`email`) <> 0));

So you can do stuff like delete all lines from the table that have an invalid email:

PLAIN TEXT
SQL:
  1. DELETE FROM table_with_email_column WHERE `table_with_email_column`.`email` IN (SELECT
  2. invalidemail FROM invalid_emails);

or just select all the valid lines :

PLAIN TEXT
SQL:
  1. SELECT FROM table_with_email_column WHERE `table_with_email_column`.`email` NOT IN (SELECT
  2. invalidemail FROM invalid_emails);

Sadly, this view is not updateble ...

Probably this is not at all precise... so I would really like to find a nice test dataset for emails that I can use to validate the validation against the RFC (with 4 letter TLDs and the works....)

Click to continue reading

Comments 3 Comments »

14 05 2007

Strip non numeric characters in pure SQL (mysql)

Posted by: Ori Pekelman in sql

Ever need to strip non numeric characters from a varchar column ? well here's a solution, That will keep leading zeros and all... this query will treat up to 24 characters but you can repeat this up to 255 if you want...

PLAIN TEXT
SQL:
  1. SELECT (concat(
  2. CASE
  3. WHEN substring(phone_number, 1,1) REGEXP '[0-9]'
  4. THEN substring(phone_number, 1,1) ELSE '' END ,
  5. CASE
  6. WHEN substring(phone_number, 2,1) REGEXP '[0-9]'
  7. THEN substring(phone_number, 2,1) ELSE '' END ,
  8. CASE
  9. WHEN substring(phone_number, 3,1) REGEXP '[0-9]'
  10. THEN substring(phone_number, 3,1) ELSE '' END ,
  11. CASE
  12. WHEN substring(phone_number, 4,1) REGEXP '[0-9]'
  13. THEN substring(phone_number, 4,1) ELSE '' END ,
  14. CASE
  15. WHEN substring(phone_number, 5,1) REGEXP '[0-9]'
  16. THEN substring(phone_number, 5,1) ELSE '' END ,
  17. CASE
  18. WHEN substring(phone_number, 6,1) REGEXP '[0-9]'
  19. THEN substring(phone_number, 6,1) ELSE '' END ,
  20. CASE
  21. WHEN substring(phone_number, 7,1) REGEXP '[0-9]'
  22. THEN substring(phone_number, 7,1) ELSE '' END ,
  23. CASE
  24. WHEN substring(phone_number, 8,1) REGEXP '[0-9]'
  25. THEN substring(phone_number, 8,1) ELSE '' END ,
  26. CASE
  27. WHEN substring(phone_number, 9,1) REGEXP '[0-9]'
  28. THEN substring(phone_number, 9,1) ELSE '' END ,
  29. CASE
  30. WHEN substring(phone_number, 10,1) REGEXP '[0-9]'
  31. THEN substring(phone_number, 10,1) ELSE '' END ,
  32. CASE
  33. WHEN substring(phone_number, 11,1) REGEXP '[0-9]'
  34. THEN substring(phone_number, 11,1) ELSE '' END ,
  35. CASE
  36. WHEN substring(phone_number, 12,1) REGEXP '[0-9]'
  37. THEN substring(phone_number, 12,1) ELSE '' END ,
  38. CASE
  39. WHEN substring(phone_number, 13,1) REGEXP '[0-9]'
  40. THEN substring(phone_number, 13,1) ELSE '' END ,
  41. CASE
  42. WHEN substring(phone_number, 14,1) REGEXP '[0-9]'
  43. THEN substring(phone_number, 14,1) ELSE '' END ,
  44. CASE
  45. WHEN substring(phone_number, 15,1) REGEXP '[0-9]'
  46. THEN substring(phone_number, 15,1) ELSE '' END ,
  47. CASE
  48. WHEN substring(phone_number, 16,1) REGEXP '[0-9]'
  49. THEN substring(phone_number, 16,1) ELSE '' END ,
  50. CASE
  51. WHEN substring(phone_number, 17,1) REGEXP '[0-9]'
  52. THEN substring(phone_number, 17,1) ELSE '' END ,
  53. CASE
  54. WHEN substring(phone_number, 18,1) REGEXP '[0-9]'
  55. THEN substring(phone_number, 18,1) ELSE '' END ,
  56. CASE
  57. WHEN substring(phone_number, 19,1) REGEXP '[0-9]'
  58. THEN substring(phone_number, 19,1) ELSE '' END ,
  59. CASE
  60. WHEN substring(phone_number, 20,1) REGEXP '[0-9]'
  61. THEN substring(phone_number, 20,1) ELSE '' END ,
  62. CASE
  63. WHEN substring(phone_number, 21,1) REGEXP '[0-9]'
  64. THEN substring(phone_number, 21,1) ELSE '' END ,
  65. CASE
  66. WHEN substring(phone_number, 22,1) REGEXP '[0-9]'
  67. THEN substring(phone_number, 22,1) ELSE '' END ,
  68. CASE
  69. WHEN substring(phone_number, 23,1) REGEXP '[0-9]'
  70. THEN substring(phone_number, 23,1) ELSE '' END ,
  71. CASE
  72. WHEN substring(phone_number, 24,1) REGEXP '[0-9]'
  73. THEN substring&#