How to count number of rows while using UNION in codeigniter?

62
January 13, 2019, at 09:20 AM
$this->db->select('*');
$this->db->from('table1');
$where = "tag = '".$tag."'";
$this->db->where($where);
$query1 = $this->db->get();
$result1 = $query1->num_rows();
$this->db->select('*');
$this->db->from('table2');
$where = "tag = '".$tag."'";
$this->db->where($where);
$query2 = $this->db->get();
$result2 = $query2->num_rows();
$result = $result1+$result2;
return $result;

In this question, I have two tables table1 and table2 where the tables structure of both are same. Now, I want to count number of rows. Now, it showing me wrong count data. If tag data present in first table then it counts 1 and if tag data present in both table then it counts 2. So, How can I do this? Please help me.

Thank you

Answer 1

If your query is ok and each one returns the exact result you need, And now you just wanna get num of rows only then its better to use count_all_results() .. and be on the safe side and reset the query builder before making another query:

...
$r1= $this->db->count_all_results();
$this->db->reset_query();
...
$r2= $this->db->count_all_results();

Both of them should return the count of results if your query is alright, then simply add them:

$sum = $r1 + $r2;
READ ALSO
Errors installing IPSearch from GitHub

Errors installing IPSearch from GitHub

I got some few errors while installing IPSearch package from GitHub, that I couldn't solve with the answers of other people, in other forums too, so I was hoping that some of you could suggest solutions to the problems below

27
Is it possible to do a group_concat inside another group_concat?

Is it possible to do a group_concat inside another group_concat?

I'm building a webshop that sells shoesI'm trying to write a command that will show the quantity of each size for 1 specific sneaker

61
MySql incremental backup of only added lines

MySql incremental backup of only added lines

How can I create incremental backups of a MySql database that only includes data that is added and ignores data that has been removed?

26
How to switch a SQL 5.6 database to a SQL 5.5 database [on hold]

How to switch a SQL 5.6 database to a SQL 5.5 database [on hold]

I've to launch a website that I develop on my side with a SQL version 56

42