Non-ascii characters stops mysql statement

Question

This SQL has failed.

SQL is

INSERT INTO
    `migration_loose_terms`
    (`source`,`field`,`term`,`lastaccess`)
VALUES
    ('services.media','keywords','spermatozoïde','2012-04-25 12:00:00')

Because it contains a weird (ï) character.

What function do you use in php/mysql to weed out the nonascii characters to be stripped or to make that word somehow into mysql?

I make sure that everything uses UTF-8: my tables, the PHP-MySQL connection, within PHP, within the browser... — eggyal, Apr 25 '12 at 22:30
like this; Incorrect string value: '\xEFde' for column 'term' at row 1 — Average Joe, Apr 25 '12 at 22:44
for where statements always use N in front of the value you're looking for example: select value from table where value = N'value'; — Robert Sinclair, Mar 07 '17 at 01:22

score 2 · Accepted Answer · answered Apr 25 '12 at 22:47

If you want to strip the non ASCII characters you can use the following code :

<?php
$input = "Clean the non ASCII äócharacters.";
$output = preg_replace('/[^(\x20-\x7F)]*/','', $input);
echo $output;
?>

otherwise, have you tried to wrap the word in question with mysql_real_escape_string ?

Non-ascii characters stops mysql statement

1 Answers1