CODEWITHSUNDEEP
phpmysqlsqldatabase-design
Rohitink
Rohitink

Reputation: 1174

A Better Way to Implement Mult. Categories For 1 Post in PHP?

Assume, I have the Following Tables in my Database.

POSTS:
id, title, content

CATEGORIES:
id, name

RELATION:
post_id, cat_id

I have been succesfully able to insert the values into these tables, while publishing the post.

I am also, able to display the categories beneath each and every post on my Homepage. But, I Fear the method I am using is very resource intensive.

Here is What I Do in MySQL/PHP.

(You can choose to skip the code, and read its description for better understanding)

//Get all the posts
$database = $connection->prepare("Select * FROM POSTS"); 
$database->execute(array()); 
$result = $database->fetchAll(PDO::FETCH_ASSOC);


foreach ($result as $r) {
        $articleid = $r['id'];

//Display Post title and other things here


//Get the Category Ids
$database = $connection->prepare("Select * FROM RELATION WHERE post_id = ".$article);   
$database->execute(array());
$ret_cat= $database->fetchAll(PDO::FETCH_ASSOC);

//Get names of category, and dislay them
    foreach($ret_cat as $rc)
                {
                    $catid = $rc['cat_id'];
                    $database = $connection->prepare("Select * FROM CATEGORIES WHERE cat_id= ".$catid); 
    $database->execute(array()); 
    $ret_cat_name= $database->fetch(PDO::FETCH_ASSOC);
                    echo "<a href='index.php?category=".$rc['cat_id']."'>".$ret_cat_name['name']."</a> ";
                }
    }//End First ForEach
  1. Select All the the Desired Posts from the POSTS table.
  2. Use a Foreach loop to display each post.
  3. Within the foreach loop, I use another SELECT statement to get the categories from RELATION table by matching the POSTS.id.
  4. Then, I Use another foreach loop to display all the categories.

But, since I have only the category id in the RELATION table and I need to display the name of the Category instead.

  1. So, I use another SELECT statement to get the name of the Category from the CATEGORIES table, by using RELATION.cat_id.

The Code Works Fine, and I get what I want. But, I feel there are a lot of mysql requests being generated. Hence, load would be increased on server.

So, I need to know what I am doing is right or not. Or, if there is a simpler way to do all this?

Upvotes: 0

Views: 124

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Why not use JOIN to decrease the number of database round-trips?

For example, first select the desired posts...

SELECT * FROM POSTS WHERE <some filter criteria for posts>

...and then for each returned row...

SELECT CATEGORIES.*
FROM RELATION JOIN CATEGORIES
    ON RELATION.cat_id = CATEGORIES.id
WHERE
    post_id = <value from the first query>

Or even do everything in a single round-trip...

SELECT POSTS.*, cat_id, name
FROM POSTS
    JOIN RELATION
        ON POSTS.id = RELATION.post_id
    JOIN CATEGORIES
        ON RELATION.cat_id = CATEGORIES.id
WHERE
    <some filter criteria for posts>

...in which case the client code would need to "merge" the resulting rows that have the same POSTS.id.

BTW, you should almost never do something like...

Select * FROM POSTS

...without a WHERE clause. This simply won't scale as the database grows.

Upvotes: 2

Related Questions