Adamhumbug Posted February 20, 2020 Share Posted February 20, 2020 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. Quote Link to comment Share on other sites More sharing options...
NotionCommotion Posted February 20, 2020 Share Posted February 20, 2020 Maybe I am missing something but... $stmt = $conn -> prepare('...'); $stmt -> bind_param('i', $jid); $stmt -> execute(); foreach($stmt as $row) { vardump($row); } Quote Link to comment Share on other sites More sharing options...
Barand Posted February 20, 2020 Share Posted February 20, 2020 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 ]; } Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted February 20, 2020 Author Share Posted February 20, 2020 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted February 20, 2020 Share Posted February 20, 2020 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. Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted February 20, 2020 Author Share Posted February 20, 2020 1 minute ago, Barand said: yes, you will. Look at my code again. Thank you! Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 20, 2020 Share Posted February 20, 2020 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Adamhumbug Posted February 24, 2020 Author Share Posted February 24, 2020 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.