3

I am searching all around the internet but I can't seem to find a simple tutorial that can help me with my custom CMS I am building.

I'd like to add multiple categories to my posts.

For example:

Title: post 1 
Content: content goes here 
Categories: Technology, Computers, Science, Internet

Title: post 2 
Content: content goes here 
Categories: Music, Jazz, Classic

So this is what I have to post my articles

My database structure

postID | postTitle | postDesc | postCont | postDate


1   Post 1      info1   content1    date1
2   Post 2      info2   content2    date2
3   Post 3      info2   content3    date3

The FORM

<form action='' method='post'>

<p><label>Title</label><br />
<input type='text' name='postTitle' value='<?php if(isset($error)){ echo $_POST['postTitle'];}?>'></p>

<p><label>Description</label><br />
<textarea name='postDesc' cols='60' rows='10'><?php if(isset($error)){ echo $_POST['postDesc'];}?></textarea></p>

<p><label>Content</label><br />
<textarea name='postCont' cols='60' rows='10'><?php if(isset($error)){ echo $_POST['postCont'];}?></textarea></p>

<p><input type='submit' name='submit' value='Submit'></p>

</form>

the PHP code to place it in the database

//insert into database
$stmt = $db->prepare('INSERT INTO blog_posts (postTitle,postDesc,postCont,postDate) VALUES (:postImage, :postTitle, :postDesc, :postCont, :postDate)') ;
$stmt->execute(array(
':postTitle' => $postTitle,
':postDesc' => $postDesc,
':postCont' => $postCont,
':postDate' => date('Y-m-d H:i:s')
));

//redirect to index page
header('Location: index.php?action=added');
exit;
3
  • I think you are looking for database structure,it depends on your requirements. But I am writing what I guess from your requirements. post_tbl it contains all post related data. category_tbl it contains all categories with auto_increment ids, third post_cat_rel_tbl it contains post id with category id. it may contain multiple rows for single post if there are more than one category. this is one kind of structure. You can modify according to your requirements. Commented Jun 21, 2014 at 16:01
  • thanks for your reply, my php knowledge isn't that great. How can i join the tables? Commented Jun 21, 2014 at 16:27
  • It's big topic. Can't tell here, see these links, mysqltutorial.org/mysql-inner-join.aspx , stackoverflow.com/questions/14673338/… and try google Commented Jun 21, 2014 at 16:34

1 Answer 1

5

Here is an example of the basic principle of Many to Many Relationships that is suitable for this task. A many to many relationship is a design pattern between two tables when one or more rows in one table may be associated with more than one row in the another table. The relationship between two tables is stored in a third table that stores the primary keys of each relationship ( postID and categoryID columns ).

Examples of database tables:

post

postID | postTitle | postDesc | postCont | postDate

1    Post 1    info1    content1    date1
2    Post 2    info2    content2    date2
3    Post 3    info2    content3    date3

post_category

postID | categoryID

1    1
2    5
3    1
2    6
3    3
2    7
1    2
1    3
1    4

category

categoryID | categoryTitle

1    Technology
2    Computers
3    Science
4    Internet
5    Music
6    Jazz
7    Classic

Examples of MySQL queries:

MySQL query to get all categories related to post

SELECT * FROM `category` NATURAL JOIN `post_category` WHERE postID=1

categoryID | categoryTitle | postID 

1    Technology    1
2    Computers     1
3    Science       1
4    Internet      1

MySQL query for all posts of the category

SELECT * FROM `post` NATURAL JOIN `post_category` WHERE categoryID=1

postID | postTitle | postDesc | postCont | postDate | categoryID 

1    Post 1    info1    content1    date1    1
3    Post 3    info2    content3    date3    1
Sign up to request clarification or add additional context in comments.

1 Comment

Your the man!! thanks i have connected certain categories to certain posts but now maybe i am just being stupid but how do i attach multiple categories to a certain post? i.e. I have posts 1 and it has category Music (5) and category Jazz (6) and also category Classic (7) How do i do that? Thanks for the help so far!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.