daily pastebin goal
43%
SHARE
TWEET

Untitled

a guest Mar 25th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2. ini_set('max_execution_time', '0');
  3. ini_set("max_input_time", '0');
  4. set_time_limit(0);
  5. error_reporting(E_ALL);
  6.  
  7. define('DBHOST', 'localhost');
  8. define('DBUSER', 'user');
  9. define('DBPASS', 'pass');
  10. define('DBNAME', 'database');
  11. define('DBPORT', 3306);
  12. define('DBPREFIX', '');
  13.  
  14. $db = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);
  15.  
  16. if ($db->connect_error) {
  17.     die('Connect Error (' . $db->connect_errno . ') '. $db->connect_error);
  18. }
  19.  
  20. echo 'Converting the content from latin1 to utf8 ...' . PHP_EOL;
  21.  
  22. $table = 'ads';
  23. $pk = 'id';
  24. $latin1Column = 'content';
  25. $failedColumn = 'failed';
  26. //Creating a new failed column for store state
  27. $db->query("ALTER TABLE {$table} ADD COLUMN {$failedColumn} TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL AFTER {$pk}");
  28.  
  29. $limit = 1000;
  30. $counter = 1;
  31. $i = 0;
  32. while($counter) {
  33.     $counter = 0;
  34.     $offset = $i * $limit;
  35.     $sql = "SELECT SQL_NO_CACHE {$pk}, CONVERT(BINARY CONVERT({$latin1Column} USING latin1) USING utf8) AS content FROM {$table} ORDER BY {$pk} LIMIT {$offset}, {$limit}";
  36.     if ($result = $db->query($sql)) {
  37.         if ($result->num_rows) {
  38.             $counter = $result->num_rows;
  39.             while($rows = $result->fetch_assoc()){
  40.                 $id = $rows[$pk];
  41.                 $res = $db->query("UPDATE {$table} SET $latin1Column = '{$rows['content']}' WHERE {$pk} = {$id}");
  42.                 if(!$res) {
  43.                     $db->query("UPDATE {$table} SET $failedColumn = 1 WHERE {$pk} = {$id}");
  44.                 }
  45.             }
  46.             $result->free();
  47.         }
  48.         $result->free();
  49.     }
  50.     $i++;
  51. }
  52.  
  53. $db->close();
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top