1

I am having a problem inserting a long text (around 9000 characters) with an INSERT query in php. I have already tested changing the column type (TEXT, MEDIUMTEXT,LONGTEXT) even thought TEXT type should do for 9000 chars. Also tested with a text free of any special chars or quotes.

I print my query and looks ok so I copy and paste into phpMyAdmin and the row inserts correctly. So the problem is coming when I try to INSERT from my php class.

I tested with a smaller text and this seems to work ok. I really need to get this solved. If anyone has the solution please let me know. Thanks!

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
lucymtc
  • 21
  • 1
  • 3
  • We need more information. Are there any errors/warnings? Show us the relevant code ... – Jan Hančič Feb 17 '11 at 11:43
  • 1
    What is the problem? What happens? – Pekka Feb 17 '11 at 11:45
  • You shouldn't be worried about special chars if you're using something like `mysql_real_escape_string`... Are you using this? – jswolf19 Feb 17 '11 at 11:50
  • no, I have no errors or warnings,and yes, I use mysql_real_escape_string, but just to make sure it was nothing to do with the text I cleaned it form special chars and quotes – lucymtc Feb 17 '11 at 12:04
  • @lucymtc You still haven't said anything about your problem, except that you have it. **You** know what your problem is but **we** don't. – Álvaro González Feb 17 '11 at 12:16
  • sorry, the problem is the row doesn't get inserted into the database – lucymtc Feb 17 '11 at 12:26
  • @lucymtc If you get a failed insert your PHP code should be able to detect it. So your PHP class has a serious bug. Of course, in order to to determine what the exact problem is you need access to the source code. – Álvaro González Feb 17 '11 at 12:39
  • if it had a serious bug it wouldn't get inserted with a smaller text and it does, I don't know if it could be because my query gets to long, is there any mysql command to manage a long query? – lucymtc Feb 17 '11 at 12:54
  • @lucymtc So the row doesn't get inserted **and** it gets inserted at the same time. Curious. – Álvaro González Feb 17 '11 at 13:19
  • no, it doesn't get inserted and inserted at the same time, that doesn't make any sence...What I say is that if the text is not to long it does get inserted correctly, buy if my text is 9000 chars (for example) the INSERT doesn't work – lucymtc Feb 17 '11 at 14:21
  • You need to show us the PHP code in which you're making the insertion. Nothing less can be expected. – Andrew Feb 17 '11 at 14:21
  • There is not really to mutch to see, query is ok, as the code works with a smaller text. "INSERT INTO `articles` SET `text` = '".$data->text."', `status` = '".$data->status."', `creation` = '".$data->creation."';"; – lucymtc Feb 17 '11 at 14:51
  • Are you sure $data->text is being generated properly? Do you have error reporting on and is mysql throwing any errors? Finally, SQL statements in PHP don't need a trailing semicolon. – Andrew Feb 17 '11 at 14:53
  • Thanks Andrew, yes $data->text is generated properly, I print my query and all seems to be ok, but the row doesn't get inserted, but if I copy/paste that same query taht I print into phpMyAdmin directly this DOES insert the row correctly, so the query is ok, it just has the problem when I execute it from my php class and only with a long text (small texts insert correctly), the strange thing is that there are no error or warnings coming up, so it is making it a bit difficult to debug...(I know doesn't need a semicolon, but it doesn't hurt to put it ;) ) – lucymtc Feb 17 '11 at 15:33

4 Answers4

1

I haven't yet found what is the problem inserting my long texts, but I have found a solution to turn around it, it is not very clean but at least it will work until I found the real problem, just in case anyone has the same issue this is what I did.

Split the text in peaces of 1000 chars, do my INSERT and the UPDATE the text field in the data base adding the peaces of text, so the code :

$textArray = str_split($text,1000);

foreach($textArray as $t){ $model = new Article_Model_UpdateText($id,$t); }

The query in Article_Model_UpdateText looks like this :

"UPDATE mg_articles SET text = CONCAT (text, '".$text."') WHERE idArticle = ".$id.";";

Hope this helps someone, thanks for all your replies.

lucymtc
  • 21
  • 1
  • 3
0

you should use mysqli_real_escape_string for storing long texts...it will be like

$variable= mysqli_real_escape_string(connection_variable,user input long text);

now you can store the $variable in your database by insert query, you should be storing $variable in longtext field in the database

Dilpazir
  • 1
  • 2
  • Hi and welcome to Stack Overflow. Thanks for answering - it's really appreciated. Just so you know, we have heaps of really super new newbies here on S/O, so it's often a good idea to be really explicit in explaining why and how your example will solve the problem. Don't forget that what may be obvious to you, might not be to them. – Taryn East Aug 31 '15 at 08:07
0

Try with the Datatype BLOB or LONGBLOB in mysql.

It will do your work.

Yoko Zunna
  • 1,804
  • 14
  • 21
  • 1
    OP already said that it works if he tries it with phpMyAdmin. So it is more likely a problem with his code. – Felix Kling Feb 17 '11 at 11:54
  • 1
    The smallest of the data types mentioned in the question (`MEDIUMTEXT`) can hold 16,777,215 characters. What's your reason to switch to `BLOB`? – Álvaro González Feb 17 '11 at 11:54
  • anyway BLOB and LONGBLOB don't work either. TEXT type really should work for this amount of chars – lucymtc Feb 17 '11 at 12:07
0

My crystal ball suggests the issue may be related to max_allowed_packet:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet

(But it's just a blind shot.)

Álvaro González
  • 142,137
  • 41
  • 261
  • 360