0

SELECT * FROM mytablename WHERE sku = 'AZ-EFCQ-5' LIMIT 0 , 30

In MySQL table (description field) there are some weird characters in the database.There are almost 400+ records with all these characters out of 200,000 records it seems.

Example: The database record for one of the field shows the following when I run the query

Select * from mytablename where sku='az123'

Replacement for TOY TRAIN 746 N&W J   Replacement Light Bulb

But when I run the same query in PHP and print it, it shows 

Replacement For TOY TRAIN 746 N&W JÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Replacement Light Bulb

I tried using the following solution at

How can I find non-ASCII characters in MySQL?

This doesn't work, doesn't show any weird characters in Mysql, check the MySQL screenshot below enter image description here

Community
  • 1
  • 1
user580950
  • 3,558
  • 12
  • 49
  • 94
  • Could you please run the query `select ascii(substring(description, 36, 1)), length(description) from mytablename where sku='az123' ` and post the result? – EagleRainbow May 29 '16 at 17:41
  • @EagleRainbow Showing rows 0 - 0 (1 total, Query took 0.0005 sec), doesn't return anything – user580950 May 29 '16 at 17:46
  • That is strange, what do you think about discussing this in the chat room at https://chat.stackoverflow.com/rooms/113259/select-query-to-remove-these-characters-in-mysql? – EagleRainbow May 29 '16 at 17:56
  • Summary of the chat: the table is in charset `latin1`. The first character in the description, which gets converted, is at position 43 and has ASCII code 160 – EagleRainbow May 29 '16 at 18:24
  • Further digest of the chat: PHP version 5.5.34 is being used; php.ini does not contain any setting of `default_charset`, so UTF8 is being applied – EagleRainbow May 29 '16 at 18:32
  • Another digest of the chat: By default, the DB connection is being opened using charset `latin1` ==> this causes the wired characters – EagleRainbow May 29 '16 at 18:37

1 Answers1

1

As further analysis in the chat has shown that there really is something broken concerning charset handling between the MySQL DB connection and the PHP process running, we came up with the following "solution":

SELECT replace(description, char(160), ' ') as description 
FROM dumpdata WHERE AZ_Code = 'AZ-EFK9-6'

The hidden chars are in fact  s which somehow got introduced by an import process. As they do not carry any information, but are only disturbing, it was ok to just remove them by replacing them when reading the column (see SQL statement above). This isn't really the proper solution for the root cause - it feels more like a workaround. However, for the given purpose, it was considered to be sufficient.

NB for other readers who might have a similar issue: The DB connection is being driven by mysqli in object-oriented style. We tried to 

$conn->set_charset('utf8')

and confirmed via conn->get_charset() that this also was successful. However, the problem with the wired chars still appeared. Moreover, we also tried an explicitly cast with

SELECT cast(description as char character set utf8) 
FROM dumpdata WHERE `AZ_Code` = 'AZ-EFK9-6'

but also this did not trigger a proper charset conversion.

EagleRainbow
  • 931
  • 5
  • 22