Okay, so I realize that when do:
//connection code;
//query code;
//$result= mysqli_query();
$row= mysqli_fetch_array($result);
you create an associative array where the column names from your table are the keys for the data in the respective row.
Then you can use:
while ($row= mysqli_fetch_array($result))
{
//code to echo out table data.
}
My question is how does the while loop go to the next row after each iteration? I thought that was what foreach loops were for?
From http://www.php.net/manual/en/function.mysql-fetch-array.php
array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] ) Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.
This part fetches one row at a time
$row = mysqli_fetch_array($result);
Putting it into a while loop makes it fetch one row at a time, until it does not fetch a row because there are no more to be fetched.
The alternative would be to fetch all the rows, then loop through them with a foreach
$rows = mysql_fetch_all($result);
foreach($rows as $row){
// do something with row
}
For this to work, you have to make yourself a mysql_fetch_all function, which of course has the original while loop in it...
function mysql_fetch_all($result)
{
$all = array();
while($thing = mysql_fetch_assoc($result)) {
$all[] = $thing;
}
return $all;
}
mysql_fetch_array simply fetches the next row of the result set from your mysql query and returns the row as an array or false if there are no more rows to fetch.
The while loops continually pulls the results, one at a time from the result set and continues until mysql_fetch_array is false.
A foreach loop loops through each value of an array. As mysql_fetch_array only pulls one result and therefore the value of count($row) would be 1 every time.
Each time the while loop runs, it executes the function mysql_fetch_array and gets the next result. It does that until there aren't more results to show.
Many functions that return a result set do so by returning an array that you can do a foreach() on like you are used to. This is not always the case however, especially with database functions. mysqli_fetch_array fetches just a single row, or returns boolean false if there are no more remaining. This is how the loop works: the expression evaluates to true as long as there is a row to process.
The reason for this construction is mainly efficiency. Fetching database rows can be a performance critical operation, and there are cases where not all rows are needed. In these situations this approach will give more flexibility. Fetching rows one-by-one is also more memory-friendly since not all result data will have to be loaded into memory at once.
Mysqli actually has a function that does fetch the entire result set in an array: mysqli_fetch_all. You will be able to foreach() over that.
This works due to the SQL connector storing the current state of the query (i.e. the next result row to return) inside the result.
If you want a similar example, it works like reading from a file, where you're able to use similar constructs:
while ($line = fgets($fp, 1000)) {
// ...
}
Behind the scenes (and depending on the language, interpreter, compiler, etc.) for and while essentially result in the same code. The difference is, depending on what your code should do, one approach could be more readable than the other.
Take the following two loops as an example. Both do exactly the same.
for ($i = 0; $i < 10; $i++) {
// ...
}
$i = 0;
while ($i < 10) {
// ...
$i++;
}
PDOStatementsupports theTraversableinterface, so it can be used inforeachloops directly, unlike mysqli results.mysqli_queryreturnmysql_resultobject which is Traversable so usingforeachwill work too.