如何从数据库创建4维数组

Question

I'm trying to figure out how to make this code work. Basically i have used MySQL to fetch an associative array containing multiple values.

example database: database name = Products

 ----------------------------------------------------------------.
|   name     | overcategory |   category   |     subcategory     |
|  Talon     |    null      | stud welding | capacitor discharge |
| cdss m3x40 | studs/bolts  |   cd-studs   |   stainless steel   |
----------------------------------------------------------------.

I used the sentence SELECT name, overcategory, category, subcategory FROM Products WHERE 1 = 1;

In the actual database there are more entries, but they are not relevant for this question.

The result i want to output as a menu on the left hand side of the screen.

<nav>
   <h1> "$overcategory" </h1>
     <h2> "$category" </h2>
         <h3> "$subcategory" </h3>
           <a href = "">$productname </a>
           <a href = "">$productname3 </a>

   <h1> "$overcategory2" </h1>
     <h2> "$category2" </h2>
         <h3> "$subcategory2" </h3>
           <a href = "">$productname2 </a>
</nav>

i was thinking of doing this by creating a multidimensional array that would look something like this:

    $testArray = array(
        ''=>array(
              'Boltsveiseapparater'=>array(
                            'Kondensator'=>array(
                                         'Talon',
                                         'LBS-75'
                                        ),
                                    'Arc'=>array(
                                         'LBH-410',
                                         'LBH-800'
                                        )
                                  )
            ),
            'Pinner/bolter'=>array(
                                        'CD-pinner'=>array(
                                                            'rustfri'=>array(
                                                                            'cdss m3x35',
                                                                            'cdss m3x40'
                                                                            ),
                                                              'stål'=>array(
                                                                            'cdms m3x35',
                                                                            'cdms m6x35'
                                                                            ),
                                                                'Alu'=>array(
                                                                            'cdal m3x10',
                                                                            'cdal m8x80'
                                                                            )
                                                        ),
                                        'Bossinger'=>array(
                                                            'Stål'=>array(
                                                                            'M6x10 5x8',
                                                                            'M5x12 4x10'
                                                                            ),
                                                                'Alu'=>array(
                                                                            'M6x10 5x8',
                                                                            'M5x12 4x10'
                                                                            ),
                                                            'Rustfri'=>array(
                                                                            'M6x10 5x8',
                                                                            'M5x12 4x10'
                                                                            )
                                                        )
                                )

      );                                                                        

I have no idea how to do this by looping through the results. is there any way of doing this without making a giant array of doom? if not, can someone assist me in decyphering this conundrum.

Thank you in advance. BM.

Answer 1

If you sort each of the columns of your results in your SQL query, you can just loop through each result and compare it with the last entry and adjust your output based on that.

SELECT name, overcategory, category, subcategory FROM Products ORDER BY overcategory, category, subcategory, name;

Otherwise, here is one way of handling your nested array:

<?php

echo "
";
echo "<nav>
";
foreach ($testArray as $overcategory_name => $category_data) {
    echo "\t<h1>$overcategory_name</h1>
";
    foreach ($category_data as $category_name => $subcategory_data) {
        echo "\t\t<h2>$category_name</h2>
";
        foreach ($subcategory_data as $subcategory_name => $product_name_data) {
            echo "\t\t\t<h3>$subcategory_name</h3>
";
            foreach ($product_name_data as $product_name) {
                echo "\t\t\t\t<a href = \"\">$product_name</a>
";
            }
        }
    }
}
echo "</nav>
";