Chapter 12: Prepared Statements, The While Loop

[markifornia]

Prepared statements makes total sense, except the example in the book does not include a while loop. See my comparison below, I assume they would be very similar.

 

In regular queries we use:

 

$q = "SELECT user_id FROM users WHERE user_id = '$id'";

 

$r = @mysqli_query($dbc, $q);

 

while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

// print out array

}

 

 

But in prepared statements would it be like so?

 

$q = "SELECT user_id FROM users WHERE user_id = ?";

 

// Prepare the statement

 

$stmt = mysqli_prepare($dbc, $q);

 

mysqli_stmt_execute($stmt);

 

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

// print out array

}

 

** I'm assuming the while loop goes after the mysqli execution.

 

Is this correct?

 

Thanks,

Mark

[Larry]

Just to clarify a couple of things, first, prepared statements and while loops are unrelated. Whether or not you use a while loop depends upon whether the query returns more than one record or not. In both of your examples, the query would only return a single required (presumably), so the while loop is unnecessary.

[Edward]

Just to clarify a couple of things, first, prepared statements and while loops are unrelated. Whether or not you use a while loop depends upon whether the query returns more than one record or not. In both of your examples, the query would only return a single required (presumably), so the while loop is unnecessary.

 

That while loop syntax also confuses me, even though i know a query is unrelated. So why does the while loop go through all the rows in an array when the $row condition is equal.

 

I checked the return value on php.net for mysqli_fetch_array and it says Returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in resultset.

[markifornia]

That while loop syntax also confuses me, even though i know a query is unrelated. So why does the while loop go through all the rows in an array when the $row condition is equal.

 

I checked the return value on php.net for mysqli_fetch_array and it says Returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in resultset.

 

Edward, my example comparison above wasn't the greatest of examples as Larry even pointed out. I had added a WHERE clause which would make the while loop unnecessary I presume. I too am getting the hang of it.

 

But to take a stab at your question, $row isn't equal to something, $row is a variable that is assigned the results of mysqli_fetch_array that loops through each row even NULL values.

 

Anyone? Correct me if I'm wrong.

 

-Mark

[HartleySan]

Yes, $row is the variable that is assigned the current row retrieved from the DB via the mysqli_fetch_array function.

As for why a while loop is used to do this and why the mysqli_fetch_array function is able to grab one row at a time, well, that's simply just the way it was designed to work in PHP, and that's how everyone uses it.

[Antonio Conte]

If you are interested in the mechanics behind the scene, look at iterators. They are objects that keeps track of pointers in an array. It's fairly common to see this code pattern (Some alternations bases on language)

 

// You would've done it this way in Java:

// Get the iterator object
Iterator it = obj.iterator();

// Check if we have a valid next before calling it.
// Will return true if next is available, else false
while ( it.hasNext() )
{
   String element = it.next(); // returns array element
}

// PHP don't have index bounds in arrays. array[10] will throw exception if empty in Java.
// Not in php. Therefor, no need for hasNext() in php. Only make sure we return 'null' when
// next(); is called (next() is implied - just how it works logically if you want to compare)

while ( $row = $mysqli->fetch_array(MYSQLI_ASSOC)
{
$row['']
}

 

The reason behind this is to protect the array from being tempered with directly. You create one controlled passage for the users.

 

 

It's all about creating easy to use functionality. My tip is not to care why it works, just how to use it. Trust that the black box works.

[Edward]

If you are interested in the mechanics behind the scene, look at iterators. They are objects that keeps track of pointers in an array. It's fairly common to see this code pattern (Some alternations bases on language)

 

// You would've done it this way in Java:

// Get the iterator object
Iterator it = obj.iterator();

// Check if we have a valid next before calling it.
// Will return true if next is available, else false
while ( it.hasNext() )
{
String element = it.next(); // returns array element
}

// PHP don't have index bounds in arrays. array[10] will throw exception if empty in Java.
// Not in php. Therefor, no need for hasNext() in php. Only make sure we return 'null' when
// next(); is called (next() is implied - just how it works logically if you want to compare)

while ( $row = $mysqli->fetch_array(MYSQLI_ASSOC)
{
$row['']
}

 

The reason behind this is to protect the array from being tempered with directly. You create one controlled passage for the users.

 

 

It's all about creating easy to use functionality. My tip is not to care why it works, just how to use it. Trust that the black box works.

 

Well i can somewhat see your point, in some occasions like if we use jquery we should be able to trust the functions work correctly, but in other situations like in the Yii framework, things may not work correctly. I think its better we keep our eye's open at all times, just in the case that something isn't working the way we expect and we fall into the trap, i don't have as much faith as you AC.