Codeigniter4 QueryBuilder example – getResultArray Vs getResult

Codeigniter4 provides a query builder to form and execute complex queries. We often convert the query response to JSON or Object. CI4 provides inbuilt functions for the same so we can avoid writing the loop ourselves.

Let us take an example. Consider the table structure below with some dummy records inserted in it.

CREATE TABLE `Persons` (
  `id` int NOT NULL AUTO_INCREMENT,
  `firstname` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `pincode` int DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

INSERT INTO `Persons` (`firstname`,`lastname`,`age`,`address`,`pincode`)
VALUES
  ("Quamar","Avila","23","Bình Phước, Germany","773499"),
  ("Yoko","Clarke","40","Bellstown South Africa","361356")

We will create a Model file under app/Config/UserModel.php with below contents.

<?php

namespace App\Models;

use CodeIgniter\Model;

class UserModel extends Model{
    protected $table      = 'Persons';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;  //whether the mentioned primaryKey is an autoincrement field

    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_date';
    protected $updatedField  = 'updated_date';
    protected $useTimestamps = true;
    protected $allowedFields = ["firstname","lastname","age","address","pincode"];


    public function __construct(){
        parent::__construct();
        $this->db = \Config\Database::connect();
    }

    public function getAllUsers(){
        $builder = $this->db->table($this->table." p ");
        $builder->select('p.firstname, p.lastname, p.age');
        $builder->where("p.age > ",35);
        $builder->orderBy('p.id', 'DESC');

        #$sql = $builder->getCompiledSelect(); echo $sql;
        $query = $builder->get();

        $users =  $query->getResultArray(); //returns response as array
        #$users =  $query->getResult(); //return response as object
        return $users;
    }

}

?>

To call the database connection with configuration settings, we call $this->db = \Config\Database::connect(); in the constructor method. This sets the variable $db of current class as a database connection object.

We have commented a few lines of code which you can try. The $builder->getCompiledSelect() function returns the final SQL query. We can use this to cross check our query or debugging. this function must be called before running $builder->get() .

The $builder->get() call executes the query and returns a response. If we want the resultset in an array we can call
$query->getResultArray(). If we want the resultset in an object we can call $query->getResult() method. We can dump the variable results to see the datatype and values.

On the controller side, we can call this model and method as below in app/Controllers/Home.php

<?php

namespace App\Controllers;

use App\Models;

class Home extends BaseController
{
    public function index()
    {
        $userModel =  new \App\Models\UserModel();
        $allPersons = $userModel->getAllUsers();
        var_dump($allPersons);
        $data["users"] = $allPersons;
        //pass variable to view file as required

    }
?>

When we run this on localhost, we can see the variable dump values being printed.
getResultArray() function would yield the result as below:

array(1) {
  [0]=>
  array(3) {
    ["firstname"]=>
    string(4) "Yoko"
    ["lastname"]=>
    string(6) "Clarke"
    ["age"]=>
    string(2) "40"
  }
}

getResult() will give the following output form: We can see it is an array of object.

array(1) {
  [0]=>
  object(stdClass)#83 (3) {
    ["firstname"]=>
    string(4) "Yoko"
    ["lastname"]=>
    string(6) "Clarke"
    ["age"]=>
    string(2) "40"
  }
}