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
Sum Values from Column in HTML
How to Remove Accents from Characters in a PHP String
Mongodb Updating Fields in Nested Array
(Php, Mysql) Result Could Not Be Converted to String
Generating a Truly Unique Order Id in PHP
Reference - What Does This Symbol Mean in PHP
How to Get Useful Error Messages in PHP
Secure Hash and Salt For PHP Passwords
Cannot Simply Use Postgresql Table Name ("Relation Does Not Exist")
Telegram Botapi, Send Message to Multiple Chat_Id
How to Prevent Multiple Inserts When Submitting a Form in PHP
Php How to Loop Through a Post Array
Keep Values Selected After Form Submission
Replace Preg_Replace() E Modifier With Preg_Replace_Callback