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

• kdub: You rock!!! That's exactly what I was looking for. And damn, I actually had almost all of that sequel a couple of hours ago, but didn't have the last line "WHERE cp.post_id = {$id}" correct. thanks