How do you list two columns but not show duplicates from first column?

[Plugnz13]

Hi All,

I've been trying to work out how to list items in a two column database in a table side by side without repeating the left hand items.

The left column is a main category column, the right column is a sub category column. 

I'd like it to look like this:

 Main Category                         Sub Category

Apportioned Expenses             Home Office

Bank Fees Loans                       Loan Drawdowns

                                                    Bank Fees

                                                   Loan Fees

                                                   Loan Repayments

GST Claimable                          Marketing/Media

                                                   Office Expense

                                                   etc

and so on. 

this is what I'm seeing instead

      

The database "categories" looks like this   

           

Code so far looks like this

   

$query3= "SELECT DISTINCT* FROM categories ORDER BY maincat";
try{
    $stmt = $mysqli->prepare($query3);
    $stmt->execute();
    $resultSet1 = $stmt->get_result(); 
    $result1 = $resultSet1->fetch_all();
}

and 

<?php  foreach ($result1 as $output_1) {?>
                                            <tr>
                                                <td> <a href=""><?php echo $output_1[0];?> </a></td>
                                                <td> <a href=""> <?php echo $output_1[1];?></a></td>
                                                <?php } ?>                      
                                            </tr>

     Any thoughts would be much appreciated. Thanks in advance.

MYSQL version

Edited June 21, 2022 by Plugnz13

[mac_gyver]

when you fetch the data from the query, you would index/pivot it using the maincat as the main array index. this will give you a sub-array of rows for each maincat value. when you loop to display the result, you can use php’s count() function to get the number of rows for each maincat value and use this as a rowspan attribute in the column where you are displaying the maincat value.

 

[Barand]

Only out the maincat when it changes to a new value.

set prevcat = maincat
foreach record
    start new row
    if maincat != prevcat
        output maincat
        set prevcat = maincat
    else
        output blank
    endif
    output subcat
    end row
end foreach

 

[Plugnz13]

Thanks mac_gyver and Barand, that worked well! Much appreciated.