Adding array Keys when building array from mysqli fetch_assoc

April 8, 2022

I have the following code

function testgetUserAccess($conn, $page_id) {
    $sql = "select * 
    from user_access 
    left join users on user_access.user_id = users.id
    where page_id = $page_id";

    $access = $conn->query($sql);
    
    while($row = $access->fetch_assoc()) {
        $test_access_id[] = $row['user_id'];
        $test_access_id[] = $row['email'];
        $test_access_id[] = $row['username'];
    }
    return $test_access_id;
}

This will return an array containing users that have access to that specific page, and this works well!

But the array looks like this

array(6) {
  [0]=>
  string(3) "119"
  [1]=>
  string(23) "stephanie.styles@xxx"
  [2]=>
  string(16) "Stephanie.Styles"
  [3]=>
  string(3) "120"
  [4]=>
  string(18) "karl.styles@xxx"
  [5]=>
  string(11) "karl.styles"
}

What I am trying to do is add keys so my array will look like this

array(6) {
  ["User_ID"]=>
  string(3) "119"
  ["User_Email"]=>
  string(23) "stephanie.styles@xxx"
  ["UserName"]=>
  string(16) "Stephanie.Styles"
  ["User_ID"]=>
  string(3) "120"
  ["User_Email"]=>
  string(18) "karl.styles@xxx"
  ["UserName"]=>
  string(11) "karl.styles"
}

I have tried the following but this only returned the last user details in the array

while($row = $access->fetch_assoc()) {
        $test_access_id = array(
            "User_ID"    => $row['user_id'],
            "User_Email" => $row['email'],
            "UserName"   => $row['username']
        );
    }
    return $test_access_id;

Any help would be appreicated.

>Solution :

you cant repeat the same index in the array but you can create multidimensional-array
should be like this

function testgetUserAccess($conn, $page_id) {
    $sql = "select * 
    from user_access 
    left join users on user_access.user_id = users.id
    where page_id = $page_id";

    $access = $conn->query($sql);
    
    while($row = $access->fetch_assoc()) {
          $test_access_id[] = [
                'user_id' => $row['user_id'],
                'email' =>  $row['email'],
                'username' => $row['username']
          ];
    }
    return $test_access_id;
}

or you can replace this while

function testgetUserAccess($conn, $page_id) {
    $sql = "select * 
    from user_access 
    left join users on user_access.user_id = users.id
    where page_id = $page_id";

    $access = $conn->query($sql);

    return $access->fetch_all(MYSQLI_ASSOC);
}

I hope it’s useful