Create an array from prepared statement result

[Adamhumbug]

Hi All,

I have a select statement

	$stmt = $conn -> prepare('
	                         SELECT u.user_firstname, u.user_lastname, so.staff_id, r.role_name
	                         FROM ssm_staff_order so
	                         INNER JOIN ssm_user u on so.staff_id = u.user_id
	                         INNER JOIN ssm_role r on u.user_role_id = r.role_id
	                         WHERE job_id = ?
	                         ');
	$stmt -> bind_param('i', $jid);
	$stmt -> execute();
	$stmt -> bind_result($fn, $ln, $id, $role);

The result looks like this

+----------------+---------------+----------+-----------+
| user_firstname | user_lastname | staff_id | role_name |
+----------------+---------------+----------+-----------+
| FName          | LName         |        8 | Chef      |
| jon            | smith         |       15 | Manager   |
| Chelsea        | Hockley       |        2 | Manager   |
+----------------+---------------+----------+-----------+

I am wanting to build an array so that i can foreach the result with the people under the header of their role.

I know this is not difficult but i am going around and around with it and clearly missing the key part.

As always your help is appreciated.

[NotionCommotion]

Maybe I am missing something but...

 

    $stmt = $conn -> prepare('...');
    $stmt -> bind_param('i', $jid);
    $stmt -> execute();
    foreach($stmt as $row) {
		vardump($row);
    }

 

[Barand]

PDO statements (and result objects from a query() ) are traversable, so you can just

foreach ($stmt as $row) {
    echo $row ['user_firstname'];
}

Unfortunately, mysqli result objects only are traversable, not prepared statements.

There is a $stmt->get_result() but it is not available in all implementations (native driver only).

Makes you wonder why people use mysqli.

You may be stuck with using

while ($stmt->fetch() ) {
   // build array
   $data[] = [ $fn, $ln, $id, $role ];
}

 

[Adamhumbug]

15 minutes ago, Barand said:

PDO statements (and result objects from a query() ) are traversable, so you can just

foreach ($stmt as $row) {
    echo $row ['user_firstname'];
}

Unfortunately, mysqli result objects only are traversable, not prepared statements.

There is a $stmt->get_result() but it is not available in all implementations (native driver only).

Makes you wonder why people use mysqli.

You may be stuck with using

while ($stmt->fetch() ) {
   // build array
   $data[] = [ $fn, $ln, $id, $role ];
}

 

When i do the following:
 

while ($stmt -> fetch()) {
        $user =[$role, $fn, $ln, $id];

    }

i only get one result and i know the sql is pulling 3 results.

I tried using .= but that give me an array to string error.

[Barand]

6 minutes ago, Adamhumbug said:

only get one result and i know the sql is pulling 3 results.

yes, you will.

Look at my code again.

[Adamhumbug]

1 minute ago, Barand said:

yes, you will.

Look at my code again.

Thank you!

[Psycho]

You previously stated

Quote

I am wanting to build an array so that i can foreach the result with the people

under the header of their role.

Based on that comment, this might make more sense:

while ($stmt -> fetch())
{
    $users[$role][$id] =[$fn, $ln];
}

You will then have a multidimensional array in a logical format based on the data, like this:

array (
    [Chef] => (
        [8] => ('FName', 'LName')
    ),

    [Manager] => (
        [15] => ('jon', 'smith'),
        [2] => ('Chelsea', 'Hockley'),
    )
)

 

You could then iterate over the results something like this

foreach($users as $role => $roleUsers)
{
    //Can add a header for each role here

    foreach($roleUsers as $userId => $userData)
    {
        //Within this loop have the following variables:
        // - $role
        // - $userId
        // - $userData['user_firstname']
        // - $userData['user_lastname']
    }
}

 

Edited February 20, 2020 by Psycho

[Adamhumbug]

On 2/20/2020 at 4:29 PM, Psycho said:

You previously stated

Based on that comment, this might make more sense:

while ($stmt -> fetch())
{
    $users[$role][$id] =[$fn, $ln];
}

You will then have a multidimensional array in a logical format based on the data, like this:

array (
    [Chef] => (
        [8] => ('FName', 'LName')
    ),

    [Manager] => (
        [15] => ('jon', 'smith'),
        [2] => ('Chelsea', 'Hockley'),
    )
)

 

You could then iterate over the results something like this

foreach($users as $role => $roleUsers)
{
    //Can add a header for each role here

    foreach($roleUsers as $userId => $userData)
    {
        //Within this loop have the following variables:
        // - $role
        // - $userId
        // - $userData['user_firstname']
        // - $userData['user_lastname']
    }
}

 

Thanks for that - will be useful.