Mysql sub-cat and parent cat with posts [Database schema]

Okay so I have a simple category table and a separate posts table easy right but when the user posts a post I wast think should I store both the sub and parent cat in the posts table but would that not be a lot of data duplication so I instead just store the sub_cat then I use a few PHP functions to query the database for the primary cat and its name.

categories table

ID | cat_name | main_cat
1  | Dinner   | 0 
2  | Chicken  | 1

posts table

ID | title | sub_cat | fields that are not related to Q
1  | test  | 2       |

Get parent(main) category
$sub_cat = is from a selection query that gets posts and their sub_cats

function main_cat($sub_cat){
    require("conn_posts.php");
    $stmt = $conn_posts->prepare("SELECT `main_cat` FROM `cats` WHERE `ID` = ?");
    $stmt->bind_param("s", $sub_cat);
    $stmt->execute();
    $stmt_results = $stmt->get_result(); // get result

    while($row_get = $stmt_results->fetch_assoc()){
        if($row_get['main_cat'] == 0){
            return $sub_cat;
        }elseif($row_get['main_cat'] !== ""){
            return $row_get['main_cat'];
        }
    }
}

This function gets any category name as long as the id is valid

function cat_name($cat_number){
    require("conn_posts.php");
    $stmt = $conn_posts->prepare("SELECT `cat_name` FROM `cats` WHERE `ID` = ?");
    $stmt->bind_param("s", $cat_number);
    $stmt->execute();
    $stmt_results = $stmt->get_result(); // get result
    $row_get = $stmt_results->fetch_assoc(); 

    if($stmt_results->num_rows <= 0){
        return 0;
    }elseif($stmt_results->num_rows == 1){
        return $row_get['cat_name'];
    }
}

My question is is this a good way to process my posts sub-category and parent category are there better ways of doing what I am currently doing? eg. is my database schema good(by good I mean is it better to just include the parent cat id in the posts table than to do the PHP server-side processing)?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Your database schema is good: it doesn’t include any replication, I wouldn’t change it. The way you’re handling fetching the categories in PHP isn’t really optimal though: you should almost always aim to minimize the number of queries as it (in general) will affect performance more than the complexity of a query.

If you’re running MySQL 8+, a great way to do this is with a recursive CTE; it will allow you to fetch all parents with one query:

WITH RECURSIVE cte AS (
    SELECT id, cat_name, main_cat, 0 as depth FROM categories WHERE ID=3
    UNION ALL
    SELECT categories.id, categories.cat_name, categories.main_cat, cte.depth+1 as depth
    FROM cte inner join categories
    ON cte.main_cat = categories.id
)
SELECT cat_name FROM cte order by depth ASC

The number ‘3’ in that query can be replaced by the category you’re trying to retrieve. You can check this DB fiddle for a live example. If I see your code, incorporating it into your PHP should be fairly trivial. If not, leave a comment and I’ll try to expand.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0