PHP: How to save binary string in MySQL 5.7

Question

How can I convert this binary string:

b"iphonée@3,;= ÑÑñe x"

To normal string?

I really need to have that string as a normal string, and not as a binary string.

I am having problems saving in MySQL this binary string. If the CSV that I decode does not have ñ, é or any of this non usual characters, I dont have problem saving. But when one of this characters is set on a cell, the string appears to be binary string and I get an error when saving on MySQL this binary string.

I get this error:

QueryException {#1780
  #sql: "insert into `seller_product_languages` (`seller_product_id`, `lang`, `name`, `description`, `description_html`, `bullet_html`, `bullet`, `meta_keywords`, `default`, `updated_at`, `created_at`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
  #bindings: array:11 [
    0 => 117
    1 => "es"
    2 => b"iphonée@3,;= ÑÑñe x"
    3 => "negro"
    4 => "negro"
    5 => null
    6 => ""
    7 => null
    8 => 1
    9 => "2020-03-02 18:40:56"
    10 => "2020-03-02 18:40:56"
  ]
  #message: b"SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE9e@3,;...' for column 'name' at row 1 (SQL: insert into `seller_product_languages` (`seller_product_id`, `lang`, `name`, `description`, `description_html`, `bullet_html`, `bullet`, `meta_keywords`, `default`, `updated_at`, `created_at`) values (117, es, iphonée@3,;= ÑÑñe x, negro, negro, , , , 1, 2020-03-02 18:40:56, 2020-03-02 18:40:56))"
  #code: "HY000"
  #file: "/home/vagrant/Code/PROJECT/vendor/laravel/framework/src/Illuminate/Database/Connection.php"
  #line: 664
  -previous: PDOException {#1779
    #message: "SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE9e@3,;...' for column 'name' at row 1"
    #code: "HY000"
    #file: "/home/vagrant/Code/PROJECT/vendor/laravel/framework/src/Illuminate/Database/Connection.php"
    #line: 458
    +errorInfo: array:3 [
      0 => "HY000"
      1 => 1366
      2 => "Incorrect string value: '\xE9e@3,;...' for column 'name' at row 1"
    ]

All tables and columns are 'utf8mb4' and collation 'utf8mb4_unicode_ci'

Environment: -PHP 7.2 -Laravel 5.5 -Ubuntu 18.04 -MySQL 5.7

That look like a utf encoded string! What makes you think its binary? — RiggsFolly
– RiggsFolly, Commented Mar 2, 2020 at 18:00
Because in a var_dump it shows with a b at the beginning like in the example I put. And this means "binary string". stackoverflow.com/questions/4749442/… — Joaquin Colella
– Joaquin Colella, Commented Mar 2, 2020 at 18:04
Does this answer your question? Can't convert 'binary' string to 'regular' string — Hafez Divandari
– Hafez Divandari, Commented Mar 2, 2020 at 18:05
No. I just tried it and I get the same binary string, in an array array:1 [ 1 => b"Ñandú [email protected]" ] — Joaquin Colella
– Joaquin Colella, Commented Mar 2, 2020 at 18:11

Joaquin Colella · Accepted Answer · 2020-03-03 15:50:17Z

0

I solve it changing all tables from utf8 to utf8mb4. And doing utf8_encode(..) to the string

Thank you all

answered Mar 3, 2020 at 15:50

Joaquin Colella

1431 gold badge1 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP: How to save binary string in MySQL 5.7

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related