0

I am trying to save the binary of UUID as primary key in a table.

I am using the UNHEX(REPLACE(UUID(),"-","")) to convert the UUID to binary after removing the dashes from it. This works perfect in SQL query executed directly on phpMyAdmin. But in a SLIM php prepared statement it gives me the following error:

Uncaught Error: Call to a member function bind_param() on boolean.

$stmt = $this->conn->prepare("INSERT INTO profile(profile_id, profile_firstname, profile_lastname) values (UNHEX(REPLACE(UUID(),'-','')),?,?)");
$stmt->bind_param("ss", $profile_firstname,$profile_lastname);
$stmt->execute();

Note that if i write any static value (i.e. '123') instead of UNHEX(REPLACE(UUID(),'-','')) error is gone.

Dharman
  • 30,962
  • 25
  • 85
  • 135
EddyG
  • 2,051
  • 3
  • 22
  • 46
  • Execute `SELECT REPLACE(UUID(),'-','')`. Think what will return its UNHEX()... char-by-char... – Akina Mar 11 '20 at 10:06
  • @Akina No, I want to store it as binary this is why I use UNHEX. The query has no errors, I tried it on phpMyAdmin, but binding error exists. – EddyG Mar 11 '20 at 10:12
  • @Dharman Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'replace' – EddyG Mar 11 '20 at 10:15
  • You should have been using utf8mb4. To solve it you can just cast the value to the collation you need. – Dharman Mar 11 '20 at 10:17
  • 1
    [Troubleshooting “Illegal mix of collations” error in mysql](https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) – Code Spirit Mar 11 '20 at 10:18
  • @Dharman can you show an example? – EddyG Mar 11 '20 at 10:29

0 Answers0