Advanced Search

Transliterate existing cyrillic slugs (post_name) in wordpress

/ Published in: PHP
Save to your folder(s)
While working on the iteration of a blog with russian contents we had to change the slugs from cyrillic to transliterated russian.

[RusToLat](http://mywordpress.ru/plugins/rustolat/) is a great plugin that does just that, but unfortunately it only does the transliteration for new or "edited" posts (i.e. you have to open the post at least once and "edit" the permalink, then it will be transliterated).

Since this blog more than 500 posts this manual updating wasn't an option so we wrote this simple script. Maybe it will save somebody some time.

You'd probably better back-up your database before updating it (google for mysqldump syntax).

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. <?php
  2. define('BASEPATH','');
  3.  
  4. // just a helper func to see how long it took to process slugs
  5. function microtime_float(){
  6.     list($usec, $sec) = explode(" ", microtime());
  7.     return ((float)$usec + (float)$sec);
  8. }
  9. $time_start = microtime_float();
  10.  
  11. // replace %root% and %secret% with your database credentials
  12. $db = mysql_connect('localhost', 'root', 'secret');
  13. // replace %wordpress_db% with your database
  14. mysql_select_db('wordpress_db', $db);
  15.  
  16. $sql="";
  17.  
  18. // this dictionary is copied from the source of http://mywordpress.ru/plugins/rustolat/ 
  19. // which is great, but only works for new posts
  20. // in the situtation when I had > 500 posts it was easier to write this script
  21. // than to go one by one updating slugs
  22. $iso = array(
  23.    "��"=>"YE","��"=>"I","��"=>"G","��"=>"i","�"=>"#","��"=>"ye","��"=>"g",
  24.    "��"=>"A","��"=>"B","��"=>"V","��"=>"G","��"=>"D",
  25.    "��"=>"E","��"=>"YO","��"=>"ZH",
  26.    "��"=>"Z","��"=>"I","��"=>"J","��"=>"K","��"=>"L",
  27.    "��"=>"M","��"=>"N","��"=>"O","��"=>"P","� "=>"R",
  28.    "�¡"=>"S","�¢"=>"T","�£"=>"U","�¤"=>"F","�¥"=>"X",
  29.    "�¦"=>"C","�§"=>"CH","�¨"=>"SH","�©"=>"SHH","�ª"=>"'",
  30.    "�«"=>"Y","�¬"=>"","�­"=>"E","�®"=>"YU","�¯"=>"YA",
  31.    "�°"=>"a","�±"=>"b","�²"=>"v","�³"=>"g","�´"=>"d",
  32.    "�µ"=>"e","��"=>"yo","�¶"=>"zh",
  33.    "�·"=>"z","�¸"=>"i","�¹"=>"j","�º"=>"k","�»"=>"l",
  34.    "�¼"=>"m","�½"=>"n","�¾"=>"o","�¿"=>"p","��"=>"r",
  35.    "��"=>"s","��"=>"t","��"=>"u","��"=>"f","��"=>"x",
  36.    "��"=>"c","��"=>"ch","��"=>"sh","��"=>"shh","��"=>"",
  37.    "��"=>"y","��"=>"","��"=>"e","��"=>"yu","��"=>"ya","�«"=>"","�»"=>"","â��"=>"-"
  38.   );
  39.  
  40. // this is a name of file that will be generated to use later to actually update our DB
  41. // it can be anything you want and by default it will be created in the same directory
  42. // where this script is
  43. $myFile = "slugs_fix.sql";
  44. $fh = fopen($myFile, 'w') or die("can't open file");
  45.  
  46. # slugs
  47. $q = mysql_query("select * from wp_posts where post_status = 'publish' and post_type = 'post'", $db);
  48. while ($row=mysql_fetch_assoc($q)){
  49.  
  50.         $slug = $row["post_name"];
  51.         $id = $row["ID"];
  52.  
  53.         // post_name is url-encoded � it's stored in a format
  54.         // such as %D1%85%D0%B2%D0%BE%D1%81%D1%82
  55.         $slug = urldecode($slug);
  56.         // translate the string
  57.         $slug = strtr($slug, $iso);
  58.  
  59.         $sql .= "update wp_posts set post_name = '" . $slug . "' where id = '" . $id . "'; \n";
  60.  
  61.         $stringData = $sql;
  62.         fwrite($fh, $stringData);
  63.  
  64.         $sql="";
  65.         $slug="";
  66.  
  67. }
  68.  
  69. mysql_close($old);
  70.  
  71. fclose($fh);
  72.  
  73. $time_end = microtime_float();
  74. $time = $time_end - $time_start;
  75. echo "Transliterated slugs in $time seconds\n";
  76.  
  77. // okay, the file is written 
  78. // and now it can be used like this:
  79. // mysql -u root -p wordpress_db < slugs_fix.sql 
  80. // after issuing this command your slugs should be updated

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.