MySQL Query only works within WordPress

Question

I tried extensively to find an answer but my question seems to be too specific.

My query contains latin characters:

$mydb->query("SELECT cotação FROM compras");

It's working fine when called in a WordPress shortcode, but in a custom php on the same site it gives me this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??o FROM compras' at line 1

Now, I don't want to just remove the characters because I know the query works and I'd rather understand the problem.

So what's going on here? Thanks.

Answer 1

To get this to work on my local machine, it would not work until I set the collation and NAMES to UTF8. It appears from the wealth of data I find in searching that this is the primary problem people encounter with non-ASCII characters.

To get it to work with my local PDO instance, I did the following:

    $pdo->exec("SET collation_connection = utf8_bin");
    $pdo->exec("SET NAMES utf8");
    $all = $pdo->query("SELECT * FROM temp_cotação LIMIT 5")->fetchAll();

You can find more information here:

PDO cutting off strings at a UTF-8 character

It seems to me that it would be better to do this globally rather than per connection...I found this to do the SET NAMES utf8 globaly for each connection:

How to make PDO run SET NAMES utf8 each time I connect, In ZendFramework

To do the same thing in mysqli_, look here:

php mysql SET NAMES 'utf8' COLLATE 'utf8_unicode_ci' doesn't work with mysqli (solved)