I have a custom query in CodeIgniter to count results.
return $this->db->query("SELECT COUNT(*) FROM post WHERE MATCH (title, description) AGAINST ('+$search_string*' IN BOOLEAN MODE)", NULL, TRUE);
But this is generating only 1 rows, but if I run this query in phpMyAdmin, then it shows the correct results. What am I doing wrong?
$q = $this -> db -> query($your_custom_query);
return $q -> num_rows();
This should work for you, but you have to replace COUNT(*) by any field in the table or *, i would suggest a field. like say .. title.
You query does not work because $this->db->query resturns and object which needs to be converted to an array, by someting like foreach ($q->result() as $row) {} ..
you may also try return $this -> db -> query($your_custom_query)->result();
$query = $this->db->query("SELECT COUNT(*) as totalCounnt FROM post WHERE
MATCH (title, description)
AGAINST ('+$search_string*' IN BOOLEAN MODE)", NULL, TRUE);
$result = $query->row();
return $result->totalCount;
This is how count is done using CI active record class
$this->db->where('name', $name);
$this->db->where('id !=', $artist_id);
$this->db->from('artists');
return $this->db->count_all_results();
i don't think there is extra parameters in query()...docs here
however going thorugh but if i run this query in phpmyadmin then it shows correct results..
you can try pritning the last query that was made just to check if it is correct or not...you can do that by..
$test=$this->db->query("SELECT COUNT(*) FROM post WHERE MATCH (title, description) AGAINST ('+$search_string*' IN BOOLEAN MODE)"); //no need of other parameters
echo $this->db->last_query();exit;
and after query is made you have to generate result...
in yourcase that would be..
return $this->db->query("SELECT COUNT(*) FROM post WHERE MATCH (title, description) AGAINST ('+$search_string*' IN BOOLEAN MODE)")->row_array();
ways for generating result
query() method signature. Your advised snippet is insecure/unstable because you are not using a parameterized query. A user's search string should not be directly written into the raw SQL.COUNT() without GROUP BY will always return a single row. In your query the result set will be a single row with a single column -- and the count can be expected to be an integer which is 0 or greater.
You have several available approaches for your MATCH AGAINST query.
Query builder methods:
public function countViaMatchAgainst(string $search): int
{
return $this->db->where(
sprintf(
'MATCH (title, description) AGAINST (%s IN BOOLEAN MODE)',
$this->db->escape("+$search*")
)
)
->count_all_results('post');
}
Execute raw SQL with a parameterized query.
public function countViaMatchAgainst(string $search): int
{
$sql = <<<SQL
SELECT COUNT(*) totalCount
FROM post
WHERE MATCH (title, description) AGAINST (? IN BOOLEAN MODE)
SQL;
return $this->db->query($sql, "+$search*")->row('totalCount');
}
I do not endorse returning a payload with rows of column data unless you actually intend to use it. This means that you should NOT be removing COUNT(*) from your raw query using num_rows().
Neither of the above scripts will return any row/column values; only the count integer will be returned.