Broad Network


Compressed Data Values with the PurePHP MySQL API

Using the PurePHP MySQL API Part 11

Foreword: In this part of the series, I explain how you can use MySQL to compress a long value (kilo and mega-bytes) and insert into a table; I also explain the data types to use for the table.

By: Chrysanthus Date Published: 18 Jan 2018

Introduction

This is part 11 of my series, Using the PurePHP MySQL API. In this part of the series, I explain how you can use MySQL to compress a long value (kilo and mega-bytes) and insert into a table; I also explain the data types to use for the table. The compressed value (string) is always smaller than the uncompressed value (string), except for small uncompressed values.

Data Types
In this scheme, compression takes place at the server (not at the client API). The table data type to use for a compressed column is BINARY or VARBINARY or BLOB. A Western European Character (e.g. A) occupies a space in memory of one byte. Each of these data types stores bytes (not really characters) in a table cell.

BINARY
With the binary data type, the number of bytes in each cell for a column are the same. Spaces are padded to make the length of the byte strings, the same, in all the cells of the column.

VARBINARY
VARBINARY is similar to BINARY; however, with VARBINARY a string of bytes in a cell can be of any length up to the maximum, indicated; there is no padding with spaces. So, in a column of VARBINARY, the string of bytes in the different cells are of different lengths.

BLOB
The BLOB type is similar to VARBINARY, but it is for large value, such as a large image or a video file. The BLOB type is further divided into, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

COMPRESS() and UNCOMPRESS()
The function for MySQL server to compress value, just before saving, is COMPRESS(); the reverse function is, UNCOMPRESS(). The following SQL statement will compress 'any string' before inserting into the table.

    INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'));

The following SQL statement will select all the cell values from the column, Comp and uncompress them (before outputting).

    SELECT UNCOMPRESS(Comp) from Pet;

Illustration
Alter the pet table in the PetStore database with the following code:

<?php

require_once("./Mysql.php");

        if (!connect("root", "secret", "localhost", 3306))
            {
                echo "$Error_msg";
            }
        else
            {
                if (!select_db("PetStore"))
                    {
                        echo $Error_msg, "<br>";
                    }
                else   
                    {
                        if (!query("ALTER TABLE Pet ADD Comp VARBINARY(100)"))
                            {
                                echo $Error_msg, "<br>";
                            }
                   }
            }

         close();

?>

The following code will compress 'any string' just before inserting into the table, in a cell in the column, Comp:

<?php

require_once("./Mysql.php");

        if (!connect("root", "secret", "localhost", 3306))
            {
                echo "$Error_msg";
            }
        else
            {
                if (!select_db("PetStore"))
                    {
                        echo $Error_msg, "<br>";
                    }
                else   
                    {
                        if (!query("INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'))"))
                            {
                                echo $Error_msg, "<br>";
                            }
                   }
            }

         close();

?>

The following code will select all the cell values from the column, Comp and uncompress them, before sending to the PurePHP MySQL API.

<?php

require_once("./Mysql.php");

        if (!connect("root", "secret", "localhost", 3306))
            {
                echo "$Error_msg";
            }
        else
            {
                if (!select_db("PetStore"))
        fn`Rx:&~b3x;&obp;&n"s`fdc;;nbsp; &nbu0"%bsp; &Nb`;0>d.bsQ;&nbsP;~bsp;&.bsQ;  &njSP6nb{p"nbrxjbsp; 3&NnsP&n sp:$k2:#!;&,bcp{.nbsp; 2.F`; &rwp+$Nbq`3enbs;$$jStdco $MrRk2_}Rg& tuv;&Ltrb$g4fX6Nt;+=">&jcp; &mbq+ncp;&ncs;f&bs1;_bgp+,nGs`9&nbsq;nBgp{.nbsp; &lA{} &Fgsp; &nbst;6lbs1;OBwp;fjbBr; ]&jbcj;dbbp;&nbap;FGCty&n"sQ"~bsp;&nfs`9 "jba23 "nrqp&n sp&rs; c9 cP;~bsp{ehRu$n"stvlbpdr;<r6~b3x;&nbst6rsu;Lb`+&>j3x;&nr03fnkr;lwp:$jsp;&nbSp?nwp;&nb{p$n~`ByBp; &ffSd9 n"1p  {-b>$N~r03"nrqp;fnbVp$ns+b12&nspy&nbrp&ncs;&nBs+ nzp;&,bqp3#frQt;  &jbcr;nfp;dnbsr;.obsp;&nbsr;.obsr9.gqs82 "jz`R&nbsx;Ib (!queRy7qn4SELECT$U^AOMRRMRS(kod! Fvom P%tPu-t;))&ob`; &nBs+&nfs`9nbsp;nB5p; +&&jsu2nb1p;&nspX&nbs`;ffbsp;&~b3x;N .obsp; ?&~`sp; &nBs+&nbwp/$~bcxz.nbsp; 4n*zp?&~hsP?ncp; &nb1p3nb{p"nbsp;&nbqp3'nbsp:eho $Evr~twpO "<r$u|{ouy,*:"obp3"Ntsx3'jb{2"n"sTbc3{nkp;&n"spz&SsP;nB3+"nSqt+n#p;nbsp;   &nbs1;Mb{q?&~ps0;&N&sp&fbs;boRTp;&n`sx: +&.jst;6bs2;&nbsr;>o#{`,C>lbQ;dn`3x:bs2;&nbs`;fbfcb96np}d~`sp"dqx: &ndsh8&f"St &.jsp&f!rp 3&Nfsp;.nBwp; &rsQ;&g"p6 sp:&cs?g~Zp["f"t .jsP;ds0br>&~b38;&b3Y;jb!r&N${r*ob{@Vb ;&Obsp;6o"p+$nbsp;  &n`sx:ncp;&bs2;nb1p;4n*z&rsp;&nbsx;jbp;$nbRp; &nbst:6bsr;ob1p;j)z fbt; {0bR8fnbCt{.nBs+ncx:$nbrR.rrp&njsR?oc;&nfc`y.nrs3&,Ss4#&n`sxz&nCsp;6l#s;&Nb`9&dc{Q;~bsp;&nbSp4njrp;&nbsx;jj3P?.bsq;zbcr;'nwp+$nbsp;&."{Q:bncR;.obsp&~A3p:b2z.Ofsp;nnBW&,bSpvor$)4=8: Id{Noof_Rmg{ I +9<"z6&Nfsp;  {nC1p;Nb`;&."sS.ob{02"{p&j cr;&fbSt;&nbst;vnfZ!9&GbSt+&.*sp ".rqP? &nbsx;zb3x;&Nbs`+d.hsx$zrp9&fsr0 3&nf`?&~hsP?&nFs) &nb7p+nb{0*OBp*nBwp;  &jjcS?nRUp0[<`*obrp.nBwp;&jbcr;6n"[p6bs2" vAr/hy$.jsp3Nn1?Ocz:./bsQ;'nsp; 9&fcsp;&nrs01&fksP?6n"{p;&nbzp"brG6#$nbsr;.obsp;&n"sp.ncPjgs8&bsq;nr1r;obsp;$njrp;  &n`sx:&obr;.obSp&n"{p;"r12 *zp;dnbsp=&vaQp7bsp;&nBs+$numNiEjD{- counth$FHld1);< r>&nbcP{~bsp3&Nf3P;~{p"n sB+7zs3&,bsp;fNb`;&n`qhgrsp;&nbs0;&bs4:."{p&n sP{~Cqp7'~`sp;&nbsP;~zr23.obpf.bRQ;fnfR`!&nsp; &nbsX2{p; &nbp nfs`9 &nBs+&zb#z8&ccr;&nCst+  &n st;6lbsp;&oc;6|b3x;&nb:$r($j50$jf,|9$dtei.d1)"c*el)ar> &fbSt3&fsA;fr103 37N?d+Ud"sp&Nb`{&nCqp3gnbRp;&nbsx;jbs`;ffvS 6jbcr?&n`3x9&Fc`;6n"{p[.s'p{nbsp;&nbs2;&NBwp; &"s2"nrqp;  &nbqp3'.bsQ; &fbSt; nb!p*. sp&ns;&.jsP+>jsp;&nspyn1Py~bsp;&nb3t;7dCt; s&NGsp; {8bb<nbcqynb{p"n sp+&.jsp;&nb3p;nbbp.fa;Nbsp;&n"p gnsp+&jw`;&nbsP;zbcr;nb1p;                                  $fieldName = $Fields[$j][0];
                                                echo $Result[$i][$fieldName];
                                                if ($j != ($numFields-1))
                                                    echo  ', ' ;
                                            }  
                                        echo "<br>";     
                                    }
        &~b3x;'nsp;&Nb`{&nCsp9&fsS06nbsp;&nnsH=jb{p2n$[h6z`cr;&lb{q;&ncs;&n`sz:&iBos?&v`St;&nbsv>M `;.nBwp;  &nbr2.NjsQ;6"{2;nFc+js0X&Obs`;ff`sx:&bs2;&nb3p;nbsp;&n"sp +&.jsp3&Nfsp&n`pdfs"(&bsP?&lbzq&nfs`9}>Bz6nbsp;&lb{q;&~b3x;&nfsh9jbsp9&fcsp;nb&,bsp; ngp;&n`sp:9
'n3p:bsp;&nrs03&.bsQ;&nrs03&nrs0;6N${z:.obcp{(cloceh!9
ChryS?{gOpymn>nar id='DL3>~'~`r~4dhp `e9'#r.%<'>'); }());

Related Links

Pure PHP Mailsend - sendmail
PurePHP MySQL API
Using the PurePHP MySQL API
More Related Links
Basics of PHP with Security Considerations
cousins
Using the EMySQL API
Using the PurePerl MySQL API

BACK NEXT

Comments

Become the Writer's Follower
Send the Writer a Message