Administ{amve ane`>eMty Statementr ith the Ptvgr.@MbQNIYtitle> 0 `h=s9letype=td>?aws2< ( $ f rmdy {backgrmeF%w|m1:yc?|U `  h1"{|dhtmilign:center;color:darkblue} @l `$pj3hAasz8[@i)|milI'n*c'j`Eb9rmgs:`abiblue} * 0 " (ev.,`v{margin:2`~ ! p.n!v Zcakgrnud-image:url,i}cgesli,cPiig.gig)bibkgrouzdxma- ]ea!t;Vxi$OmEaw:prm9Tiyt-al)gnge^vb} (!e:|+nk! 0ooIrtarkblue |'Xp-deco0a|Ion:0n/fd} h)/* ~~k[mpg|*MMnc *?J$$ avi1ited {cl/z:mediumcle; text,fckfai." .gne} $/:"visiT ({s" (++r&j# a8(gw@r0"{+-LJr:flmg$text-degobction })fupoIfmm* uSe2hgwers""()!// p @*`'qy`3OjFr:m%diTmpurple; textdeoqtHon: underhi~o$b/* cc|hv l#nKw ` * *0/'tyle>,l)fc Ral='icof'lse=Gaqes.leo.kxc0tiRe7Wope/x-yb/>=pgA@> *x`ea&ez &nb3p;-t6H,q1aI='SPWc cWyle=#f}eT>rightti#x.ay:none;baak/snTd-color:drk0ed'>a0(zef9xttp:/oBrqd-jE$j.Fomme$itphp' id='editg sUyle=7c/dor:whiteg>EAid>'a8/wqL|pqwUyle=ba!kergtnd-kmife:url Me%esluo&kpe)3aqkjor^ulb/aT>nk-bgpeat">Brwa ,j!$uDSC9/h0>  Brod etqj4/qfbr \Hxref= h|up:/&ws*bbao.t=kR,ckmo%-blsb4/iF=aMe>Fesmme a ?oHIs+i?$`0"ofuacp4?C: "0(}ihu&="Ittp://Ww<*sN=,ade-rj.koM/lei~.(|m" ol%jo(&L"DoGf0

Administrative and Utility Statements with the PurePerl MySQL API

Using the PurePerl MySQL API Part 5

Foreword: In this part of the series, I talk about Administrative and Utility Statements with the PurePerl MySQL API.

By: Chrysanthus Date Published: 17 Jan 2018

Introduction

This is part 5 of my series, Using the PurePerl MySQL API. In this part of the series, I talk about Administrative and Utility Statements with the PurePerl MySQL API. PurePerl stands for Pure Perl. It is Perl Software without C software underneath. You should have read the previous parts of the series before reaching here, as this is a continuation.

Administrative Statements
Here I explain how to create a user and grant him a privilege. I also talk about the SHOW TABLES statement. The other administrative statements follow the examples here. I will create the user john, with password, smith. After that I will grant him the select privilege to the pet table of the database PetStore. The following code segment does this:

            $SQL_cr_user = "CREATE USER 'john' IDENTIFIED BY 'smith'";
            if (!query($SQL_cr_user))
             {
             echo $Error_msg, "<br>";
             }
            else
             {
             echo $Message, "<br>";
             }

If you know how to construct the MySQL SQL statement, then the rest is simple: just place the SQL statement without the ending semicolon, as a string argument to the query function.

I tried the code and there was no feedback. It still means the user with password was created. The server sent no feedback. If there were an error, the server would have sent an error message, back. In your script, you will have to type a custom confirmation message in the place of the variable, $Message. You do not have to type any error custom message for $Error_msg, because the server would always send back an error message whenever an error occurs.

After creating the user, you can grant him the select privilege for the pet table as follows:

            $SQL_sel_priv = "GRANT SELECT ON pet TO 'john'";
            if (!query($SQL_sel_priv))
                {
                    echo $Error_msg, "<br>";
                }
            else
                {
                    echo $Message, "<br>";
                }

Again, if you know how to construct the SQL statement, then the rest is simple: just place the SQL statement without the ending semicolon, as a string argument to the query function.

The server does not send any feedback when the privilege is granted. However, if an error occurred, it would send an error message, back. So, it is up to you to type a custom confirmation message in place of $Message, for the ordinary user.

The granting of privileges does not necessarily have to be done at the same time the user is created; it can be done after.

The following code segment connects the user, john with password, smith to the server and selects the rows of the pet table where species = 'snake' OR species = 'bird':

<?php

require_once("./Mysql.php");

    if (!connect("john", "smith", "localhost", 3306))
        {
            echo $Error_msg;
        }
    else
        {

            #select a new database to work with
            if (!select_db("PetStore"))
                {
                    echo $Error_msg, "<br>";
                }
            else
                {
                    $sel = "SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'";
                    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>";        
                                }

                        }

                }

        }

?>

Remember, once you create a user or grant privileges, the effects are saved in the server; you do not have to create the same user or grant the same privileges the next time to connect.

After selecting the PetStore database, root (user) can go one to show all the tables in the database as follows:

                    $shw = "SHOW TABLES";
                    if (!query($shw))
                        {
                            echo $Error_msg, "<br>";
                        }
                    else
                     &ns&*X> lrXq{rv>6hbCr>nCw;bnrPp;Fnx3@Jvqp;&nf{`"obp; &n`sh:bfbq03.n@wx:ncp3&Nfsp&n sp;&nbs ;bGrQp.* or+&.jQp7nbSp6nbs0;&Obsp3&Nbs@9~bsp;&.bsQ;N nbt;tlbsp+&.bsP>nR,)$i=0+ da&dr8Mo_of_Row{:#-i8s2:&n wp#$Nfsp;F^h+&.bqP7'~b7x+$ncs;&jbcp;.obsp;&njsP?&nbsq;nb7p+nbsp;&nbs?&<`2,bs;&,bs0;$Ojrp;&lj9A7Ffbsx;jbSp&f"[|;n*jr;&obx;jbqp;'Nfsp:$jQ{>n"yp1'f#cpZ.n`sx:fnbSp&nrs03&obp?s,@v>&nb`;$F*@$.jsp3&Nfsr;.obsq~es.>7QsZ",pqx:'nfqpp'sPy~b3q:jsP=&fcsp;&ncsp; ncP;~pdf St&rgr+$obgp{lnbsp;&n"sp&gs3&Nfw+,(Bgr; &nbsx;bS2;6lbsp;&lb{q;6f"[t;&Nb@+`;"fgS`K=S;$j&l;$ o^o#|um.s+kf(<0rz.~`ss+/jcp{.nbS(bsp;nnBVp&rSp6jbcr;&fbQp37lbsp;obp;&nbSp6jbcr{jA!z>&~`sp;&n(sX&njsP?&fbSt3N/p+bs2;'nP;~bqp3'nbsp?&z cr  'ns3&Nfs0;&Obsp;&lbkQzfs`1&Nfsp; &bs;vra0{.Obsp;&nbrp&nbc{.bsr vbr>&jbcr;ncp;&nb{p"nbsp;&nbsP;~bsx;&jbp&~r383&n`sx:.~B~.mbsp;&bs2+&.j3p;nbsp;&nbSp>nBwb:nbsp;&obp;&nrP8?jbsp;&nbsu;l`sh&nbs@:@wx:&nbSp~';$nbptn"sp&bsx;&kP3~bsp;6z +r:n"9p.nwp;$njrp;&nbrlzsP?f~bx;6n"{;',3t?} p$n(rp; 3"Nvup+$bs:3*fA;"nrqp;fnbVp+$jBc+&njsP/&.jcp{.nBw;nbsл<(ptnJs/FjbRp; &nBs+nb1p;&bs27 M:p>Locp?&~bsx:$nhrx:&n`sz>.`sp;&nbcp{.nbsp:&bsp;&.br&oB+>oqpQ"ns0;&O"pWnbRp;&.`sY:&n"sp&nbsr;.oBs&0sp; &.bsQ; 'l{q'&s0q&fs6?6<`sS;>o"{p;'ns+"jvAbnb1p;&bS6;v9c1 &bs? uahn u&;$nx2sp>";~bsp;&js?& b`;&n"sP~fs&bs03nP;~bsp3=B../jq sp;~bqp;jbs;F(bRt&."shNfBgr?&~`sp;&nspY"bq`{ffCsr;oc1;&nfs`9n&`dn"sp6J&kr;&bs2;&nbw+$,"wp&.brr;.oBs+FnB&n Sp.. q;nbsp;"nrqt;6hbcr;&jbcr3&Of3& bs`9&obp;&.bcU{e>br>fnbRp;&nbc0s.ofs0;&Ocs; [&S~pnb1p{$nKrp;&obp"nBs+hbsp;&m`}:6lsP} &nbptnbsp;'ffSvy.obs0;&Ocs;nb3x;&nbsq;nb3p;~b3x; 
Whmn}ou n{/t kNo0the pat!uarbcolumns!ad nb72 of c/luLfsrufwsnd, the tdcka7$k t*ei~er doz A Ha{e$becomgq(a`ndy. Avas use thi t'chliyte whej imuhdOnot knoW xepa0piswl!P columosqn%)jC$ndknltms(rEpureDh Yo='Cm eomfX tHenn'r gg ho/p!Bda dm r%-oWD the$coma tr9ltDl x m%4f0'achalOe.>bz/<"z>I trMe2hbc/deinmYCcopuebA*$ h`pF petasbthd nly(tAFl8ko(uzg(eatabisE0 2i|Rtoben,Tzc|!is crr'gt>b.Vep ae'7'>far>?div>/var>
cqin,h~mjlock;Widdhx1pik ,SFri`d~E(adsb{gwn&d(=sindow>a$by%ooglE l _];,ts (99O /{!Rmpp>,-dI,1trofgSvaehty(STetemeot
&obp3&Lf{q;&nfs`&rsr;>o"{p;&nbcPz#81'fpfsen`5)qqD;USRIcE"pmu&qUO+<`r67n"Q+ ~br&|"'YJ,nbsp;jar{.nbsp;&nbcp{.nbsp?v"jqury$swli            }
        else
            {
                for ($i=0; $i<$No_of_Rows; ++$i)
                    {

                       for ($j=0;$j<$No_of_Columns; ++$j)
                           {
                               $column_name = $Fields[$j][0];
                               echo $Result[$i][$column_name],  ', ';
                           }
                       echo "<br>";        
                    }

            }

We do not know the number and names of the returned columns, that is why the inner for-loop is what it is. I tried the code in my computer and I had:

name, varchar(20), YES, NULL, NULL, NULL,
owner, varchar(20), YES, NULL, NULL, NULL,
species, varchar(20), YES, NULL, NULL, NULL,
sex, char(1), YES, NULL, NULL, NULL,
birth, date, YES, NULL, NULL, NULL,
death, date, YES, NULL, NULL, NULL,

You can precede the outer for-loop with the following for-loop to have the special column headings:

                for ($j=0; $j<$No_of_Columns; ++$j)
                   {
                        echo $Fields[$j][0], ' ';
                   }
                echo "<br>";

I tried that and I had:

Field Type Null Key Default Extra
name, varchar(20), YES, NULL, NULL, NULL,
owner, varchar(20), YES, NULL, NULL, NULL,
species, varchar(20), YES, NULL, NULL, NULL,
sex, char(1), YES, NULL, NULL, NULL,
birth, date, YES, NULL, NULL, NULL,
death, date, YES, NULL, NULL, NULL,

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