Jump to content

Create an array from prepared statement result


Adamhumbug

Recommended Posts

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.

Link to comment
Share on other sites

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 ];
}

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.