Displaying categories and subcategories in php having different tables

Question

Just wanted to ask if there is an other way of PHP coding for displaying my subcategories and subsubcategries than mine.

My tables:

Categories
-------------------------
cat_ID | cat_name
------------------
4      | Baby & Kids
5      | Bicycles
6      | Boats
7      | Books & Comics
....
13     | Clothes & Accessories
....
35     | Sport & Fitness
36     | Study
....
38     | Toys & Games
....


Subcategories
-------------------------
subcat_ID | cat_ID | extra_cat_ID | subcat_name
------------------------------------------------
....
15        | 4      | 13           | Baby clothes
16        | 4      | 0            | Baby products
17        | 4      | 13           | Kids clothes
18        | 4      | 38           | Toys
19        | 5      | 0            | Bycicles
20        | 5      | 0            | Bycicle gear & Accessories
21        | 6      | 0            | Boat parts
22        | 6      | 0            | Other Boats
23        | 6      | 0            | Power Boats
24        | 6      | 0            | Sailboats
25        | 6      | 35           | Windsurf & Surfing
26        | 7      | 0            | Antiquarian
27        | 7      | 0            | Books
28        | 7      | 38           | Childrens books
29        | 7      | 0            | Comics
30        | 7      | 0            | Magazines & Newspapers
31        | 7      | 36           | Study & Training



Subsubcategories
-------------------------
subsubcat_ID | subcat_ID | subsubcat_name
-----------------------------------------
...
470          | 15        | Baptism outfits
471          | 15        | Bibs
472          | 15        | Body warmers
473          | 15        | Bodysuits
....
496          | 16        | Baby bath
497          | 16        | Baby books
498          | 16        | Baby inserts
499          | 16        | Baby monitors
....
548          | 17        | Belts
549          | 17        | Blouses & Shirts
550          | 17        | Body warmer
551          | 17        | Boots
....
....
740          | 26        | Music
741          | 26        | Navy
742          | 26        | Novel
743          | 26        | Photography
....
....
867          | 30        | Animals
868          | 30        | Arts and Culture
869          | 30        | Branch
870          | 30        | Cars
870          | 30        | Computers
....
....
etc.

I know it's just super basic HTML, but what I want to do is this:

subcat_name 1

subsubcatname 1
subsubcatname 2
subsubcatname 3
subsubcatname 4
subsubcatname 5
subsubcatname 6
subsubcatname 7

subcat_name 2

subsubcatname 8
subsubcatname 9
subsubcatname 10
subsubcatname 11
subsubcatname 12

subcat_name 3

subsubcatname 13
subsubcatname 14
subsubcatname 15
subsubcatname 16
subsubcatname 17
subsubcatname 18

etc ...

My code I use right now:

$result=mysqli_query($connect,"SELECT subcategories.subcat_name, subsubcategories.subsubcat_name, subcategories.subcat_ID FROM subcategories INNER JOIN subsubcategories ON subcategories.subcat_ID=subsubcategories.subcat_ID WHERE subcategories.cat_ID = $cat_ID OR subcategories.extra_cat_ID = $cat_ID ORDER BY subcategories.subcat_name, subsubcategories.subsubcat_name ASC");
$lastcat = 0;
while ($row = mysqli_fetch_array($result)){
    if($lastcat != $row['subcat_ID']){
        $lastcat = $row['subcat_ID'];
        echo "<br>"; 
        echo $row['subcat_name'];
        echo "<br>";
        echo "<br>";
        }
    echo $row['subsubcat_name'];
    echo "<br>";
    }

My code is working fine but what I want to know is if there is a better or safer way of handling it in PHP? Or am I doing it the right way?

Answer 1

Yes, let's see:

• You are vulnerable to SQL injection: By including variables directly in the query, you are making yourself vulnerable to SQL injection attacks. Use prepared statements. Further reading: How can I prevent SQL Injection in PHP?

• Don't use <br> for vertical spacing control. You should use proper semantic elements, for example:

  <ul>
      <li>Subcat 1
          <ul>
              <li>Subsubcat 1</li>
              <li>Subsubcat 2</li>
              <li>Subsubcat 3</li>
          </ul>
      </li>
  </ul>
  

Aside from that, LGTM. Good job! :)

---

Example for prepared statements:

$stmt = mysqli_prepare($connect, "SELECT subcategories.subcat_name, subsubcategories.subsubcat_name, subcategories.subcat_ID FROM subcategories INNER JOIN subsubcategories ON subcategories.subcat_ID=subsubcategories.subcat_ID WHERE subcategories.cat_ID = ? OR subcategories.extra_cat_ID = ? ORDER BY subcategories.subcat_name, subsubcategories.subsubcat_name ASC");
mysqli_stmt_bind_param($stmt, "ii", $cat_ID, $cat_ID);
mysqli_stmt_execute($stmt);