PHP Database Query foreign key data from database

I try to build a simple blog system. database table like below.

Posts

+----+----------+-------------+
|id  | title    | content     |
+----+----------+-------------+
|1   | Tile     | Content     |
+----+----------+-------------+
|2   | Tile 2   | Content 2   |
+----+----------+-------------+

Categories

+--------+-------------+
|id      | category    |
+--------+-------------+
|1       | Category1   |
+--------+-------------+
|2       | Category2   |
+--------+-------------+
|3       | Category3   |
+--------+-------------+

Post-Categories

+--------+-------------+
|postID  | categoryID  |
+--------+-------------+
|1       | 1           |
+--------+-------------+
|1       | 2           |
+--------+-------------+
|1       | 3           |
+--------+-------------+
|2       | 2           |
+--------+-------------+
|2       | 3           |
+--------+-------------+

I use this code

<?php
    $posts = DB::get("SELECT * FROM `posts` LIMIT 5");
    foreach ($posts as $post) {
        echo $post["title"];

        $category_ids = DB::get("SELECT categoryID FROM `post_categories` WHERE postID = " . $post["id"]);

        $ids = join(", ", $category_ids);

        $categorys = DB::get("SELECT * FROM `categories` WHERE id IN (" . $ids . ")");

        foreach ($categorys as $category) {
            echo '<a href="...">' . $category["category"] . '</a>';
        }

        echo $post["content"];
    }
?>

I know this is not a good way fetching categories.

Even It query same category over and over and over again.

Can anyone give me some guideline/resources/tutorials to learn PHP logics, OOP, Best Practices. How may I handle this with OOP PHP.

And please also provide your ans for fetching categories.

I don’t want to join the post_categories and posts table to fetching data because there are few more columns in there.

Also there may have multiple category in a single post.

Answer

why you don’t want join .. your code is more complex and time expensive that a single joined query

    SELECT * FROM `posts` LIMIT 5

    SELECT p.content, p.title, c.category  FROM `categories` c 
    INNER JOIN  `post_categories` pc on pc.id = c.id 
    INNER JOIN (
        SELECT id, content, title FROM `posts` LIMIT 5
    ) p on p.id = c.id

Advertisement

Answer