Broad Network


Using the PurePHP MySQL API Result Set

Using the PurePHP MySQL API – Part 4

Foreword: In this part of the series, I show you the basics on accessing the PurePHP MySQL API result set.

By: Chrysanthus Date Published: 17 Jan 2018

Introduction

This is part 4 of my series, Using the PurePHP MySQL API. In this part of the series, I show you the basics on accessing the PurePHP MySQL API result set. A result set is a special kind of database table. PurePHP stands for Pure PHP. It is PHP Software that does not wrap over C. You should have read the previous parts of the series before reaching here, as this is a continuation.

Remember, you begin your script that will use the API with:

    require_once("./Mysql.php");

or similar statement.

The $Result Array Variable
The variable, $Result holds an array. It has the result set. To access the array rows from your script the syntax is,
    
    $Result[$row_no]

Row number ($row_no) counting begins from zero (not 1).

This array is actually an index array of associative arrays. To refer to the array alone, you would type:

    $Result

To refer to each row (of associative array) the syntax is:

    $Result[$row_no]

which returns a reference to the associative array that forms the row.

To access a cell value of the result set, the syntax is:

    $Result[$row_no]['column_name']

where column-name is the name of the column of the result set.

Reading Row Contents
You need a for-loop to read the contents of a number of rows of the result set. Before that you should know the maximum number of rows that have been sent by the server. This is stored in the scalar variable, $No_of_Rows in the Mysql module. It is accessible similarly to the above as,

    $No_of_Rows

In your script, you can print out the number of rows to the browser with:

    echo $No_of_Rows;

If you want to read all contents of all the rows, of the table we are dealing with in this series, and print to the web page (browser), your for-loop would be like:

            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'],  ', ';
               &nbyð#mb÷Q;oæç`9eãxo $Vecwlt[$É]Ûq`uóvleM.$njrp; 'l 'N-pe0Jk÷ vh'!cëluenle@dm~g{ heöu"b-dfUöe l zï]!­q]&ére(o•he PHP technipuás to MoìqF_d|iD`foS-loop. ƒ \íátbt`d cd,æmp(uid¤×Q\ 3|ateme®u,ÇfquNtSÅ\ECT " fVOM pet GHZE`spDcies < £snaie !ß@ rpácis Œ`' Hrd'&a5/|, ent"H ìed*&+bçS; nâcp;.nBWð»Y.Qg2q, Sõsa,, bird, f, 0x1-09-11lNô\L,
¦nb1p;&nb3p+'&êCt;&jbãr;in7e2 ×5óaOn BiòT, NE $`:00'

Note: you can choose the cell to print by choosing the column name.

You can print out the column headings before printing the rows that follow. To do this you will precede the for-loop structure with a new for-loop, like:

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

The Mysql module has a variable, $No_of_Columns, which is the number of columns in the result set. There is also a two-dimensional array in the Mysql module, called $Fields. The number of rows in the 2D array is the number of columns, one row for each column. The order of the rows from top to bottom is the order of the columns in the result set from left to right. The first cell of each row in the 2D array has the name of the column. The last echo statement in the above code senew¥dîd çuòsïp,bU{ $be Jeàfhi~mnfR>¼rr>Yo} wež:ðbint!a¤parthañddr0aolumn named5cJOg i´s¨O1 po1ition in the msBãy (row poci4cofhng begins from z%ro.`Av09'} wqn4(vo(ñòi,v tIu <@Iöt cmn}do na/g nn2 eYampxe|*}oå"w-uld tYpå<bò.
 '~ös83gnêBP_&þS³x?&rîã$Rkelds[2][2]$!%p-z=cò.
T(e Wamué Oàb|;e`{econd {yUEvå ba‡wtv÷,(K}dhwle ¥nwHxS ât%þñp $gz)cOhumn!nåmeó. oðe phu"_2M"fov djetèyre çonTek0naM쯈ísecvigo><~cR,hd='SS3>¬wáæ>=tì6¬id='links3'6<`év>~öarbid=%LDrg>vcr6?dÁw¢+L|¯ä!â.Da'~4/dyv~4waö id=çTTP'>Š<8n‰¨THráeByTwo -->  wbvípv~ hadRBggl%le = willgW:å%{æygongèe0|<({©>Q}sHŒ{ýz;‰ ¸mdiw>¸stronc>q!£uRRiþg`+$[yLcmu$i>we¼?svzong.<"z>Vo(`ccess a R`kãì¥:6i}E%ìnor a0particular row and column, the syntax is:

    $Result[$row_no]['column_name']

In the previous part of the series, the single value of row 2 and the column with the heading, 'owner' was read with:

    $Result[1]['owner']

Note: row indexing in the result set begins from zero.

Example of Selecting and Displaying
A select and display code can be something like:

        $sel = "SELECT * FROM pet";
        if (query($sel) !== true)
            {
                echo $Error_msg, "<br>";
            }
        else
            {
                for ($i=0; $i<2; ++$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>";        
                    }
            }

The code prints the first 2 rows.

The query_num() Function
This function is the same as the query() function, but instead of returning an array of hashes (associatives), it returns an array of arrays (index-index). The array of arrays is $Result_Num. You should use this function when you already know the columns that are returned and their positions in the result set. The result set (array of arrays) for this function occupies less memory space than the result set (array of hashes) for the query() function. The following code illustrates the use of the function and the returned result set.

         $sel = "SELECT * FROM pet";
         if (!query_num($sel))
          {
                   echo $Error_msg, "<br>";
          }
          else
          {
                   for ($i=0; $i<$No_of_Rows; ++$i)
                            {
                                     for ($j=0; $j<$No_of_Columns; ++$j)
                                              {
                                                       echo $Result_Num[$i][$j], ', '; ;
                                              }
                                     echo "<br>";
                            }
          }


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