Problem inserting a long text in database column MYSQL PHP

Question

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!

We need more information. Are there any errors/warnings? Show us the relevant code ... — Jan Hančič
– Jan Hančič, Commented Feb 17, 2011 at 11:43
You shouldn't be worried about special chars if you're using something like mysql_real_escape_string... Are you using this? — jswolf19
– jswolf19, Commented Feb 17, 2011 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
– lucymtc, Commented Feb 17, 2011 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
– Álvaro González, Commented Feb 17, 2011 at 12:16

lucymtc · Accepted Answer · 2011-02-17 15:56:39Z

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.

answered Feb 17, 2011 at 15:56

lucymtc

211 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yoko Zunna · Accepted Answer · 2011-02-17 11:53:51Z

0

Try with the Datatype BLOB or LONGBLOB in mysql.

It will do your work.

edited Feb 17, 2011 at 11:53

answered Feb 17, 2011 at 11:47

Yoko Zunna

1,82414 silver badges21 bronze badges

3 Comments

Felix Kling Over a year ago

OP already said that it works if he tries it with phpMyAdmin. So it is more likely a problem with his code.

Álvaro González Over a year ago

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?

lucymtc Over a year ago

anyway BLOB and LONGBLOB don't work either. TEXT type really should work for this amount of chars

Álvaro González · Accepted Answer · 2011-02-17 13:24:41Z

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.)

answered Feb 17, 2011 at 13:24

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

1 Comment

lucymtc Over a year ago

I have set max_allowed_packet to 32M but still not working, thanks anyway for your reply

Dilpazir · Accepted Answer · 2015-09-01 05:29:58Z

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

edited Sep 1, 2015 at 5:29

answered Aug 31, 2015 at 8:01

Dilpazir

12 bronze badges

1 Comment

Taryn East Over a year ago

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.

Collectives™ on Stack Overflow

Problem inserting a long text in database column MYSQL PHP

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related