combining two rows wit the Query Builder

I have two rows showing up in mysql table
BC     taxyear     research     support     supervision
IOS    201704     $90000
IOS    201704                         $6234
I'd like to make these two lines into a single line like this
IOS     201704    $90000        $6234
I thought I could use group_by like this
$this->db->group_by(array("BC", "taxyear")); 
$this->db->insert('substantially_all_render_temp', $data);
But this doesn't work. What am I doing wrong? $data contains an array of the data I am placing into the table.

proof that an old dog can learn new tricks

You can't do a GROUP BY on an INSERT statement.

You'd need to merge the arrays before you insert them (or SELECT them, then merge the arrays then remove the rows and reinsert the merged array).

Probably something like this.

PHP Code:

// ... after selecting $row1 and $row2
$data = array_merge(array_filter($row1), array_filter($row2));
$this->db->delete('substantially_all_render_temp', [
    'BC' => $row1['BC'],
    'taxyear' => $row1['taxyear']
]);
$this->db->insert('substantially_all_render_temp', $data); 


Thanks. That is helpful. How would I do it? Like this?

              $this->db->group_by(array("BC", "taxyear"));
                $query = $this->db->get('substantially_all_render_temp');

The problem is that they don't exist in any table prior to me inserting them. I am going from a table that has this form

BC      dollars    taxyear      type
IOS    2000        201704      research
IOS    6234        201704      support
GOL    100          201704      supervision

and I want to get it into this form for rendering
BC      taxyear    research  support  supervision
IOS    201704      2000          6234          0

taking your advice I ran the following line in mysql workbench but the results were not encouraging:
SELECT * FROM substantiator.substantially_all_render_temp GROUP BY BC, taxyear

proof that an old dog can learn new tricks

The problem is that they don't exist in any table prior to me inserting them. I am going from a table that has this form

Then why do you insert 2 rows if you want all the data in a single row? The best way to do this would be to collect all the data in a single array and insert only one row. Or another less efficient way would be to check if a row already exists for this "taxyear" and do an update instead of an insert.

CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/

Thanks Beer. I ended up merging the rows with this statement.
$sql="INSERT INTO substantially_all_render_temp2 SELECT '',PR, taxyear,
sum(research_dol) as research,
sum(support_dol) as support,
sum(supervision_dol) as supervision,
sum(research_dol) as total_POE_dol,
sum(research_dol)+sum(support_dol)+sum(supervision_dol) as total_BC_wages_dol,
sum(research_dol)*100/ (sum(research_dol)+sum(support_dol)+sum(supervision_dol)) as percent_substantially_all
FROM substantially_all_render_temp
GROUP BY BC, taxyear";

proof that an old dog can learn new tricks