stefangabos/Zebra_Database

query() don't return results if parameters are utf8 encoded

Closed this issue · 7 comments

jatubio commented

Hi,

My table especialidades is:

Id,Texto
--,-----
1,Pruebas
2,Cardiología

When Zebra_Database->query() method build this sql query:
SELECT * FROM especialidades WHERE Texto = 'Cardiologia', returns the second register.

But if the sql query is: (Cardiología)
SELECT * FROM especialidades WHERE Texto = 'Cardiología', don't return nothing.

The problem seems to be on line 2808 of Zebra_Database.php. On the call to mysqli_query. I don't know why don't return any results on the second sentence.

stefangabos commented

Here are some tips that might help:

First, make sure your PHP runs in UTF8 by having this line before anything else
header('Content-Type: text/html; charset=utf-8');

After connecting to the MySQL server, first query should be
SET NAMES "utf8" COLLATE "utf8_general_ci"

jatubio commented

Hi, thank you for your fast reply.

I made them both:

From my html code:

<head>
	<title>URGENCIAS Y CONSULTAS DEL NIÑO GRAVE</title>
	<!-- Required meta tags always come first -->
	<meta charset="utf-8">

From Zebra_Database log:

##################
# DATE:          #: 2017 Aug 21 19:02:02
# QUERY:         #: SET NAMES "utf8" COLLATE "utf8_general_ci"
# EXECUTION TIME:#: 0.000 seconds
##################

I'm always running set_charset('utf8') method of Zebra_Database

stefangabos commented

It's not enough having your output (your HTML) in UTF-8 - that has nothing to do with PHP, it's only for the browser. To set PHP iself to work correctly with UTF8 you have to call that header() function

jatubio commented

I have put header('Content-Type: text/html; charset=utf-8'); as my first line on my ìndex.php` file, but nothing has changed :(

Can i give you more information? What else can I do?

Thank you for your time :)

PS: I want to take this opportunity to thank you for this great library. It really is a great job!

stefangabos commented

What collation does your table have? Make sure it is UTF-8 (I set my tables to utf8_unicode_ci)

Here's my SQL

CREATE TABLE `especialidades` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Texto` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `especialidades` (`Id`, `Texto`) VALUES
(1,	'Pruebas'),
(2,	'Cardiología');

And here's my PHP

require 'path/to/Zebra_Database.php';
$db = new Zebra_Database();
$link = $db->connect('host', 'username', 'password', 'database');
$db->set_charset();
$db->query('SELECT * FROM especialidades WHERE Texto = ?', array('Cardiología'));

Also, be sure to save the PHP file with UTF8!!

This is working for me as expected

jatubio commented

Thank you a lot for your sample code.

I have tried it and it's working for me.

Now I have to investigate what is the difference between this code and the way I am using the Zebra_Database library within my project.

I'll tell you what I find.

Thanks, again!

jatubio commented

Okay, I found the cause of it not working well. And it has nothing to do with Zebra_Database.

The parameter for the query takes it from the url through a router and seems to be encoding it into something similar to utf8 but it is not utf8.

I feel really bad about the confusion and the time you've spent helping me. Again, thank you very much for the help.