How to access mysql result set data with a foreach loop

user_468017 0
Viewed 339570

I'm developing a php app that uses a database class to query MySQL.

The class is here: http://net.tutsplus.com/tutorials/php/real-world-oop-with-php-and-mysql/
*note there are multiple bad practices demonstrated in the tutorial -- it should not be used as a modern guide!

I made some tweaks on the class to fit my needs, but there is a problem (maybe a stupid one).

When using select() it returns a multidimensional array that has rows with 3 associative columns (id, firstname, lastname):

Array
(
    [0] => Array
        (
            [id] => 1
            [firstname] => Firstname one
            [lastname] => Lastname one
        )

    [1] => Array
        (
            [id] => 2
            [firstname] => Firstname two
            [lastname] => Lastname two
        )

    [2] => Array
        (
            [id] => 3
            [firstname] => Firstname three
            [lastname] => Lastname three
        )
)

Now I want this array to be used as a mysql result (mysql_fetch_assoc()).

I know that it may be used with foreach(), but this is with simple/flat arrays. I think that I have to redeclare a new foreach() within each foreach(), but I think this could slow down or cause some higher server load.

So how to apply foreach() with this multidimensional array the simplest way?

resultset foreach arrays php
13 Answers

If you need to do string manipulation on array elements, e.g, then using callback function array_walk_recursive (or even array_walk) works well. Both come in handy when dynamically writing SQL statements.

In this usage, I have this array with each element needing an appended comma and newline.

$some_array = [];

data in $some_array
0: "Some string in an array"
1: "Another string in an array"

Per php.net

If callback needs to be working with the actual values of the array, specify the first parameter of callback as a reference. Then, any changes made to those elements will be made in the original array itself.

array_walk_recursive($some_array, function (&$value, $key) {
    $value .= ",\n";
});

Result:
"Some string in an array,\n"
"Another string in an array,\n"

Here's the same concept using array_walk to prepend the database table name to the field.

$fields = [];

data in $fields:
0: "FirstName"
1: "LastName" 

$tbl = "Employees"

array_walk($fields, 'prefixOnArray', $tbl.".");

function prefixOnArray(&$value, $key, $prefix) { 
    $value = $prefix.$value; 
}

Result:
"Employees.FirstName"
"Employees.LastName"

I would be curious to know if performance is at issue over foreach, but for an array with a handful of elements, IMHO, it's hardly worth considering.

user_22885470

A mysql result set object is immediately iterable within a foreach(). This means that it is not necessary to call any kind of fetch*() function/method to access the row data in php. You can simply pass the result set object as the input value of the foreach().

Also, from PHP7.1, "array destructuring" allows you to create individual values (if desirable) within the foreach() declaration.

Non-exhaustive list of examples that all produce the same output: (PHPize Sandbox)

foreach ($mysqli->query("SELECT id, firstname, lastname FROM your_table") as $row) {
    vprintf('%d: %s %s', $row);
}

foreach ($mysqli->query("SELECT * FROM your_table") as ['id' => $id, 'firstname' => $firstName, 'lastname' => $lastName]) {
    printf('%d: %s %s', $id, $firstName, $lastName);
}

*note that you do not need to list all columns while destructuring -- only what you intend to access

$result = $mysqli->query("SELECT * FROM your_table");
foreach ($result as $row) {
    echo "$row['id']: $row['firstname'] $row['lastname']";
}
user_29434030
Related