CODEWITHSUNDEEP
phpmysqlpdo
user2015253
user2015253

Reputation: 1444

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

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.

Upvotes: 2

Views: 194

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31792

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

Upvotes: 1

Related Questions