#!@CPY@G HTML> <(|mot>O=un"> >hdtO*$ |Dhe>PurepH0MyQD15rAnqctanN/title> (s9l't6-6Hxcqs>H&`8# body!{ackg2ouOd-aodnr:bhsum)3"(!$ *hZ-texp-qnign:#enQeb9smlos e1~+j.yeO  (! h3.hucde{3nYa*cGfuR;Nkzd`kblTey r div.nav"{e`reif;10`h=E`  p.jafb{b@ckground,)aNeqrmhm@gesmdanoRIeg.id)3"jOu4mzDpeatzseet;5hktu,SaB&:ree6-li!N:5fTem r ph!2M}n3$jrgOxmr:dar{b,=e;t%xtdBoqtion: nsN0 ?* unvisited lijkc&"?/ ! azviRivem!coorxmediumbl}e,tEmtimera6ioo:.mnL $b?(`viZiadBmk4 *+ : 80f:(evas&color*b,}g{(uDxt-deooBdtnj:0wnderli.e}+kp2hovers b*/ c:itmtuz!lb.5n ma=Pie`4mYt-dg#gs@tionz uODePth>(. a)tIre miK30 */ $ 0>;s4s.m6=link rel='mcolgh(RF%&imeguqoloFo.jpg' type-'9d!e/x-iBob78/head> h=`$Lp> < syx7mhnasigrownl,amAca2gP`)i}g'up?t,xvi&!;bakw2Ow|,rereiu:reteyv>&~`s0;|p 4rp!n`HaQPU'(rty|e}fl=a42right;distlq{6l[rtqkakcCV{wnd-como:qrIz47<]a!href='xttp:'/Bvo`edtorK.h/&it.php' id<'dit# cvyle5'Cklgrshiue>Edit8/q</`an>*<0pyLeba!kgroun}-aage2uRhim#ges/l/goj0w+[facw2G|%sepaad8lo%seea6?Noae e4woS mn"wt thp1'dgtjsize=?4P+ name='saaba(StS': ,u&oj d{peY'Ffmyv' gnChick="oc7mAn/f-rm> ,`2>+ baT=T{gAf5Fa:`bo>~t+ xr%n?2`5|p://wwvrqd,ntWo;fJm?kndex.Ht2B_a2Nmuwork $a@druf}jhdU0s/+Vw5/Boqg.pVork.#ommembernhtL" id='ba7ReBome"a(Lgmjlr+c>(! px//www.brOa=feTsork.mm.oguph8nm"@i "leo?uL"0r4lu=bhyc6`q8l-fd"8\w$gut write2LoFin = |o'in 0 $C" b " (! " ! ( $ `#LE.>fel/glC9Ad(#l%inm')nuPe@idlq{!="non; ( $ 0x( ("Dcbumej?a#dG.e-e.]BxMd'| 'gutL')*sd{mu$astl{w @mnlieyX `( 0 `(0 dH0 document.getD|mf6c}d('wi&(rd`>hs2 i '9"in|}.= ->d ( $ "! Jj "(*,! elwe0k& Re1sionStosiE*readerLofi =] ]o'in") !{ % & rc}mDlt&FeUleeeN`B9At(gdmg!oL*"{"mBmspla !i2DKne"{ $ 0" " ,!" b` Eai|utdh%/:yHd'Djgott').st]lE>zby$  ]=dv c|q3;5'Nis|;D`tkRpdgY&jn,iTPd{k; min-wIhL<"2tplt4t9ia,-`]f`o2 a'=nf2' x3`rtem aE|miL)~c%nTD7R|ated Artybs/3>

=ahref='Data-DeFi}lXm`*qUmiEzvs-An}thD-PurePHP-ySL-PI.htm'>Data(bH,It StatgmmOt0and<*p>p8c m3s=nav' rt|e}/'><` #dap.Wz.14q.e=''>{aoo!Te`PuSeTH@"MySQL API,26su.t Set>+)/> p6=pl0s='nav'"s|xhe=%o6a x0df'QfministraTive aj|-upi}iqmSp@dgments-vijmtie,PrePerl-myAL/AXH>h4e'>@%Kdrative"avudlklitys6dgmIoa0uih 6he Qu@Cl|bsYC8A4e:<782 cla3sq' y.&-otmh2feC)|e/f-PrmpmStc/5ivn%nk,@wreRHX,MySYL EYn(|L'>Columl Xr/edies wht xu~*r?pw}QHP(MYWQL API/p>8p0alaws-%Nas>xhe-%%>4` href='LocKiwq-ibla-qld-Endi~gmiS!sio~-!thve-QuePHP-MySPLAPI>h4E'>o!Jngba Taleban E-d~g aM}8?>8a0jver?7RurePLP}Oy2 qSpa0ed-Statements.htm'>PurePHP MySQL Prepared
Statements

 

PurePHP MySQL Transaction

Using the PurePHP MySQL API Part 10

Foreword: In this part of the series, I explain how to code a transaction in MySQL.

By: Chrysanthus Date Published: 18 Jan 2018

Introduction

This is part 10 of my series, Using the PurePHP MySQL API. In this part of the series, I explain how to code a transaction in MySQL. More than one client can connect to the MySQL server at one time (duration). It is possible for two or more users to attempt to edit the same tables (rows) at a particular time. To prevent conflict and inconsistent result, the tables of interest have to be locked from the rest of the clients, so that the very first client uses the tables. When he finishes, the tables are unlocked. That is transaction. The tables will be locked again for the next user, and released after his transaction.

Transaction can be extended to the locking of particular rows of a table, but I will not go into that.

START TRANSACTION and COMMIT
In the following transaction code, a value is read from the pet table and placed in another row of the pet table.

    START TRANSACTION;
    SELECT @species := species FROM pet WHERE owner='John';
    UPDATE pet SET species=@species WHERE owner='Diane';
    COMMIT;

The transaction begins with the statement START TRANSACTION; and ends with the statement COMMIT;. In between, the SQL statement results are saved temporarily and not permanently. They are saved to disk permanently with the COMMIT; statement.

Statements that Cause an Implicit Commit
There are some statements that are always committed whether or not they are in a transaction. Typical of these are the Data Definition Language (DDL) statements that define or modify database objects. These are: ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE, TRUNCATE TABLE.

Transaction Illustration
The above transaction can be coded as follows (after selecting the database):

    $transSt = "START TRANSACTION;
                 SELECT @species := species FROM pet WHERE owner='John';
                 UPDATE pet SET species=@species WHERE owner='Diane';
                 COMMIT;";
        if (!query($transSt))
        {
            echo $Error_msg, "<br>";
        }

To see if Diane's specie has been changed from dog to cat (John's specie), just run the following code:

<?php

require_once("./Mysql.php");  

    #connect
    if (!connect("root", "secret", "localhost", 3306))
        {
            echo $Error_msg;
        }
    else
        {

            if (!select_db("PetStore"))
                {
                    echo $Error_msg, "<br>";
                }
            else
                {

                    $sel = "SELECT * FROM pet";
                    if (query($sel) !== true)
                        {
                            echo $Error_msg, "<br>";
                        }
                    else
                        {
                            for ($j=0; $j<$No_of_Columns; ++$j)
                               {
                                    echo $Fields[$j][0], ' ';
                               }
                            echo "<br>";
                            for ($i=0; $i<$No_of_Rows; ++$i)
                               &nbsE3f0.&j`;&ndsh8&Jbb;&nbst;lb1p; &.brQ'jbP.bs2; &njsP? #La;$fjRt;&n`sx: 9&fcsQ"~"sx:  &~b3x?&~pr0&nbcp{.nbrt?6l s`;fbbc2;"Orqp; &Rq#'o`x:.nBwp;2n2y`zbSqp&~"sp9&fbs?&~`sp;for ($j=8; j<Noof_Cohu}ms ++$j9".&nbs`;fvb3x;&n3p}~cp{.nbsp; &Nb`; frR0;&OjsP?&nfsh9j2S2>obsp;&ncs;  &fBs6&jt;nbsp;&n"rp 's2; 3.NFp;dnbr;doCsp; +&.jsp;&nBs+ ncp;lb9q; &l q+$nbsp9$fkzp"nBsgqp!{~br> &nb9plbzq&.spy 3&Nfsp;&nsP{~#[t;  &n`sx:&nbwp#4NzЯ~Bp; &nbSp>NB{&,Csp;.nR703n"Sp&~ {t;6lbsp;&ncS6n"sp  .nBwh;Fbbsp;"nrqp&p3;',sp?vd3$FfSp6fcsp;6n"{p;&lbyu36lsp3&Ngs;&ojP?&n"sp&n"sp ? 4die,dNmeb`TLe]fsSJY90;
~bsp;&nbsr;nobR0;Oc1;ffbsp3&NFs+.obsP;c2;&nbwt+                                       }
                                    echo "<br>";
                               }
                        }          

                 }
          
        }

    close();

?>

Thats 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