4
\$\begingroup\$

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?

\$\endgroup\$
4
  • \$\begingroup\$ Welcome to Code Review! The first part of your question is on-topic (and a good question), but the second part "What am I doing wrong?" is not on-topic here. (Hint if I remember my PHP correctly: Find out what mysqli_stmt_execute returns, and what parameter mysqli_fetch_array requires). \$\endgroup\$ Commented Jun 10, 2014 at 10:05
  • \$\begingroup\$ Ok I'll delete last part and ask at Stackoverfloaw. Wasn't aware it was seperated that strictly. Good to know. Thank you. :) \$\endgroup\$
    – Thoaren
    Commented Jun 10, 2014 at 10:20
  • \$\begingroup\$ @ Simon André Forsberg Thanks for the hint! ;) \$\endgroup\$
    – Thoaren
    Commented Jun 10, 2014 at 14:38
  • \$\begingroup\$ I've removed the updated code as it should be posted as a new follow-up question for further review. \$\endgroup\$
    – Jamal
    Commented Jun 10, 2014 at 17:11

1 Answer 1

3
\$\begingroup\$

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);
\$\endgroup\$
4
  • \$\begingroup\$ Thank you Madara. Could you show me how this code looks like implemented in mine? Can't figure out how these prepared statements work. Thanks! \$\endgroup\$
    – Thoaren
    Commented Jun 10, 2014 at 9:20
  • \$\begingroup\$ Added example.. \$\endgroup\$ Commented Jun 10, 2014 at 9:28
  • \$\begingroup\$ @ Madara Uchiha Thanks Madara. I tried to implement this but it seems I'm doing something wrong. I asked for help here because this is the codereview site. \$\endgroup\$
    – Thoaren
    Commented Jun 10, 2014 at 10:48
  • \$\begingroup\$ @ Madara Uchiha I think I found how to implement this. At least it works. Can you dubbelcheck for me please? Also gonna work on the html list elements now. Thanks for you advice! \$\endgroup\$
    – Thoaren
    Commented Jun 10, 2014 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.