The Bug is with the User and Not the Program

This just in: The program does not have a bug. The problem is the user. Here is the bug report. The response is simply that I need to learn to program.

The exact bug I reported was that, on my system, the PDOStatement::fetch(PDO::FETCH_NUM) command returns an array when called on its own, but it returns an incomplete string when called in a foreach loop. PHP's documentation states the function always returns an array and that foreach loop is the preferred method for looping through output.

The program does not do what their examples and their documentation claims.

This is the classic definition of a bug.

Now that the bug report has been rejected, I feel free to solve the problem my way.

I've worked with dozens of SQL drivers over the years. Long ago I realized that a SQL driver needs to do things. One needs to be able to take data out and put data back in.

Now, the very first thing that I do when I encounter a new database driver is to create my own wrapper for the driver. I usually call the program that sends SQL commands to the database execSQL(). I usually create two programs to pull data out of the database which I call sqlValue(), and sqlRow().

As the name imply, sqlValue() pulls a single value from the database with a command like:

$user_nm = sqlValue('SELECT user_name FROM User_Table WHERE user_id = :user_id);

I never liked the mysql_ or the mysqli_ family of programs because they are vendor specific. So, I simply wrapped the vendor specific programs with my programs.

Now, I am not as pigheaded as you might imagine. I strongly believe in following industry standards. I was ready to abandon my tried and true approach for the new Object-Oriented way.

But I have yet to see anything which is as robust as my little database functions.

So, what I decided to do over Christmas was to give up on the "object oriented" way and revive the tried and true style of accessing data with simple functions.

I did make one huge leap forward in the way that I loop through database tables, which I will get to after discussing loops.

So, I would like you to take a moment and consider the while loop.

The loop has the form:

While condition is true perform an action.

The condition is a statement that evaluates to true or false. If you have a condition that is always false, you will never see the inside of the loop. If a condition is always true, the loop will run until some one or something kills the job.

Now, lets consider the following PHP Code which loops through an array:

$arr = array('a','b','c','d');
$cnt = 0;
while ($val = $arr[$cnt++]) {
  echo 'Line '.$cnt.' is '.$val.'';
}

In the conditional part of the statement. The program attempts to pull an item from the array. If the assignment succeeds, the system reports true and the loop continues. When the assignment fails, the system throws an error and the computer pops out of the loop.

That is a very ugly way to handle a loop.

The foreach loop implements an idea called "traversable." It is functionally the same as the while loop, but captures the error more elegantly.

$arr = array('a','b','c','d');
foreach ($arr as $val) {
  echo 'The value of this object is '.$val.'
';
  if ($cnt++>100) break;
}

In a superficial way, the foreach loop appears more elegant. As my experience of this last moth shows, the condition for breaking out of the loop is dependent a weird thing called "traversable".

The problem in both the first and second loop is that we are trying to use the code that assigns the value as the condition for the loop.

The form of a while loop should be: "while condition perform action." This next example shows a better form for the while loop:

$arr = array('a','b','c','d');
$cnt = 0;
while (isset($arr[$cnt])) {
  $val = $arr[$cnt];
  echo 'Line '.$cnt.' is '.$val.'';
  $cnt++;
}

The function isset() cleanly evaluates to true or false. It returns true for offsets 0 thru 4 and fails on 5; popping us out of the loop.

Looping with sqlRow()

I decided I wanted to improve the looping with sqlRow(); So, I updated the program to work as follows.
If you call the function as sqlRow($sql,$arr), with $sql containing a SQL statement and $arr being an array of parameters. The program will fetch a row with that statement. If you call sqlRow() with no parameters; it returns a row from the last result set. If you call it with the option DB_CHK; the program returns a true/false statement.

A loop works as follows:

$sql = 'SELECT a bunch of data FROM Table WHERE id = ?';
$arr = [$id];  // arr contains a bound array with the variables for the SQL
$row = sqlRow($sql, $arr);  // get the first row.
while (sqlRow(DB_CHK)) {
  // process data in $row
  $row = sqlRow();  // get the next row.
}

The big trick in the code above is that I pass the $row parameter by reference.

Creating the first row outside the loop seems awkward at first. However, it is useful when producing tables, lists and other HTML elements. You want to know if you have any data before creating the table as in the following example:

$sql = 'SELECT list_data FROM Table WHERE id = ?';
$arr = [$id];  // arr contains a bound array with the variables for the SQL
$row = sqlRow($sql, $arr);  // get the first row.
if (sqlRow(DB_CHK)) {
  echo '<ul class="ulclass" >';  // open Ordered List
  $closeUL = '</ul>';
} else {
  $closeUL = '';
}
while (sqlRow(DB_CHK)) {
  // process data in $row
  $row = sqlRow();  // get the next row.
}
echo closeUL;

You don't want to print the UL tags if there is no data.
I put the pre-alpha version of the code in my code viewer. The code has passed my initial tests on this and a few other servers. I will start writing code with sqlLoop() tomorrow.