php/mysql : How to insert a gzcompress-ed string into a text mysql field?

Question

I have been trying to compress and store a json encoded string into mysql, but I am getting "unexpected /" errors.

I also tried to use addslashes like this:

addslashes(gzcompress(json_encode($mystring)));

And to display

json_decode(gzuncompress(stripslashes($mystring)));

But it fails on insert with the error I mentioned.

I read somewhere a string with gzcompress should be stored as a blob, but I was hoping there is a way to store it in a mysql text field so I dont have to mess with the db.

PS: Some asked for full error message here it is:

Warning: Unexpected character in input: '\' (ASCII=92) state=1

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\x9C\xED}\x8Br\xDB...' for column 'field_text_value' at row 1.

mysql_real_escape_string should be used instead of addslashes (not saying that will fix this problem; I mean in general). addslashes is not aware of character sets, whereas mysql_real_escape_string is actually aware of the database connection and can more accurately escape data. addslashes() is one of the many things PHP did trying to help coders that should have never been done. — Corbin
– Corbin, Commented Dec 15, 2011 at 7:02
Thanks Corbin, will check that out as well. By the way whoever minus oned this please give an explanation why. — giorgio79
– giorgio79, Commented Dec 15, 2011 at 7:02
Storing "big arrays" in a relational database makes very little sense. Asking questions providing them with not a full error message but rather with a stub says '"unexpected /"' makes even less sense. It is even impossible to tell, what part of the code issues this stub — Your Common Sense
– Your Common Sense, Commented Dec 15, 2011 at 7:03
@Corbin mysql_real_escape_string is not aware as well, unless you explicitly tell it otherwise. thus, just a recommendation "use it, not addslashes" will actually make no change at all. — Your Common Sense
– Your Common Sense, Commented Dec 15, 2011 at 7:05
Are you sure about that? "Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query()." And as for the optional connection parameter: " If the link identifier is not specified, the last link opened by mysql_connect() is assumed." — Corbin
– Corbin, Commented Dec 15, 2011 at 7:07

Francis Avila · Accepted Answer · 2011-12-15 06:59:54Z

11

Store it as a BLOB. Even if there were a way to store it in a VARCHAR or *TEXT field in a way that survives a round trip, it would be a horrible way.

Are you sure you need compression anyway?

You can also make MYSQL do the compression, e.g. INSERT INTO mytable (compressed_json) VALUE (COMPRESS('[\"the json\"]').

answered Dec 15, 2011 at 6:59

Francis Avila

31.8k7 gold badges63 silver badges99 bronze badges

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

4 Comments

giorgio79 Over a year ago

Thanks, I am not sure, but gzcompress looked like a nice way to compress a string. Will investigate the blob way.

Francis Avila Over a year ago

gzcompress produces binary data. BLOB column data type stores binary data. If you're not using gzcompress there's no point using BLOB. (All "blob" says is "mysql, do not try to rewrite these bits. This is not text, it has no encoding". How you put your bits in, whether with gzcompress or COMPRESS(), is up to you.)

giorgio79 Over a year ago

Thanks it works with BLOB. Much appreciated. It takes up much less space with gzcompress.

giorgio79 Over a year ago

Final numbers are out, managed to compress a table with 30 000 records, 1.7 GB, big json encoded arrays to 300 MB :D WOW Awesome. Added a new blob field, imported text field and gzcompressed it. Then deleted the old field.

Richard · Accepted Answer · 2011-12-15 06:57:34Z

-2

Why would you want to add a gzip compressed string into a database? Why don't you just store it as:

mysql_real_escape_string(json_encode($myArray)))

answered Dec 15, 2011 at 6:57

Richard

4,4267 gold badges38 silver badges58 bronze badges

1 Comment

giorgio79 Over a year ago

It's a big array, I was hoping compression would make the arrays json encoded string even smaller.

Collectives™ on Stack Overflow

php/mysql : How to insert a gzcompress-ed string into a text mysql field?

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related