I have 03 MySQL databases as 2022_db, 2023_db and 2024_db. All the tables and table structures in the 03 databases are same & included different values.I want to get the sum of values in two tables in 02 databases 2022_db and 2023_db into a single report. Tables are named as vote & finacne_budget_issue. The columns are as vote and amount. I used the following query to do that.
public function amt_tot()
{
$otherdb2022 = $this->load->database('2022_db', TRUE);
$otherdb2023 = $this->load->database('2023_db', TRUE);
$q = $this->db->query("
select
SELECT finance_vote.vote, SUM(amount) AS amount
FROM (
SELECT finance_vote.vote, SUM(amount) AS amount AS amt_2022
FROM $otherdb2022.finance_budget_issue
GROUP BY vote
UNION ALL
SELECT finance_vote.vote, SUM(amount) AS amount AS amt_2023
FROM $otherdb2023.finance_budget_issue
GROUP BY vote
) AS tot
GROUP BY vote");
if ($q->num_rows() > 0) {
$results = $q->result();
return $results;
}
}
But the query says that systax error "near 'SELECT vote, SUM(amount) AS amount FROM..... ". What may be going wrong in my query ? Can any one help this for me ?
You have double aliased the sums, e.g.
SUM(amount) AS amount AS amt_2022
However, a better pattern to use here would be to union the two tables first, then aggregate that as a subquery and take the totals:
SELECT vote, SUM(amount) AS amount
FROM (
SELECT vote, amount
FROM table_2022
UNION ALL
SELECT vote, amount
FROM table_2023
) t
GROUP BY vote;
By the way, it is generally bad practice to bind a table name using a PHP variable. This opens your code to outside SQL injection attacks. That being said, if you are certain that $otherdb2022
and $otherdb2023
would not be getting populated from outside data, then it might be safe.