Broad Network


This is part 9 of my series, Using the PurePHP MySQL API. In this part of the series, I explain how to code multi-statement and stored procedure. A multi-statement is more than one statement, sent in a query function; the statements are separated by semicolons. A stored procedure is a special kind of multi-statement that is saved in the MySQL server hard disk. You should have read the previous parts of the series before coming hur$#0thi3 iR adcMtin5aHn,MuldImdete/eot/stronb>`v>FbeBklo+ng is!a ex!oEof a m}lTm-statum%Fwnding (af|eR$el'ctinf hm#diu!baRe)28Bf<6bZ;&nBSMky?z`3P9fc3x;%slSt=6quot;SELECT : ZOM pet WLDB d-iVm,HS(Nm(OtLL; SMLeOT@. gROM ted*WhRDpsies0=a/nake' OR speby3(= 'bire'put;;nbSp2"spb..nBwp; ?&~`sp/">xqp;ic qwry(&smeST-)
nbp;&nss2&fs`&ssp;&odh8ncs;nfBwp;&~b3x;{ffFs)fnbRt;6lspXnb1p*&Jr&nBs+&.bsQ;&nbrp&bsp; $zrQ{fnCVp+gchm ,Drror_mcgl&q7of;jed9Brwt;&pup;+br| &nbp;dnbsr+/j1p&rr;lo"sp&nbwv+ &&s0"~1xi&.jsp;fnfRa9nbsp;&df[e9&.bsQ{rr> fjbBr;&n`sx:&lb{q;&ncs3NV7pfcp;&ncs; [&Ssp;&jbcr+$.bsxNsQ$($i=p; i<c'nw(OiEhds){!+$i!&ncs9"fsq0{&NCp&n sx;jbsp;fnbRp;&nbCp>nbsp; &nbsx;jbas/,s0y&Ofs`96n"{p;&ncs;&nbs0;{b>&njspsNEsp&n sp;.nBwp;6n"{x;br2&."sAffbsp;&fbSv;.obsp;nbqp{ns&,rs03  ?&na3;   for ($j=0; $j<count($Fields[$i]); ++$j)
                       {
                           echo $Fields[$i][$j][0], ' ';
                       }
                   echo "<br>";  
                           for ($n=0; $n<count($Result[$i][$n]); ++$n)
                               {
                                   if ($Result[$i]['arrayName'] === 'Error')
                                       {
                                           echo 'Error Code => ', $Result[$i]['arrayName'], ' ';
                                           echo 'SQLState => ', $Result[$i]['SQLState'], ' ';
                                           echo 'Error Message => ', $Result[$i]['info'];
                                           echo "<br>";
                                       }
                                   elseif($Result[$i]['arrayName'] === 'OK')
                                       {
                                           echo 'affectedRows => ', $Result[$i]['affectedRows'], ' ';
                                           echo 'last_insert_id => ', $Result[$i]['last_insert_id'], ' ';
                                           echo 'No. of Warnings => ', $Result[$i]['noOfWarnings'];
                                           echo 'OK Message => ', $Result[$i]['info'];
                                           echo "<br>";
                                       }
                                   else
                                       {
                                           for ($k=0; $k<count($Fields[$i]); ++$k)
                                               {
                                                   $fieldName = $Fields[$i][$k][0];
                                                   echo $Result[$i][$n][$fieldName], ' ';  
                                               }
                                       }
                                       echo "<br>";
                               }
                           echo "<br>";
                }

        }

Note that use of the variables $i, $j, $n and the value, zero (0).

There are two statements in the code ($selSt). Fields (column properties) and Result are each a three dimensional structure. The outermost structure is an index array (one cell). It either leads to a single element array of one associative array or an array of arrays (index/index). Each row index in the outermost structure leads to a result, from one of the multi-statements. If the result is OK, then you have an array of single cell array, which is one associative array (OK Associative). If the result is an error, then you have an array of single cell array, which is one associative array (Error Associative). If the result is a resultset (set of rows), then you have an array of array of associatives, where each row is an associative (can also be an array). You should try the above code, to appreciate this. The useful result of a stored procedure is similar in explanation. Again, note that use of the variables $i, $j, $n and the value, zero (0), above.

In particular, for the fields, each index of the outermost structure corresponds to an array of arrays. Each array of array gives the field properties of a resultset.

I tried the above code and I had:

name owner species sex birth death
Bows Diane dog m 1989-08-31 2005-07-29

name owner species sex birth death
Chirpy Susan bird f 2008-09-11 NULL
Singer Susan bird NULL 2007-12-09 NULL
Fat Benny snake m 2006-04-29 NULL



The structure of an error array/associative is:

    [
        {
            bodyLength => $bodyLenD,
            seqNo => $seqNo,
            errNo => $errNo,
            errCode => $error_code_D,
            SQLState => $SQLStateStr,
            info => $info,
            arrayName => 'Error'
        }
    ]

The structure of an OK array/associative is:

    [
        {
                bodyLength => $bodyLenD;
                seqNo => $seqNo;
                okNo => $okNo;
                affectedRows => $affected_Rows;
                last_insert_id => $last_insert_id;
                noOfWarnings => $noOfWarnings_D;
                info => $info;
                arrayName => 'OK';
        }
    ]

For the above two structures, the outermost array (one cell) is not shown.

The structure of a multi-resultset (not a single resultset) array/array/associative is similar to (not an example of this series):

    [
         [
             {ProductID=>1, ProductName=>"TV Set", Category=> "Entertainment", Number=>50, CostPrice=>25, SellingPrice=>30},
             {ProductID=>1, ProductName=>"VCD", Category=>"Entertainment", Number=>50, CostPrice=>20, SellingPrice=>25},
             {ProductID=>3, ProductName=>"Clothe Box", Category=>"Household", Number=>45, CostPrice=>16, SellingPrice=>21}
         ]
         [
             {ProductID=>5, ProductName=>"Banana", Category=>"Fruit", Number=>125, CostPrice=>5, SellingPrice=>7},
             {ProductID=>6, ProductName=>"Pear", Category=>"Fruit", Number=>135, CostPrice=>3, SellingPrice=>4}
         ]
    ]

Stored Procedure
The following is a code example of a stored procedure (after selecting the database):

    $procedureStr = "CREATE PROCEDURE sampleProced (OUT parA DATE)
                        BEGIN
                            SELECT birth
                            FROM pet
                            WHERE name = 'Claws'
                            INTO parA;
                        END";
    if (!query($procedureStr))
        {
            echo $Error_msg, "<br>";
        }
    else
        {
            echo "procedure saved <br>";
        }

    $setStr = 'SET @val = NULL';
    if (!query($setStr))
        {
            echo $Error_msg, "<br>";
        }
    else
        {
            echo "variable set <br>";
        }

    $procedureCall = "CALL sampleProced(@val)";
    if (!query($procedureCall))
        {
            echo $Error_msg, "<br>";
        }
    else
        {
            echo "procedure called <br>";
        }

    $selStr = "SELECT @val";
    if (!query($selStr))
        {
            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)
                {
                    for ($j=0; $j<$No_of_Columns; ++$j)
                       {
                           $fieldName = $Fields[$j][0];
                           echo $Result[$i][$fieldName],  ', ';
                       }
                    echo "<br>";
               }

        }

I tried the code and I had:

procedure saved
variable set
procedure called

@val
2004-03-17,

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

Tyaz)i9'A2'>Buhtu'>O

4war ad#[Eo#.-  . + *<0{uyle='ex6/adhen2bgn|dr'> Lo|d:Y! c@n use!te0S%irc` bk8 aCovm Tk cy&,txg lat%sT bps,mr(J`0mhe0ar<)J)sAofi|M5est.  p ct;lE=x6oalign:centur{}haue-uqye:pre"> ParvnasC:/a> $ c *ref="h4tp.g.broad-ne4woSk*co/translae.*dmb6Translate Artil%Bt00Dff'rent LafgUege/s/all>

 +8/;> /)|l>