Displaying data from from many-to-many table

Question

The goal is to display all posts with their categories underneath them. The code below accomplishes this, however I'm thinking it's bad and was hoping someone could review and offer some feedback.

Because each post can have many categories and because each category can belong to many posts, I used a many-to-many table.

I have three tables:

• table 1: Posts
  columns: | id | post_name | post_text |

• table 2: categories
  columns: | id | category_name |

• table 3: cats_to_posts
  columns: | cats_id | post_id |

index.php:

$posts = Posts::read_all();

foreach ( $posts as $post )
{
    echo $post->post_name ."<br>";
    echo $post->post_text ."<br>";

    $categories_array = Posts::get_cats_by_post_id($post->id);

    foreach ($categories_array as $category)
    {
        echo "&bull;". $category->category_name ."<br>";
    }
}

Posts class:

public static function get_cats_by_post_id($id)
{
    $db = new Database();

    $sql = "SELECT  cats_to_posts.cats_id
            FROM    cats_to_posts
            WHERE   cats_to_posts.post_id = {$id}";

    $result = $db->exe_query($sql);

    $cat_id_array = array(); // stores array of category ids that match post ids

    while ( $record = $result->fetch_object() )
    {
        $cat_id_array[] = $record;
    }

    $cat_name_array = array(); // stores array of category names that match category ids
    foreach($cat_id_array as $cat_id)
    {
        $new_sql = "SELECT  category_name
                    FROM    categories
                    WHERE   id = ". $cat_id->cats_id;

        $new_result = $db->exe_query($new_sql);

        while ( $new_record = $new_result->fetch_object() )
        {
            $cat_name_array[] = $new_record;
        }

    }

    return $cat_name_array;
}

My main curiosity is on the get_cats_by_post_id() method inside the Posts class. Is there is a better way to get all category names based on the post_id than using two SQL commands like I am doing?

Answer 1

By the looks of it, you

• get the category IDs using the post ID
• get the categories according to the category IDs you just had

Well, you can use JOINS to bridge the tables:

SELECT c.category_name       //select category name
FROM cats_to_posts cp        //from cats_to_posts
    INNER JOIN categories c  //joined with categories
        ON cp.cats_id=c.id   //where the cats_to_posts id  = category id
WHERE cp.post_id = {$id}     //get for post with ID

This single query should return an array of all category names for that certain post ID. The INNER JOIN joins the cats_to_posts table with categories table and returns only those categories that have an equivalent ID in cats_to_posts