Can't understand why this PHP code works.

Factual Questions
1

I’m trying to learn/work out how to dump a recordset to an array before doing all the display stuff (looping through and constructing a table of values)

The PHP code below is some I found on the internet (with modifications to suit my environ)

What it seems to do is put the column headings in a single-dimensional array once…

for ($i=0; $i < $num_columns; $i++) {
$fld[$i] = $rs->Fields($i);
}

Then , looping through the record set, it seems to display the contents of that array, over and over again. I can’t see how the contents of the array could be different on each pass through. Also they would contain column headings, not record values surely!, and finally this method still requires the recordset - when one of the points of putting it into an array is so that you don’t need it any more.
Can anyone make sense of this code? And better - can anyone show me as simplistically as possible how to do put a sql query resultset into an array, then loop through the array displaying the values.



$sqlquery="SELECT * from table where id > 10000";

$rs = $conn->execute($sqlquery);

$num_columns = $rs->Fields->Count();
echo $num_columns . "<br>";  

for ($i=0; $i < $num_columns; $i++) {
    $fld[$i] = $rs->Fields($i);
}

echo "<table border = 1>"; 

while (!$rs->EOF)  //carry on looping through while there are records
{
    echo "<tr>";
    for ($i=0; $i < $num_columns; $i++) {
        echo "<td>" . $fld[$i]->value . "</td>";
    }
    echo "</tr>";
    $rs->MoveNext(); //move on to the next record
}
echo "</table>";
2

That’s not dumping anything into an array at all. It’s doing exactly what you had been doing previously with ASP - opening an RS then looping through it and then closing the RS.

Look at the ASP code I gave you. This is what it does…

  1. Using objRS.GetRows() we get all of the rows and dump them into an array variable. It’s a multidimensional array with both “rows” and “columns”
  2. We get the number of “rows” in the array by saying UBOUND(arrayName, 2) (UBOUND(arrayName, 1) would get the number of “columns”)
  3. We tell the loop to run as many times as there are “rows” by saying “For iRow = 0 to UBOUND(arrayName, 2)”
  4. We then fill a variable for each “column” in each “row” by calling to is specifically: iRecordID = arrayName(0, iRow) where 0 is the first “column” and iRow is the “row” number. If “First Name” was the next column in your recordset, you’d fill the variable by saying strName = arrayName(1, iRow) etc etc.
  5. Then once you’ve filled the variables for that “row”, you write out the variable values however you want.
  6. We finish off with the command Next to tell the loop to go to “row” 2 (which is actually numbered 1, as 0 is the first “row”)

Dig?

3

I understand what the code you gave me is doing. I just don’t understand what’s happening in the code I supplied above. It seems to loop through the rs displaying item 1 in the array, item 2 in the array, item 3 in the array.

It looks like it would display the same values over and over again (because nothing within the while loop updates the contents of the array) but what it actually does is display the records properly. I am baffeled as to how this works.
On a seperate note: is there a php equivalent to ‘ubound()’ because if there is I’d know how to use the code you gave me and convert it to PHP.

I guess I could have asked for the equivalent to ubound in the op (I already figured out what it does) but I figured I’d go from a ‘fresh’ starting point and ask how it would typically be done in php.

But it would be just as helpful to me if I you can supply me with an equivalent function to ubound/ or an equivalent way of returning the number of rows in the array.

4

I began with something like this…



$sqlquery="SELECT * from table";
$rs = $conn->execute($sqlquery);
$num_columns = $rs->Fields->Count();
$array = $rs->getrows();
$rs->Close();
$conn->Close();
$rs = null;
$conn = null;
echo "<table border = 1>
"; 
for($i=0; $i < ubound($array,2); $i++)
{
	echo "<tr>";
	for($j=0; $j< $num_rows; $j++) echo "<td>" . $array[$i][$j] . "</td>
";
	echo "</tr>";
}	
echo "</table>";

But it told me ubound is an undefined funciton.

5

Start with count(). But that is just for single-dimensional arrays. In my example with UBOUND I’m asking for the rows (2) not columns (1).

Here’s a page on count() that says it counts multi-dimensional arrays if you specify 1.

6

What’s to be baffled about? You’re calling $RS->MoveNext() – in ASP-speak it would be RS.MoveNext() – in your while loop. It’s doing exactly what ASP code would do. The difference is that PHP uses C++'s way of referring to object properties and methods (the “->” operator) instead of using dot syntax like everything else in the world.

7

I understand that. But look within the loop. It doesn’t do anything with rs each time it loops to the next one. It just does something with the array.

8

rs->getrows() seems to be getting the columns…

$sqlquery="SELECT forename,surname,accountnumber,email from clients where clientid > 10000";
$rs = $conn->execute($sqlquery);

$array = $rs->getrows();

$size = count($array,1);

$size becomes ‘4’.

If I add or remove fields from the select list the size changes accordingly.

9

Right. Lets start from scratch…
I need to know the number of records in $array when …

$array = $rs->getrows();

has been run.

count() doesn’t seem to work.

And I can’t find anything on google.

10

I give up. getrows doesn’t seem to work at all in php.

count doesn’t count a multidimensional array properly.

Thanks anyway guys. Sorry this GQ turned out to be a bit of an awkward haphazard one.


$sqlquery="SELECT 'hello'";

$rs = $conn->execute($sqlquery);

$arr = $rs->getrows();
//this fails badly
echo $arr[0][0];

//this works
echo $rs->fields(0)->value;
11

The code in the OP example seems to work because $fld is magically bound to the result set and calls to $rs->MoveNext() seems to affect $fld behind the scenes. I’m not sure what sort of database you are using or what the OO interface you seem to be using is, so I can’t say more than that. Looking through the PHP reference material I don’t see such thing as a “Field()” command in the function index. Since you’re using stuff that I don’t know where to go to look up the documentation of, proposing a solution to your problem is rather difficult.

As to your question of how to grab the data and store it in your own multidimensional array, well you certainly know where the items are being output:

echo “<td>” . $fld[$i]->value . “</td>”;

So simply rewrite that one line to insert into your multidimensional array and take the rest of the code as it is.

I know that when dealing with MySQL and PostgreSQL there are functions that will automatically put an entire result into an array, but like I said I don’t know what extension or OO abstraction you are using so I can’t look up what the equivalent would be if there is such a thing.

12

Thanks Sage Rat.

I could use a loop to move through the resultset adding its records to a multidimensional array, but would that defeat the object of putting it into an array in the first place?

As ZipperJ points out it is fairly simple in ASP. with one line “set arr = rs.getrows()” which dumps the entire lot into ‘arr’.

There is no such equivalent in php (or I couldn’t find one) there’s rs->getrows() which fails completely (the values are not readable) and there’s fetchrow and fetcharray which just get one record at a time. I could use them in a loop to build the multidimensinal array, but the question remains - is there any point when I can just use the same loop to actually display the values.

Another problem I got was - Zipper provided code which worked perfectly ubound(arr,2). In hindsight the php equivalent ‘count()’ probably does work, but because my rs->getrows() wasn’t working then neither was the count().

but having said all that. You’ve answered the main question “how is this code working” and I am grateful for that, because I was wondering if I was being dumb for not being able to see how it was working. I thought it might be some weird binding thing but I thought only C/C++ was prone to that sort of thing with its pointers.

p.s. I’m using ADO (Activx Data Objects I think it stands for) functions to access an MSSQL database. All the ADO functions are overloaded to work in php in almost exactly the same way they work in asp (but obviously they don’t all work as expected, unless I’m doing something wrong)

13

Disclaimer: I don’t know/use PHP specifically, but have broad programming knowledge.

Do you have a description of the Fields() function? My impression is that it doesn’t return the value of a field, it returns the Field heading (as you say above). If that’s the case, your array contains structural information about the recordset, analogous to an array index.

Thus, it seems to me that the while (!$rs->EOF) loop points at a row in the table, while the $rs->MoveNext(); increments the row pointer. The $fld[$i] refers to a Field heading of that row (i.e., a column), while $fld[$i]->value is the value at that row, column location.

Is that clear? And am I right?

14

The PHP datatype can be anything. In this case, it is a pointer. When it is an object, similarly, it is a pointer. You know this because when you pass an object to a function, changes to it will reflect outside the function.

PHP is a wrapper around C/C++ and the vartype way of handling things is why coding serious projects in scripted languages is unhealthy.