Archive for May, 2007

« Previous Entries
31 05 2007

Google Developer Day presentation

Posted by: Ori Pekelman in ajax, events, google maps, php

For the benefit of those who participated in the Google developers day presentation a French only post.

Dans ce billet vous trouverez les sources et les présentation de la session Google Maps lors du Google Developers Day à Paris du 31 mai 2007 . Dans le poste du 25 mai vous pouvez trouver le code pour travailler avec les adresses françaises… Suivez ce lien pour la présentation de Clément Hallet sur les bases d’utilisation de Google Maps API et celui ci pour la présentation sur des thèmes plus avancés (géocodage côté serveur, chargement asynchrone des marquers et calcul des distances).

Ici vous pouvez télécharger le code d’exemple utilisé dans la présentation Google Maps API - Basics. Notez que la clé utilisée (ABQIAAAAyzlxpGWavaTREVb4HcYiUhT2yXp_ZAY8_ufC3CFXhHIE1NvwkxSYK1OvuhDXV84EQwe2sBFw6vUHtg) marchera sur http://localhost - adaptez la à vos besoins, pour générer votre propre clé Google Maps API cliquez ici (vous pouvez consulter mon billet sur la gestion de configuration par virtual host pour résoudre le problème des clés google utilisé conjointement avec mod_proxy et PHP ).

Click to continue reading

Comments 2 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 »

28 05 2007

af83 @ Google Developer Day 2007 in Paris

Posted by: Alexandre Loureiro Solleiro in events

af83's very own Ori Pekelman, Clément Hallet and Louis Montagne will be hosting cool workshops at the Google Developer Day 2007 event here in Paris, on May 31st.

Ori and Clément will discuss the development of an event-scheduling tool to be used during electoral campaigns, based on our experience developing the one used by the French Socialist Party for this year's presidential race, during the Atelier Google Maps API - Outil de campagne politique (Desirsdavenir.com) workshop. This smart tool mashes up Google Maps with Phorum for authentication and sends invitation email to all people located in the region where the event will be held.

Louis, together with Benoît Sibaud, will host a workshop on Open Source and Google Maps API about the ongoing effort to build the application that will help manage co-working spaces, which is a rapidly growing network of "non-offices-nor-homes" (pardon my French) for developers and independents around the world. The workshop's name is Atelier OpenSource & Google Maps API - Réseau mondial de bureaux pour développeurs (Projet Coworking).

We'll do our best to share our experience at Google Developer Day with those of you who won't attend. In the meanwhile, if you are interested in any of the mentioned subjects don't hesitate do shoot questions and comments.

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 »

21 05 2007

Get remote ip address in PHP even when the user is behind a proxy

Posted by: Ori Pekelman in php

If you want to log what a user is doing, or have any other reason to get his ip using HTTP_CLIENT_IP to get the remote user IP is not at all enough, if the client is connecting through a proxy (for an example your site is configured behind an apache_mod_proxy) you should also test for HTTP_X_FORWARDED_FOR and fall back on REMOTE_ADDR or you might always be getting the proxie's IP.

PLAIN TEXT
PHP:
  1. function getIP() {
  2. $ip;
  3.  
  4. if (getenv("HTTP_CLIENT_IP")) $ip = getenv("HTTP_CLIENT_IP");
  5. else if(getenv("HTTP_X_FORWARDED_FOR")) $ip = getenv("HTTP_X_FORWARDED_FOR");
  6. else if(getenv("REMOTE_ADDR")) $ip = getenv("REMOTE_ADDR");
  7. else $ip = "UNKNOWN";
  8.  
  9. return $ip;
  10.  
  11. }

Click to continue reading

Comments No Comments »

21 05 2007

Make connected combo boxes to choose a time interval

Posted by: Ori Pekelman in javascript

Ever needed to select in a form a start and an end time (meeting, event etc..) this little bit of code will help you have a default interval (here 2 hours). When the first combo box changes the second one is set to two hours later.
As usual this javascript is unobtrusive and degradable, just call somewhere initHourCombos() to initialize it. Make sure you change the name $('start') and $('end') if your elements have a different id.

PLAIN TEXT
JAVASCRIPT:
  1. /* Make connected combo box 'End' jump 2 hours when combo 'Start' changes
  2. * (c) AF83 2007 Ori Pekelman
  3. * uses code found on http://www.irt.org/script/626.htm
  4. * uses the Prototype library for attaching the event to the combo (you can do without this.. just find the correct element)
  5.  
  6. <label for="start">From :</label>
  7. <select id="start" name="start" class="hour">
  8.   <option value="07:00">07:00</option>
  9.  
  10.   <option value="07:30">07:30</option>
  11.   <option value="08:00">08:00</option>
  12.   <option value="08:30">08:30</option>
  13.   <option value="09:00">09:00</option>
  14.   <option value="09:30">09:30</option>
  15.   <option value="10:00">10:00</option>
  16.  
  17.   <option value="10:30">10:30</option>
  18.   <option value="11:00">11:00</option>
  19.   <option value="11:30">11:30</option>
  20.   <option value="12:00" selected="selected">12:00</option>
  21.   <option value="12:30">12:30</option>
  22.   <option value="13:00">13:00</option>
  23.  
  24.   <option value="13:30">13:30</option>
  25.   <option value="14:00">14:00</option>
  26.   <option value="14:30">14:30</option>
  27.   <option value="15:00">15:00</option>
  28.   <option value="15:30">15:30</option>
  29.   <option value="16:00">16:00</option>
  30.  
  31.   <option value="16:30">16:30</option>
  32.   <option value="17:00">17:00</option>
  33.   <option value="17:30">17:30</option>
  34.   <option value="18:00">18:00</option>
  35.   <option value="18:30">18:30</option>
  36.   <option value="19:00">19:00</option>
  37.  
  38.   <option value="19:30">19:30</option>
  39.   <option value="20:00">20:00</option>
  40.   <option value="20:30">20:30</option>
  41.   <option value="21:00">21:00</option>
  42.   <option value="21:30">21:30</option>
  43.   <option value="22:00">22:00</option>
  44.  
  45.   <option value="22:30">22:30</option>
  46.   <option value="23:00">23:00</option>
  47.   <option value="23:30">23:30</option>
  48.   <option value="24:00">24:00</option>
  49.   <option value="24:30">24:30</option>
  50.   <option value="01:00">01:00</option>
  51.  
  52.   <option value="01:30">01:30</option>
  53.   <option value="02:00">02:00</option>
  54.   <option value="02:30">02:30</option>
  55.   <option value="03:00">03:00</option>
  56.   <option value="03:30">03:30</option>
  57.   <option value="04:00">04:00</option>
  58.  
  59.   <option value="04:30">04:30</option>
  60.   <option value="05:00">05:00</option>
  61.   <option value="05:30">05:30</option>
  62.   <option value="06:00">06:00</option>
  63.   <option value="06:30">06:30</option>
  64. </select>
  65. <label for="end" class="hour">to:</label>
  66. <select id="end" name="end" class="heure">
  67.   <option value="07:00">07:00</option>
  68.   <option value="07:30">07:30</option>
  69.   <option value="08:00">08:00</option>
  70.   <option value="08:30">08:30</option>
  71.  
  72.   <option value="09:00">09:00</option>
  73.   <option value="09:30">09:30</option>
  74.   <option value="10:00">10:00</option>
  75.   <option value="10:30">10:30</option>
  76.   <option value="11:00">11:00</option>
  77.   <option value="11:30">11:30</option>
  78.  
  79.   <option value="12:00">12:00</option>
  80.   <option value="12:30">12:30</option>
  81.   <option value="13:00">13:00</option>
  82.   <option value="13:30">13:30</option>
  83.   <option value="14:00" selected="selected">14:00</option>
  84.   <option value="14:30">14:30</option>
  85.  
  86.   <option value="15:00">15:00</option>
  87.   <option value="15:30">15:30</option>
  88.   <option value="16:00">16:00</option>
  89.   <option value="16:30">16:30</option>
  90.   <option value="17:00">17:00</option>
  91.   <option value="17:30">17:30</option>
  92.  
  93.   <option value="18:00">18:00</option>
  94.   <option value="18:30">18:30</option>
  95.   <option value="19:00">19:00</option>
  96.   <option value="19:30">19:30</option>
  97.   <option value="20:00">20:00</option>
  98.   <option value="20:30">20:30</option>
  99.  
  100.   <option value="21:00">21:00</option>
  101.   <option value="21:30">21:30</option>
  102.   <option value="22:00">22:00</option>
  103.   <option value="