bzcomp & mysql insert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
liderbug
Forum Newbie
Posts: 19
Joined: Tue Jul 23, 2002 2:18 pm

bzcomp & mysql insert

Post by liderbug »

I have a lot of text data I want to store via mysql-blob. (a lot). To that end I have tried the following:

<?php
foreach (data as line) {
cd = bzcompress (line);
mysql_query (insert into table values ('cd') )
}

I'm finding that when the string "cd" contains a (guessing) 0x0A (think thats a CR) I have EndOfData and the insert terminates. Makes for a mess when I then "select line from table".

Thoughts? Soultions?
A beer for the best answer :D
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Exactly what is data in foreach (data as line) ?
liderbug
Forum Newbie
Posts: 19
Joined: Tue Jul 23, 2002 2:18 pm

my data

Post by liderbug »

0~20070524140101>1~123456789>2~98765>3~45689>...999>09876
0~20070524140102>1~123456X89>2~98765>3~556789>...999>09876
0~20070524140103>1~123456789>2~98765>3~6,6,8,9>...999>09876

CVS format, delim = ">"
each line length 500 <-> 1meg
liderbug
Forum Newbie
Posts: 19
Joined: Tue Jul 23, 2002 2:18 pm

need coffee

Post by liderbug »

CSV not cvs :oops:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

And you want each line inserted as one record/field or do you want to split the csv into its fields?
liderbug
Forum Newbie
Posts: 19
Joined: Tue Jul 23, 2002 2:18 pm

Post by liderbug »

One entry.

As an aside: table 1 has 14 fields 0~2007, 1~1234, 2~... 13~22222 - size aprox 150 bytes (not what I have a problem with)

table 2 has 1 field everything else, 400 bytes + (and I mean "+") and thats what I need to compress.

a given record looks like this:

0~20070514>1~12345>2~54321> ... 13~1111>201~1>537~7783>603~0>214~7> ... >519~47362543>800~000000>...

Starting at: 201~1 above goes into table2 (a key is included in each table 1 <--> 2 ). The system was sized for all of the data to be flatfile.gz at 30TB. If I can't come close to the same data reduction I have to stay with flatfiles.
(as in why can't life be easy just once in a while) :?:
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You could store only table1 in mysql while keeping "table2" as flat files. Then table1 in the database holds the filename or the "table2" is named after an unique key in table1.
liderbug
Forum Newbie
Posts: 19
Joined: Tue Jul 23, 2002 2:18 pm

Post by liderbug »

That's sort of what I'm doing now. Table-1 has the first 14 fields (the header) and I need that for phplot etc. And file.gz contains the whole record.

Thanks for the help and the conversation -

av'a beer on me :wink:
Post Reply