1

I have a codeigniter controller that receives json content from an API and inserts it to a mysql longtext field unchanged. Everything's been running smoothly for a while, but recently i've been noticing some of the content is being truncated. Here are two examples.

The table being inserted has three fields

id int 
data longtext 
date_added datetime

I am receiving the data and directly adding it to the database using the active record insert function. It looks like that

$this->db->insert('received', array('data' => $data, 'date_added' => date('Y-m-d H:i:s')));

Using the profiler, i monitored the queries and found two faulty ones:

INSERT INTO `received` (`data`, `date_added`) VALUES ('{\"status\":{\"lastMaintenanceAt\":0000,\"code\":304,\"period\":900},\"items\":[{\"permalinkUrl\":\"http://example.com\",\"updated\":0000,\"postedTime\":0000,\"summary\":\"Man\'s guarantees are like his words.\",\"title\":\"By: John\",\"content\":\"<p>Man&#8217;s guarantees are like his words.</p>\",\"id\":\"http://example.com\",\"actor\":{\"permalinkUrl\":\"\",\"displayName\":\"John\"}}],\"title\":\"Comments on: Banker refuses to give ‘Written Guarantee’\"}', '2012-04-08 00:28:29')

and

INSERT INTO `received` (`data`, `date_added`) VALUES ('{\"status\":{\"code\":304,\"period\":900,\"feed\":\"http://example.com\"},\"items\":[],\"title\":\"Comments on: Making her cry…\"}', '2012-04-08 00:49:35')

The queries seems alright. But only part of the JSON is making it to the table in the first case, it is truncated after "[...] refuses to give" and in the second after making her cry.

The queries are not returning an error and the date is being inserted properly. Moreover, if i copy the queries and execute them in a mysql command prompt, they will insert the full text. These queries a one a few of hundreds of other successful ones.

Any idea what might be the problem?

thank you

applechief
  • 6,615
  • 12
  • 50
  • 70
  • If you do a print $this->db->last_query() and run that via phpmyadmin or command line, the text is fully inserted and with AR it's not - can you confirm this? – stef Apr 08 '12 at 17:38
  • Yes, the AR query insert looks like that: `[...],"title":"Comments on: Making her cry` the command line insert is `[...],"title":"Comments on: Making her cry…"}` – applechief Apr 08 '12 at 17:47

1 Answers1

2

All of the faulty queries share one thing in common: they've got "special characters" which MS Word inserts, e.g. ‘, ’ and …. If you can convert / remove these, then your problems should go away.

Update

For a CodeIgniter solution to this, you could load the Text Helper library and then use the ascii_to_entities function on any strings, e.g. 

$this->load->helper('text');
$desc = ascii_to_entities($desc);
stealthyninja
  • 10,343
  • 11
  • 51
  • 59
  • There are a couple of Stackoverflow answers that could point you in the right direction on how to do this, for instance http://stackoverflow.com/questions/1262038/how-to-replace-microsoft-encoded-quotes-in-php and http://stackoverflow.com/questions/9897888/php-change-ms-word-special-characters-l-p-s-to – stealthyninja Apr 08 '12 at 21:48