Returning an array of objects from a query?

Question

I need a function for returning an array of services which each contains an id and a name. I've been looking in the mysqli documentation, but cannot verify if there exists smarter solution than this:

function getServices() {
    $services = array();

    $stmt = $this->db->prepare('SELECT id, name FROM services WHERE client_id=?');
    $stmt->bind_param("i", $this->clientId);
    $stmt->execute();
    $stmt->bind_result($id, $name);
    while ($stmt->fetch()) {
        $service = array();
        $service['id'] = $id;
        $service['name'] = $name;
        $services[] = $service;
    }
    $stmt->close();

    return $services;
}

It seems a bit cumbersome that I have to build the resulting array myself. Is there a way to let mysql build the array using the selected columns?

I don't see any reasons why cannot this code be used. Even if the mysql-built version exists (which I doubt, but I'm not sure, I always used this way), there is no need to change. — axiomer
– axiomer, Commented Feb 5, 2012 at 11:58

Decent Dabbler · Accepted Answer · 2012-02-05 12:16:13Z

2

I don't use mysqli, but from inspecting the docs, I believe this should work:

$stmt = $this->db->prepare('SELECT id, name FROM services WHERE client_id=?');
$stmt->bind_param("i", $this->clientId);
$stmt->execute();

$result   = $stmt->get_result();
$services = $result->fetch_all( MYSQLI_ASSOC );

$stmt->close();

return $services;

Thus using:
1. mysqli_stmt::get_result()
2. mysqli_result::fetch_all()

It is for PHP >= 5.3.0.

answered Feb 5, 2012 at 12:16

Decent Dabbler

22.8k11 gold badges77 silver badges107 bronze badges

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

2 Comments

dhrm Over a year ago

Thanks for your solution. Sadly my server is running PHP 5.2 so the get_result method does not exist.

Decent Dabbler Over a year ago

@DennisMadsen: Sorry to hear that. In that case you could perhaps extend mysqli_stmt and implement it yourself, if you think it's worth the effort. Or, indeed, use PDO, in stead of mysqli if you think it's worth the effort.

Community · Accepted Answer · 2023-11-17 20:29:45Z

1

Switch to PDO, and you have the option of

PDOStatement::fetchAll()

edited Nov 17, 2023 at 20:29

CommunityBot

11 silver badge

answered Feb 5, 2012 at 11:59

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

1 Comment

Darragh Enright Over a year ago

Seconded. You can pass fetch argument $stmt->fetchAll(PDO::FETCH_ASSOC) to return an associative array. Lots of other options here: php.net/manual/en/pdostatement.fetchall.php

Collectives™ on Stack Overflow

Returning an array of objects from a query?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related