Skip to content

Instantly share code, notes, and snippets.

@Daniel15 Daniel15/fix.php
Created May 11, 2014

Embed
What would you like to do?
Fixing badly-encoded UTF-8 characters in MySQL database
<?php
// Go home PHP, you're drunk.
// No charset here
$old_db = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
$old_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Charset here
$new_db = new PDO('mysql:charset=utf8mb4;host=localhost;dbname=database2', 'username', 'password');
$new_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $old_db->query('SELECT * FROM items');
$row = $result->fetch(PDO::FETCH_ASSOC);
$columns = array_keys($row);
// [foo, bar] => [':foo', ':bar'] for query params
$placeholders = array_map(function($column) { return ':' . $column; }, $columns);
// INSERT INTO items (foo, bar) VALUES (:foo, :bar)
$insert = $new_db->prepare('INSERT INTO items (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $placeholders) . ')');
do {
$insert->execute($row);
echo '.';
} while ($row = $result->fetch(PDO::FETCH_ASSOC));
echo "\nDone";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.