Add mysqli options in Codeigniter 4

Question

I have several fields in my database that are DECIMAL(10,2) or INT values, when I return the results everything is returned as string.

Example:

    $sql = "SELECT id, operacion_id FROM operaciones";
    $query = $this->db->query($sql);
    $result = $query->getResultArray();

    return $this->response->setStatusCode(200)
                    ->setContentType('application/json')
                    ->setJSON($result);

And that returns:

[{
    "id": "31",
    "operacion_id": "ES-2021-0031"
},
{
    "id": "30",
    "operacion_id": "ES-2021-0030"
}]

I tried to put directly in system\Database\MySQLi\Connection.php connect() method:

$this->mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);

This works well but I know that it is not upgrade safe.

Answer 1

You should be able to apply this option to mysqli property of your database object.

// run this after your DB connection has been initialized
$this->db->mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
// and then run your queries
$query = $this->db->query($sql);

When working with prepared statements, you don't have to apply this setting as prepared statements always return the data in the correct type.