codeigniter count_all_results with join table

Question

I have a problem getting the number of rows from my tables.

Here is my mysql query using codeiginiter:

$this->db->where('rd', 1);
$this->db->where('id_user_first', $user_type);
$this->db->from("messaging");
$this->db->join('answers', "answers.id_message = messaging.id AND answers.id_user != '$user_type'");
$count_reponse  = $this->db->count_all_results();

I get this error message:

Parse error: syntax error, unexpected '' '' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ';' in

@RahulMeshram The problem is not there sir, and i prefer to use them especially for long value or encrypted entries. — S Triss, Sep 22 '17 at 12:15

NikuNj Rathod · Accepted Answer · 2017-09-22T12:23:36.063

2

You can try this solution for your problem :

Please changes query

$this->db->select('messaging.*, answers.*');
$this->db->from("messaging");
$this->db->join('answers', "answers.id_message = messaging.id");
$this->db->where('answers.id_user <>', $user_type);
$this->db->where('messaging.rd', 1);
$this->db->where('messaging.id_user_first', $user_type);
$count_reponse  = $this->db->count_all_results();

edited Sep 22 '17 at 12:23

answered Sep 22 '17 at 12:15

NikuNj Rathod

1,663
1
17
26

Thank you for your efforts, but still getting the exact same error message. – S Triss Sep 22 '17 at 12:20
Please post new error message and please check now new answer. – NikuNj Rathod Sep 22 '17 at 12:23
Problem solved, thank you sir. – S Triss Sep 22 '17 at 12:36

always-a-learner · Answer 2 · 2017-09-22T12:42:08.613

You need to escape your quotes or change the inner double-quotes to single quotes.

$this->db->join('answers', 'answers.id_message = messaging.id AND answers.id_user != '.$user_type.'','left');

Try This query:

$this->db->where('rd', 1);
$this->db->where('id_user_first', $user_type);
$this->db->from("messaging");       
$this->db->join('answers', 'answers.id_message = messaging.id', 'left');        
$this->db->where('answers.id_user !=', $user_type);
$count_reponse  = $this->db->count_all_results();

I hope it will help. Please leave a comment if any error came.

codeigniter count_all_results with join table

2 Answers2