0
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ 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 \$\endgroup\$
    – kdub
    May 6 '12 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.