mishamosher mishamosher - 4 months ago 6
MySQL Question

Fetch data with PHP and MySQL of a "text" column with special characters on it

I've a MySQL database that has this definition on one of its tables (theTable)

CREATE TABLE `theDatabase`.`theTable` (
`id` INT NOT NULL AUTO_INCREMENT ,
`thecolumn` TEXT NOT NULL ,
PRIMARY KEY (`id`) );


I'm trying this with PHP:

/*File executeSelect.php*/
mysql_connect("theServer","theUser","thePassword") or die(mysql_error());
mysql_select_db("theDatabase") or die (mysql_error());
$q = mysql_query($_REQUEST['query']);
while($e = mysql_fetch_assoc($q))
$output[] = $e;
print(json_encode($output));
mysql_close();


If I do a
executeSelect.php?query=select * from theTable
, the information shown for the column
thecolumn
is
NULL
if
thecolumn
has special characters. I've
thecolumn
defined as
text
because I need to store large texts on it (500~1000 letters).

For example, a
executeSelect.php?query=select * from theTable
with
id=1;thecolumn=asdf
in
theTable
works OK, but the same executeSelect with
id=1;thecolumn=ásdf
in
theTable
gives
NULL
for
thecolumn
.

I've tried to execute the same query using the MySQL console and it works OK. I'm using Apache 2.4 and PHP 5.3.

What I'm misshing here that my php code doesn't retrieve data from
text
columns of MySQL in a right way?

Answer

After 2 years, it bothers me that there is no answer in this question that solves my issue.

As pointed by Mike W in the comments of the question, the issue was with encoding in the sql client.

If using MySQLi, something like described in PHP: mysqli::set_charset - Manual can be used.

In my scenario, it was just a matter of doing a simple $mysqli->set_charset("utf8") right after opening the connection.