foreach can replace fetch_array?

Question

I saw in a video that the guy takes the query and plays straight in foreach and manages to list without any problem, my doubt is the following: if really possible, using only foreach would be much more practical than using while with fetch_array ?

Answer 1

You can do it all at once with foreach, the question is whether it’s really worth it.

To use the foreach you would need to get the data, either with mysqli_fetch_all or accumulating somewhere with multiple readings, which is bad in some situations because either way will need to take all the data and store in memory if you want to use the foreach.

For a request or a site barely accessed, this is irrelevant. Incidentally, with little data, the mysqli_fetch_all can even be more "economic". To darlings of large listings on a "busy" website, the picture already changes.

According to the PHP manual:

As mysqli_fetch_all() Returns all the Rows as an array in a single step, it may consume more memory than some similar functions such as mysqli_fetch_array(), which only Returns one Row at a time from the result set. Further, if you need to iterate over the result set, you will need a looping Construct that will further Impact performance. For These reasons mysqli_fetch_all() should only be used in those situations Where the fetched result set will be sent to Another layer for Processing.

Which, more or less, is:

As mysqli_fetch_all() returns all lines as array in one step, you can consume more memory than similar functions like mysqli_fetch_array(), which returns only one row at a time from the result set. If you need to iterate on the results, you will need a loop that impacts performance. For these reasons, mysqli_fetch_all() should only be used where the result needs to be sent for processing in another layer.

See example of use of mysqli_fetch_all in the reply by @Inkeliz

In other words, the fact of using foreach does not dispense with a number of fetch_array, fetch_assoc or function that does similar thing, as mentioned.

A generally simpler way is to use while with mysqli_fetch_rowor mysqli_fetch_assoc, because you will usually use the records one by one and already release next.

See a simple example of using while with mysqli_fetch_assoc:

<?php
    $link = mysqli_connect( ... );

    $result = mysqli_query($link, 'SELECT name, country FROM city ORDER BY name') or die ("Erro na query: %s\n", mysqli_error($link));

    while ($row = mysqli_fetch_assoc($result)) {
       printf ("%s (%s)\n", $row['name'], $row['country']);
    }

    mysqli_free_result($result);
    mysqli_close($link);
?>

There are similar examples in the PHP site itself:

http://php.net/manual/en/mysqli-result.fetch-row.php

Answer 2

You can use the mysqli_fetch_all(), thereby making a foreach of the data already obtained.

Thus using:

$sql = mysqli_connect(...);

$Select = mysqli_query($sql, 'SELECT id, nome, idade FROM tabela WHERE 1 = 1');

$resultado = mysqli_fetch_all($Select, MYSQLI_NUM);

if(!!$resultado){
    foreach($resultado as list($id, $nome, $idade)){

      echo 'id>'.$id;
      echo 'nome>'.$nome;
      echo 'idade>'.$idade;
      echo '<br>';

    }
}

In that case I used the list() so that each item of the SELECT has a unique variable, this allows the code to be cleaner and without having to use, for example: $item['id'], $item['nome'], $item['idade'].

Meanwhile you can do it quietly:

//...

$resultado = mysqli_fetch_all($Select, MYSQLI_ASSOC);

if(!!$resultado){
    foreach($resultado as $item){

      echo 'id>'.$item['id'];
      echo 'nome>'.$item['nome'];
      echo 'idade>'.$item['idade'];
      echo '<br>';

    }
}

Then you’ll have to use the MYSQLI_ASSOC instead of MYSQLI_NUM. The difference between them is that the first will return an array whose indices have the column name, while the second is only number. For example, the first can be obtained using $item['id'], in the second should use $item['0'].

You can read more in the documentation on http://php.net/manual/en/mysqli-result.fetch-all.php.

Difference between the mysqli_fetch_all() and mysqli_fetch_array():

In relation to performance, it is extremely relative.

The PHP manual states that there may be greater use of memory, as mentioned by @Bacchus in his reply. But in the PHP documentation itself a user reported that this does not occur, Personally I never noticed differences between the two, nowadays I use the mysqli_fetch_all, but not for performance reasons.

Note: THE mysqli_fetch_all() uses the mysqlnd, you need to use it. This is by default installed in PHP 5.4 and higher unless mistaken. If you don’t have mysqlnd uninstall mysql (yum remove php-mysql) and install the mysqlnd (yum install php-mysqlnd). :D

Looking at the source code of the mysqlnd we were able to find this on the line 1806, see this by clicking here.

/* {{{ mysqlnd_res::fetch_all */
static void
MYSQLND_METHOD(mysqlnd_res, fetch_all)(MYSQLND_RES * result, const unsigned int flags, zval *return_value ZEND_FILE_LINE_DC)
{
    zval  row;
    zend_ulong i = 0;
    MYSQLND_RES_BUFFERED *set = result->stored_data;

    DBG_ENTER("mysqlnd_res::fetch_all");

    if ((!result->unbuf && !set)) {
        php_error_docref(NULL, E_WARNING, "fetch_all can be used only with buffered sets");
        if (result->conn) {
            SET_CLIENT_ERROR(result->conn->error_info, CR_NOT_IMPLEMENTED, UNKNOWN_SQLSTATE, "fetch_all can be used only with buffered sets");
        }
        RETVAL_NULL();
        DBG_VOID_RETURN;
    }

    /* 4 is a magic value. The cast is safe, if larger then the array will be later extended - no big deal :) */
    array_init_size(return_value, set? (unsigned int) set->row_count : 4);

    do {
        mysqlnd_fetch_into(result, flags, &row, MYSQLND_MYSQLI);
        if (Z_TYPE(row) != IS_ARRAY) {
            zval_ptr_dtor(&row);
            break;
        }
        add_index_zval(return_value, i++, &row);
    } while (1);

    DBG_VOID_RETURN;
}
/* }}} */

Apparently it runs a loop, so it delivers all the data. So, use the foreach you’d be doing two loops! A first was made by the library of mysqlnd when performing the mysqli_fetch_all() and a second that is the foreach, that your own code is running. This supposedly could cause performance loss, in addition to obviously increased memory usage. S