So this is just something I scripted out a few moments ago. Most of the work is handled by the mysqli class file, MysqliDb.php, that you can find at https://github.com/ThingEngineer/PHP-MySQLi-Database-Class. Otherwise here is the code that makes use of it. It replaces every single column in a database table with a string or value without any non ASCII characters they may or may not contain. Of course its replacing the values with nothing, so if they were a symbol or something, they will be gone. This is good for cleaning up tables that you may have scraped and the values had that little black diamond with the ? mark in the center. If you don’t know what this means, read this, https://en.wikipedia.org/wiki/Specials_(Unicode_block) and you should understand. � is something you see when the current character set doesn’t have a character available.
/////////////////////////////
// Local DB Connection Settings
/////////////////////////////
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'dbname';
$table_name = 'table_name_here';
/////////////////////////////
// Create Conn for DB Connection (Yah, I know I connect twice, it doesn't matter, it's a fire and forget script)
$conn = mysqli_connect($host, $user, $pass, $dbname);
// Get from https://github.com/ThingEngineer/PHP-MySQLi-Database-Class
include_once 'mysqli.class.php';
// Create DB Class for mysqli using mysqli DB Class
$db = new MysqliDb($host, $user, $pass, $dbname);
// Function to automatically replace all fields...
// $conn is the original mysqli_connect connection, $db is the mysqli classes db object, $table_name is the string of the table name, 'id' is the auto incrementing ID used for the WHERE id = % part of the updates....
removeNonASCIICharacters($conn, $db, $table_name, 'id');
/**
* Remove all NO ASCII characters from DB tables
* @param $conn - mysql connection
* @param $db - mysqli DB class initialized
* @param $table_name - table name
* @param $id - defaults to "id", used to update WHERE id = $id
*/
function removeNonASCIICharacters($conn, $db, $table_name, $id = 'id') {
// Get target table columns and put into values array
$sql = 'SHOW COLUMNS FROM '.$table_name;
$res = mysqli_query( $conn, $sql );
$values = $res->fetch_all( MYSQLI_ASSOC );
// Query the table to loop through...
$q = "SELECT * FROM $table_name";
$r = mysqli_query( $conn, $q );
$count = 0;
while ( $row = mysqli_fetch_assoc( $r ) ) {
$id = $row[$id_name]; // Define $id for update
// Create Insert Array used by MySQLi DB Class
$insert_arr = array();
// Loop through values and add to insert_array... this will automatically create an array with all the column names and remove non ascii characters in the process without you having to define anything at all...
foreach ( $values as $k => $v ) {
$column_name = $v['Field'];
$insert_arr[$column_name] = preg_replace( '/[\x00-\x1F\x7F-\xFF]/', '', $row[$column_name] );
}
// Define the WHERE
$db->where( 'id', $id );
// Prepare update and execute
if ( $db->update( $table_name, $insert_arr ) ) {
$count++;
} else {
echo 'Updated ' . $count . ' records. Current ID Failed: ' . $id . '
';
echo 'Update failed: ' . $db->getLastError() . '
';
}
}
echo 'Non ASCII Character Removal Completed! ' . $count . ' records updated!
';
}
So that’s about it. Just replace the DB connection parameters, the table name, define the id tag of the table and all non ascii characters get removed. It’s pretty easy to use. I haven’t posted in a while and I figured this might be helpful to some! Enjoy!
P.S.
As a bonus, I figured I would throw in something that I use to define variables when I am working with code where I can’t work off the $row[‘column_name’] variable. It is a function that will output code to copy paste into your IDE. It’s occasionally useful.
Usage is:
outputTableColumns($conn, $table_name, true, $arr_name = ‘arr’, $row = ‘row’);
I know you can just also do $arr = $row; after a $row = mysqli_fetch_assoc($result); but where is the fun in that? Sometimes you really just need the variable names as an array or as variables.
That will output something like this:
$arr[‘id’] = $row[‘id’];
$arr[‘asin’] = $row[‘asin’];
$arr[‘original_dup_asin’] = $row[‘original_dup_asin’];
$arr[‘is_isbn’] = $row[‘is_isbn’];
$arr[‘is_duplicate_asin’] = $row[‘is_duplicate_asin’];
$arr[‘title’] = $row[‘title’];
$arr[‘clean_title’] = $row[‘clean_title’];
$arr[‘binding’] = $row[‘binding’];
$arr[‘edition’] = $row[‘edition’];
$arr[‘small_image’] = $row[‘small_image’];
$arr[‘author’] = $row[‘author’];
$arr[‘publisher’] = $row[‘publisher’];
$arr[‘publication_date’] = $row[‘publication_date’];
$arr[‘label’] = $row[‘label’];
$arr[‘manufacturer’] = $row[‘manufacturer’];
$arr[‘studio’] = $row[‘studio’];
$arr[‘number_of_pages’] = $row[‘number_of_pages’];
$arr[‘xml_string’] = $row[‘xml_string’];
Or if you do this:
outputTableColumns($conn, $table_name, false, $arr_name = ”, $row = ‘row’);
You get this:
$id = $row[‘id’];
$asin = $row[‘asin’];
$original_dup_asin = $row[‘original_dup_asin’];
$is_isbn = $row[‘is_isbn’];
$is_duplicate_asin = $row[‘is_duplicate_asin’];
$title = $row[‘title’];
$clean_title = $row[‘clean_title’];
$binding = $row[‘binding’];
$edition = $row[‘edition’];
$small_image = $row[‘small_image’];
$author = $row[‘author’];
$publisher = $row[‘publisher’];
$publication_date = $row[‘publication_date’];
$label = $row[‘label’];
$manufacturer = $row[‘manufacturer’];
$studio = $row[‘studio’];
$number_of_pages = $row[‘number_of_pages’];
$xml_string = $row[‘xml_string’];
/**
* Output Table Columns for easy copy pasting. This is a helper function to cut back on tedious work that is fairly useless to begin with.
* This functions takes a look at the column names in a table and outputs them so I can copy paste them into my code to modify the variables.
* @param $conn - mysqli $conn variable
* @param $table_name - target table name
* @param bool $use_array - defaults to true, if true will return the columns back as an array
* @param string $arr_name - defaults to arr, so if $array = true, the array name will be $arr
* @param string $row - defaults to row, so the output would be $column_name = $row['Field'];. Use this for whatever is assigned to mysqli_fetch_assoc
*/
function outputTableColumns($conn, $table_name, $use_array = true, $arr_name = 'arr', $row = 'row') {
$sql = 'SHOW COLUMNS FROM '.$table_name;
$res = mysqli_query( $conn, $sql );
$values = $res->fetch_all( MYSQLI_ASSOC );
foreach ( $values as $k => $v ) {
$column_name = $v['Field'];
if ($use_array == true){
echo '$'.$arr_name.'[\'' . $column_name . '\'] = $'.$row.'[\'' . $v['Field'] . '\'];
';
} else {
echo '$'.$column_name.' = $'.$row.'[\'' . $v['Field'] . '\'];
';
}
}
}
Ok that’s really it for the day!