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
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.
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();
?>
<?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.
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;c9cP;~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$n s+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;))
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;
'n3p:bsp;&nrs03&.bsQ;&nrs03&nrs0;6N${z:.obcp{(cloceh!9
ChryS?{gOpymn>
Related Links
Pure PHP Mailsend - sendmailPurePHP 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