CODEWITHSUNDEEP
phpmysqlsqldatabase
user10426000
user10426000

Reputation:

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.

Upvotes: 2

Views: 70

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 1

Guy Louzon
Guy Louzon

Reputation: 1213

Your answer is a bit broad Regarding your specific implementation, I would merge the categories queries, generate an output and echo it once

<?php
    $posts = DB::get("SELECT * FROM `posts` LIMIT 5");
$output = '';
    foreach ($posts as $post) {
     $categories = DB::get("SELECT c.* FROM `post_categories` pc INNER JOIN categories c ON c.id = pc.category_id WHERE id = " . $post["id"]);

        $output .= $post["title"];

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

        $output .=  $post["content"];
    }

echo $output;
?>

If you'd want some more order, I'd recommend to work with a framework, that will help you put things in order and maybe read a about MVC

Upvotes: 0

Related Questions