Merge multiple table rows into one row

[Plugnz13]

Hi there, I'm trying to populate a table with data that is recurring over multiple months but has the same name. the table currently distributes the new items onto a separate row. 

it currently looks like this 

I'd like to show the same but with 1 row 

the code looks like this

<tbody>
                                        <?php $prevcat = "";?>
                                        <?php  foreach ($result as $cat) {?>
                                            <?php $maincat = $cat[0];?>
                                            <?php $subcat = $cat[1];?>
                                            <?php echo "<tr>" ?>
                                            <?php if($prevcat !== $subcat) {?>
                                            <th colspan="27" class="job-categories"><?php echo $subcat;?></th>
                                              <?php $prevcat = $subcat;?>
                                              <?php }?>
                                            <?php echo "</tr>" ?>
                                            <?php $prevexp = "";?>
                                            <?php  foreach ($result1 as $exp) {?>
                                                <?php $newexp = $exp[1]?>
                                                <?php echo "<tr>" ?>  
                                                <?php if($exp[4]==$subcat){?>
                                            <th><?php echo $newexp; //expname?></th>
                                            <?php
                                                $FYarray = [4,5,6,7,8,9,10,11,12,1,2,3]; // fix this to display data of Jan/Feb/March next year, not this year. this line make condition to consider only month
                                                for($i=0;$i<count($FYarray);$i++) {
                                                    if($exp[8]!=null){
                                                        if($exp[8]!="0000-00-00"){
                                            ?>
                                            <td>
                                                <?php if(date('n',strtotime($exp[8]/*startdate*/))==$FYarray[$i]){echo $exp[2]/*expamount*/;}else{echo "";}}else
                                                         if($prevexp == $newexp) {
                                                        echo "<td></td>";}else{
                                                         echo "<td></td>";} ?>          
                                            </td>
                                            
                                                <?php if($exp[10]!=null){
                                                        if($exp[10]!="0000-00-00"){?>
                                            <td class="actual">
                                                <?php if(date('n',strtotime($exp[10]/*paiddate*/))==$FYarray[$i]){echo $exp[3]/*expactamount or paid amount*/;}else{echo "";}}else{
                                                         echo "<td class="."actual"."></td>";} }else{
                                                         echo "<td class="."actual"."></td>";} ?>                                                        
                                            </td>
                                            <?php }?>
                                            <?php //$prevexp = $newexp;?>
                                            <?php } ?>
                                            <td class=".'total'."></td>
                                            <td class=".'total'."></td> 
                                            
                                            </tr>
                                            <?php } ?>
                                            <?php } ?>

and 

//display expense category
$query1= "SELECT * FROM categories";
try{
    $stmt = $mysqli->prepare($query1);
    $stmt->execute();
    $resultSet = $stmt->get_result(); 
    $result = $resultSet->fetch_all();
}
catch(Exception $ex){
 echo ($ex -> getMessage());
}

//display expenses
$query2= "SELECT * FROM expenses ORDER BY expstartdate" ;
try{
    $stmt = $mysqli->prepare($query2);
    $stmt->execute();
    $resultSet1 = $stmt->get_result(); 
    $result1 = $resultSet1->fetch_all();
}
catch(Exception $ex){
 echo ($ex -> getMessage());
}

 

database looks like this

Any advice would be much appreciated. 

Thanks in advance.

[Barand]

When you process the query results, build an array with the required structure for your output. In this case something like

$data = [
            'Kindred Coaching' => [
                                      2204 => [ 'Forecast' => '',    'Actual' => ''    ],
                                      2205 => [ 'Forecast' => '',    'Actual' => ''    ].
                                      2206 => [ 'Forecast' => '345', 'Actual' => '345' ],
                                      2207 => [ 'Forecast' => '345', 'Actual' => ''    ],
                                      2208 => [ 'Forecast' => '345', 'Actual' => ''    ],
                                      2209 => [ 'Forecast' => '345', 'Actual' => ''    ],
                                      2210 => [ 'Forecast' => '345', 'Actual' => ''    ],
                                      2211 => [ 'Forecast' => '345', 'Actual' => ''    ]
                                  ] ,
            'Other Coaching' =>   [
                                      2204 => [ 'Forecast' => '',    'Actual' => ''    ],
                                      2205 => [ 'Forecast' => '',    'Actual' => ''    ],
                                      2206 => [ 'Forecast' => '123', 'Actual' => '234' ],
                                      2207 => [ 'Forecast' => '123', 'Actual' => ''    ],
                                      2208 => [ 'Forecast' => '123', 'Actual' => ''    ],
                                      2209 => [ 'Forecast' => '123', 'Actual' => ''    ],
                                      2210 => [ 'Forecast' => '123', 'Actual' => ''    ],
                                      2211 => [ 'Forecast' => '123', 'Actual' => ''    ]
                                  ]
        ];

then

foreach ($data as $account => $accdata) {
    // start row 
    // output account
    foreach ($accdata as $yrmnth => $values) {
        // output forecast and actual cells
    }
    // end row
}

 

[Plugnz13]

Hi Barand, thanks again for your help. I have tried what you suggested above and get stuck when it comes to accessing  the forecast and actual cells.

I get the following error. 

 

    foreach ($result1 as $exp1) {
            $expnm = $exp1[1];
            $fcexp  = $exp1[2];
            $actexp  = $exp1[3];
            $expcat  = $exp1[4];
            $expsdt  = $exp1[8];
            $exppddt  = $exp1[10];
            $fcd  = "";
            $actd = "";
            
            $data = [
                $expnm => [


                            2204 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2205 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2206 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2207 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2208 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2209 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2210 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2211 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2212 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2201 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2202 => ['Forecast' => $fcexp, 'Actual' => $actexp],
                            2203 => ['Forecast' => $fcexp, 'Actual' => $actexp]
                ]
                ];

                foreach ($data as $expense => $expdata) {
                    $tdata .= "<tr>" ;
                    $tdata .= "<th>" . $expense . "</th>";
                    foreach ($expdata as $yrmonth => $amount) {
                        foreach ($amount as $amt) {
                        $tdata .= "<td>" . $amt[0] . "</td>"; 
                        $tdata .= "<td>" . $amt[1] . "</td>"; 
                    }
                    }
                    $tdata .= "</tr>" ;
                }
                //$tdata .= "<th>" . $data[0][0][0] . "</th>";
                //$tdata .= "<td>" . $data[0][1][0] . "</td>";
                //$tdata .= "<td>" . $data[1][0][0] . "</td>";
                //$tdata .= "<td>" . $data[1][0][1] . "</td>";
//$tdata .= print_r ($data);
        }

have tried various options but none seem to work. any thoughts? 

Much appreciated.

[Barand]

1 hour ago, Plugnz13 said:

any thoughts? 

Several.

Where is line 98?

When you construct the array, where are those Yr/mth values coming from in the data (you have just hard-coded them) and throwing the same values into every month.

You have a subarray with keys "forecast" and "actual" but you subsequently try to output them with keys "0" and "1"

I would show you how to build the array but my software is useless at loading images of data into tables. (Images of code and data are as much use as chocolate teapots in this forum)

[Plugnz13]

Hi Barand, 

Thankyou, you have rightly seen my error

Quote

You have a subarray with keys "forecast" and "actual" but you subsequently try to output them with keys "0" and "1"

and I now see that it was a very rookie error! Have just revised it to this and it worked!

foreach ($expdata as $yrmonth => $amount) {
                        foreach ($amount as $amt) {
                        $tdata .= "<td>" . $amt . "</td>";  
                    }

I've left the $yrmonth values as being hard coded until I managed to get a result from the array that was working so that's my next task along with making sure the values are retrieved under the correct expense name and date.

Thankyou so much for you help.