Broad Network


Compressed SQL Statements with the PurePHP MySQL API

Using the PurePHP MySQL API Part 12

Foreword: In this part of the series I explain how to use PurePHP MySQL API to compress a SQL statement and send to the MySQL server.

By: Chrysanthus Date Published: 18 Jan 2018

Introduction

This is part 12 of my series, Using the PurePHP MySQL API. In the previous part of the series, it was the data value in a cell (column) of a database table that was compressed. That is expected when the PurePHP MySQL API (client) is in the same computer as the MySQL server. If both are in different computers and possibly distant apart, then the client (PurePHP MySQL API) should compress the whole SQL statement and send to the MySQL server. The MySQL server will decompress the SQL statement before carrying out the query. The compressed statement may still require a cell value to be compressed (before being inserted) - you will try that on your own. In this part of the series I explain how to use PurePHP MySQL API to compress a SQL statement and send to the MySQL server.

Compressing an INSERT Statement
The following code compresses an INSERT statement and send to MySQL server:

<?php

require_once("./Mysql.php");

        if (!connect("root", "secret", "localhost", 3306, 'compress'))
            {
                echo "$Error_msg";
            }
        else
            {
                if (!select_db("PetStore"))
                    {
                        echo $Error_msg, "<br>";
                    }
                else   
                    {

                            $ins = "INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Pin','Peop','brancher','m','2017-04-21',NULL)";
                            if (!query($ins))
                                {
                                    echo $Error_msg, "<br>";
                                }
                    }
            }

         close();

?>

This code is similar to other code samples, except for the presence of the argument, 'compress' in the connection expression (above).

Compressing a SELECT Statement
The following code compresses a SELECT statement and send:

<?php

require_once("./Mysql.php");

        if (!connect("root", "secret", "localhost", 3306, 'compress'))
            {
                echo "$Error_msg";
            }
        else
            {
                if (!select_db("PetStore"))
                    {
                        echo $Error_msg, "<br>";
                    }
                else   
                    {
                        $sel = "select * from pet where name='Pin'";
                        if (!query($sel))
                            {
                                echo $Error_msg, "<br>";
                            }
                        else
                            {
                                for ($i=0; $i<$No_of_Rows; ++$i)
                                    {
                                       echo $Result[$i]['name'],  ', ';
                                       echo $Result[$i]['owner'],  ', ';
                                       echo $Result[$i]['species'],  ', ';
                                       echo $Result[$i]['sex'],  ', ';
                                       echo $Result[$i]['birth'],  ', ';
                                       echo $Result[$i]['death'],  ', ';
                                       echo "<br>";        
                                    }
                            }

                    }
            }

         close();

?>

Note the use of the 'compress' argument in the connection expression, which enabes the SELECT statement to be compressed (before being sent over the network).

Compressing a DELETE Statement
The following code compresses a DELETE statement and send:

<?php

require_once("./Mysql.php");

        if (!connect("root", "secret", "localhost", 3306, 'compress'))
            {
                echo "$Error_msg";
            }
        else
            {
                if (!select_db("PetStore"))
 &fjST?&.brQ &nfs`9  +f*jBr;  +&zb3 fnbRp;nb1p;&nBs+ &rsp;&fbCt{.nbS24c0&rsp=$vhr;&fbSt9&fcsp;&nb3t;Wmbp6Nj@nbp;dnbsp;&nbs0;&Obsp;$nhrx:&nb3p;nb3pcs03&lf{ay&nCqt37lbsp&js7&ncs9&fs303nb{#$3tn"{;Sh $ rJ]m3g$!&qun?&.6;rrft;dquot;;.`syf!cp{.nbsp;&nBs+&nbst;6lbsp?&~`sq;jbcr&~Asp; "nv1`9nbsp{&nCsp;nb1r;.obsp;ncP;~bsp'{p"nsr9.oCp;dnbst;6lbsp;&n"s& cp;$sel = qt;deletm Fvom pap0kg0b!u='Pin.EcT,br>&ncz"gbt;&OCwd+&~b3x;&ncs; &Zbj?6^ `9&fcsx;jbsp;fbZ3&n`s`ffbsp;&nspy 1"yffbqp3nc`9fn`Zz*obQp7nqpq'ncs;.obsP;~"ypshf(!query($sel))
                         {
                                echo $Error_msg, "<br>";
                         }
                    }
            }

         close();

?>

This code is similar to other code samples, except for the presence of the argument, 'compress' in the connection expression. Any compression of a SQL statement, needs this argument.

A compressed statement sent by the API should receive a compressed result from the server (the return compressed result is not made known to you).

Note: The API does not yet support a combination of INSERT, SELECT and DELETE.

That is it for this part of the series. We stop here and continue in the next part.

Chrys

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