Codeigniter Call to a Member Function Num Rows on Boolean

PHP Fatal error: Call to a member function num_rows() on boolean in

As discussed in our chat, the issue is caused by the server migration and the new server using MySQL 5.7+ which enables only_full_group_by by default.

The reason is that MySQL is not able to select a determinant value related to the groupings, and errors instead of silently ignoring the potentially undesired value(s) in the SELECT statement, as it did in prior versions of MySQL.


For example: SELECT customer, SUM(order_total) FROM orders GROUP BY purchase_date

If two or more customers have the same purchase_date, only one can be shown and MySQL needs to determine which one you want.

Since CodeIgniter defaults to using SELECT * for the statement when using $this->db->get(), the error is encountered.

To resolve the issue, it is highly recommended to update all of your GROUP BY related queries, in the entire application, to be MySQL 5.7 compatible. This will ensure you are retrieving the correct values from your GROUP BY statements.

There are a few options you can use, in order of least complex and most beneficial.

1) Use $this->db->count_all_results() instead of $query->num_rows()
(aka: SELECT COUNT(*))

$this->db->where("savsoft_result.quid",$quid);
$this->db->group_by("savsoft_result.uid");
$res[0] = $this->db->count_all_results('savsoft_result');

$this->db->where("savsoft_result.quid",$quid);
$this->db->where("savsoft_result.uid !=",$uid);
$this->db->where("savsoft_result.score_obtained <=",$score);
$this->db->group_by("savsoft_result.uid");
$res[1] = $this->db->count_all_results('savsoft_result');

Note the removal of order_by as it is not needed to count the rows.

2) Limit the values to only those grouped

$this->db->select('savsoft_result.uid');
$this->db->where("savsoft_result.quid",$quid);
$this->db->group_by("savsoft_result.uid");
$this->db->order_by("savsoft_result.score_obtained",'DESC');
$query = $this->db->get('savsoft_result');
$res[0] = $query->num_rows();

$this->db->select('savsoft_result.uid');
$this->db->where("savsoft_result.quid",$quid);
$this->db->where("savsoft_result.uid !=",$uid);
$this->db->where("savsoft_result.score_obtained <=",$score);
$this->db->group_by("savsoft_result.uid");
$this->db->order_by("savsoft_result.score_obtained",'DESC');
$querys = $this->db->get('savsoft_result');
$res[1] = $querys->num_rows();

3) Explicitly define ANY_VALUE for all non-grouped columns

(This is the equivalent of disabling only_full_group_by)

Using the query I provided above.

SELECT ANY_VALUE(customer), SUM(order_total) FROM orders GROUP BY purchase_date

So you should be able to use something like the following, be sure to add ALL of the non-grouped columns in the related table.

$this->db->select('ANY_VALUE(savsoft_result.quid), ANY_VALUE(savsoft_result.score_obtained), savsoft_result.uid');
$this->db->where("savsoft_result.quid",$quid);
$this->db->group_by("savsoft_result.uid");
$this->db->order_by("savsoft_result.score_obtained",'DESC');
$query = $this -> db -> get('savsoft_result');
$res[0] = $query->num_rows();

$this->db->select('ANY_VALUE(savsoft_result.quid), ANY_VALUE(savsoft_result.score_obtained), savsoft_result.uid');
$this->db->where("savsoft_result.quid",$quid);
$this->db->where("savsoft_result.uid !=",$uid);
$this->db->where("savsoft_result.score_obtained <=",$score);
$this->db->group_by("savsoft_result.uid");
$this->db->order_by("savsoft_result.score_obtained",'DESC');
$querys = $this->db->get('savsoft_result');
$res[1] = $querys->num_rows();

Alternatively you can disable the only_full_group_by sql_mode.

You can temporarily disable it by updating the global variable, by executing a SET GLOBAL statement.

SET GLOBAL sql_mode=(SELECT TRIM(BOTH ',' FROM REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')));

This will retrieve the currently used sql_mode and remove the ONLY_FULL_GROUP_BY portion, then remove any leading or trailing comma that remains. However once the MySQL service is restarted, you will need to execute the statement again.

To ensure only_full_group_by does not revert back to enabled when the MySQL service is restarted, edit your my.cnf MySQL configuration file, to omit the ONLY_FULL_GROUP_BY in the sql_mode declaration.

You can retrieve the current sql_mode by executing the statement

SHOW VARIABLES LIKE 'sql_mode';

Which should return something like:

ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Copy the entire text, then declare the sql_mode in the my.cnf file, removing the ONLY_FULL_GROUP_BY portion.

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After making the change to the my.cnf file, restart the MySQL service.

Call to a member function num_rows() on boolean

For insert and update query we use $this->db->affected_rows()

Also change your query too

public function new_pass(){

$email = $this->input->post('email');
$pass1 = $this->input->post('pass1');
$pass2 = $this->input->post('pass2');
$this->db->set('pass1',$pass1);
$this->db->set('pass2',$pass2);
$result = $this->db->where('email', $email);
$this->db->update('user');

$afftectedRows = $this->db->affected_rows();
if ($afftectedRows == 1) {
return TRUE;
}else{
return FALSE;
}
}

CODEIGNITER Fatal error : call to a member function num rows on boolean

A boolean query return means that your query is failing and thus num_rows() result() row() whatever... won't work.

You can identify the error in your SQL by going into the database configuration file and turning on db debug.

CODEIGNITER Exception: Call to a member function num_rows() on boolean

This is because get() either returns a ResultInterface or false (https://github.com/codeigniter4/CodeIgniter4/blob/e31166e246a7b27ba555897ef87b9812c9a01195/system/Database/BaseBuilder.php#L1855)

In case of false you cannot call num_rows() on it (this is what the error message says)
Try something like this:

$result = $this->db->get();
return $result ? $result->num_rows() : 0;


Related Topics



Leave a reply



Submit