Get array from database, using one of the columns as array key

Question

I have a function like this to get users from a database:

function getUsers(){
    $db = db::getInstance();
    $res = $db->prepare("
        SELECT *
        FROM `users`
        ORDER BY `points` DESC
    ");
    $res->execute();
    if($res->rowCount()==0){
        return null;
    }
    return $res->fetchAll();
}

This will return an array like:

Array(
    0 => {id => 555, name => Smith, firstname => William, points => 123}
    1 => {id => 213, name => Hitchcock, firstname => Alfred, points => 95}
    2 => {id => 999, name => James, firstname => Kevin, points => 66}
)

Is there a simple way to use one of the fields as key? In this example I might want the field id to be used as array keys, so I would want this result:

Array(
    555 => {id => 555, name => Smith, firstname => William, points => 123}
    213 => {id => 213, name => Hitchcock, firstname => Alfred, points => 95}
    999 => {id => 999, name => James, firstname => Kevin, points => 66}
)

I could achieve this by manually creating a new result array like:

    $result = [];
    foreach($res->fetchAll() as $row){
        $result[$row['id']] = $row;
    }
    return $result;

Does PHP or PDO provide a built-in solution for this though? I'm trying to keep the complexity (as in speed/memory usage) as low as possible.

Paul Spiegel · Accepted Answer · 2017-04-23 16:00:24Z

1

You can use the fetch mode PDO::FETCH_UNIQUE:

return $res->fetchAll(PDO::FETCH_UNIQUE);

Note that this will use the first column from the SELECT as array index and remove that column from the row. If you want to keep that column (id) you will need to select it twice:

function getUsers(){
    $db = db::getInstance();
    $res = $db->query("
        SELECT u.id as unique_key, u.*
        FROM `users` u
        ORDER BY `points` DESC
    ");
    if($res->rowCount()==0){
        return null;
    }
    return $res->fetchAll(PDO::FETCH_UNIQUE);
}

answered Apr 23, 2017 at 16:00

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2015253 Over a year ago

Works like a charm! Just to add: as unique_key was not neccessary, it seems to work flawless even without that part.

Collectives™ on Stack Overflow

Get array from database, using one of the columns as array key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related