Best way to display multiple categories next to posts

LearnToCode · July 31, 2014, 8:09pm

Hello everybody.

I’m trying to figure out the best approach to display multiple categories next to my posts.
Currently I have a very simple list with just a title and a small content area.
The way it works is like a blog someone clicks on the title and they’ll will be taken to the full content.
But i want to display linked categories next to each listed post.

like this:


TITLE 1 POSTED IN: <a href="">IMAGES</a>
SMALL CONTENT INTRO

TITLE 2 POSTED IN: <a href="">BOOKS</a>, <a href="">COVERS</a>
SMALL CONTENT INTRO

TITLE 3 POSTED IN: <a href="">GAMING</a>, <a href="">RETRO</a>, <a href="">VIRTUAL-CONSOLE</a>
SMALL CONTENT INTRO

PAGE 1 - 2 - 3

So I figured I should store the data in my database like this:
PostTable
postID
postTitle
postContent

CategoriesTable
catID
catName
catHref

CategoriesPostRelationTable
postID
catID

So here comes my question and i hope i’m asking this in the right place.
But how do I display multiple linked categories next to my posts?

I tried this:


SELECT p.postID, p.postTitle, p.postContent, GROUP_CONCAT(catName) AS categories
FROM PostTable AS p
LEFT JOIN CategoriesPostRelationTable AS cp ON p.postID = cp.postID
LEFT JOIN CategoriesTable AS c ON cp.catID = c.catID
GROUP BY p.postID

But the problem is I need linked categories.
How can add in the links?
So I can echo it out in php like this:


<?php
echo '<a href="'. $row['catHref'] . '">' . $row['catName'] . '</a>';
?>

Is there a approach without the use of GROUP_CONCAT?
Mabey using two queries with php I really don’t know?

Basically i’m trying to find the best method to get the result i want and i want to make sure it’s not going to slowdown the website.
Thanks for the help.

r937 · August 1, 2014, 1:22am


SELECT p.postID
     , p.postTitle
     , p.postContent
     , GROUP_CONCAT( CONCAT(c.catHref
                          , '-'
                          , c.catName) 
                     SEPARATOR ';'
                    ) AS categories
  FROM PostTable AS p
LEFT OUTER
  JOIN CategoriesPostRelationTable AS cp 
    ON cp.postID = p.postID
LEFT OUTER
  JOIN CategoriesTable AS c 
    ON c.catID = cp.catID
GROUP 
    BY p.postID

i’m sure you will know how to discombobulate this in php

LearnToCode · August 1, 2014, 3:32pm

First of all thanks for the help r937,
but i’m not exactly sure how to split up the links with php efficiently.

I know about the explode function in php but i’m not quite sure if thats the correct way to do this sins I need to split the categories and at the same time I also need to extract the hyperlink part en name part of each categorie.
Could you give me some tips how to do this?

r937 · August 1, 2014, 7:05pm

please hit the little orange flag on one of your posts and request that the thread be moved to the php forum

it would be a good idea to post a couple of examples of the entire “categories” string from your query so that the php folks can see the two different separators at work

DaveMaxwell · August 1, 2014, 7:10pm

Moved per OP request…

Drummin · August 1, 2014, 11:28pm

Some things are not clear to me.
Are you creating a new Category with each new POST? Seems the only place you are storing catHref links is in that categories table.
What kind of links are you storing in catHref? For example are we talking directories like BOOKS would be “/books” and COVERS be “/books/covers”?
If there are actual articles then would they then be shown with GET[‘id’]? Example: /books/covers/index.php?id=26

Maybe attaching an sql file containing a sample of your tables with data and an sample of what a link should look like would help clarify things.

LearnToCode · August 2, 2014, 9:45am

Here is my sql


CREATE TABLE IF NOT EXISTS `categoriespostrelationtable` (
  `postID` int(10) unsigned NOT NULL,
  `catID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`postID`,`catID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categoriespostrelationtable` (`postID`, `catID`) VALUES
(1, 2),
(2, 2),
(2, 3);


CREATE TABLE IF NOT EXISTS `categoriestable` (
  `catID` int(11) NOT NULL AUTO_INCREMENT,
  `catName` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `catPath` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`catID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4;

INSERT INTO `categoriestable` (`catID`, `catName`, `catPath`) VALUES
(1, 'Uncategorized', 'uncategorized/'),
(2, 'Books', 'books/'),
(3, 'Paperback', 'paperback/');


CREATE TABLE IF NOT EXISTS `posttable` (
  `postID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `postTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `postContent` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`postID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

INSERT INTO `posttable` (`postID`, `postTitle`, `postContent`) VALUES
(1, 'Unbroken: A World War II Story of Survival', 0x556e62726f6b656e3a204120576f726c64205761722049492053746f7279206f6620537572766976616c2c20526573696c69656e63652c20616e6420526564656d7074696f6e2050617065726261636b20e28093204a756c792032392c20323031340d0a0d0a2331204e455720594f524b2054494d4553204245535453454c4c455220e280a220534f4f4e20544f2042452041204d414a4f52204d4f54494f4e205049435455524520e280a2204c6f6f6b20666f72207370656369616c20666561747572657320696e736964652e204a6f696e207468652052616e646f6d20486f75736520526561646572e280997320436972636c6520666f7220617574686f7220636861747320616e64206d6f72652e),
(2, 'The History of Nintendo 1980-1991 SC', 0x546865207365636f6e6420766f6c756d65206f662054686520486973746f7279206f66204e696e74656e646f2069732064656469636174656420656e746972656c7920746f2074686520666162756c6f75732047616d6520262057617463682067616d65732c20612066616d6f7573206c696e65206f6620656c656374726f6e6963204c43442067616d657320776869636820776173207665727920706f70756c617220696e20746865206561726c7920383027732e20596f75276c6c2066696e64207468652064657461696c656420616e64206578636c7573697665206368726f6e69636c6573206f6620686f77207468697320696e76656e74696f6e2c207768696368207265766f6c7574696f6e697a6564207468652077686f6c6520656e7465727461696e6d656e7420696e6475737472792c2063616d6520746f2062652e20596f75276c6c20616c736f20756e636f7665722074686520646966666963756c7469657320656e636f756e746572656420647572696e6720746865697220646576656c6f706d656e742c20746865207365637265747320626568696e642074686973206e657720636f6e63657074206174207468652074696d652c20616e6420646f7a656e73206f6620756e657870656374656420646f63756d656e74732e);

Note: I changed the catHref column name to catPath

The catPath column stores the directory path i’m planning to use when someone clicks on one of the categories listed next to the posts.
It works like this I have php file named categories.php this grabs all posts in a specific category.
With a $_GET[‘cat’]; which uses the catPath column.
In the end i’m going to use htaccess to rewrite the urls to make clean website paths like this for example:

website.com/categories.php?cat=books/

becomes:

website.com/categories/books/

I’m not creating new categories on each post, categoriesTable stores all categories i have.
categoriesPostRelationTable stores the relation between each post and category.
This way a post can have multiple categories ore just a single one.

My main problem was the SQL, I wanted to display my posts like this:


&lt;div class="title"&gt;Unbroken: A World War II Story of Survival&lt;/div&gt;

Unbroken: A World War II Story of Survival, Resilience, and Redemption Paperback – July 29, 2014
#1 NEW YORK TIMES BESTSELLER • SOON TO BE A MAJOR MOTION PICTURE • Look for ... READ MORE

Posted in &lt;a href="categories.php?cat=books/"&gt;Books&lt;/a&gt;


&lt;div class="title"&gt;The History of Nintendo 1980-1991 SC&lt;/div&gt;

The second volume of The History of Nintendo is dedicated entirely to the fabulous Game & Watch games, a famous line of electronic LCD games which ... READ MORE

Posted in &lt;a href="categories.php?cat=books/"&gt;Books&lt;/a&gt;, &lt;a href="categories.php?cat=paperback/"&gt;Paperback&lt;/a&gt;

I had trouble figuring out how to select multiple categories names + the paths associated to multiple posts.
I know how to grab all categories in a single post but I didn’t know how to do it with multiple posts
r937 here on sitepoint helped me with the query and came up with the GROUP_CONCAT + CONCAT.
This works so now my query to select all my data looks like this:


SELECT p.* GROUP_CONCAT( CONCAT(c.catPath,'.',c.catName) SEPARATOR ';') AS categories
	FROM posttable p
	LEFT JOIN categoriespostrelationtable cp ON p.postID = cp.postID
	LEFT JOIN categoriestable c ON cp.catID = c.catID
	GROUP BY p.postID
	ORDER BY p.postTitle DESC

So the data I now currently have is this:


Array
(
    [1] =&gt; Array
        (
            [postID] =&gt; 1
            [postTitle] =&gt; Unbroken: A World War II Story of Survival
            [postContent] =&gt; Unbroken: A World War II Story of Survival, Resilience, and Redemption Paperback – July 29, 2014

#1 NEW YORK TIMES BESTSELLER • SOON TO BE A MAJOR MOTION PICTURE • Look for special features inside. Join the Random House Reader’s Circle for author chats and more.
            [categories] =&gt; books/.Books
        )

    [2] =&gt; Array
		(
            [postID] =&gt; 2
            [postTitle] =&gt; The History of Nintendo 1980-1991 SC
            [postContent] =&gt; The second volume of The History of Nintendo is dedicated entirely to the fabulous Game & Watch games, a famous line of electronic LCD games which was very popular in the early 80's. You'll find the detailed and exclusive chronicles of how this invention, which revolutionized the whole entertainment industry, came to be. You'll also uncover the difficulties encountered during their development, the secrets behind this new concept at the time, and dozens of unexpected documents.
	    [categories] =&gt; books/.Books;paperback/.Paperback
        )
)

Now i’m trying to figure out a way with PHP to separate the category data so i’m able to echo them out as links.
As you can see in the array currently all category names + paths are beeing appended into a single string.
So my question now is how can I separate the categories and extract the path parts so i can display them like links.
Hope everything is a bit clear.

Drummin · August 2, 2014, 6:34pm

I believe this should do it.

<?php
	$sql = "SELECT p.postID
	     , p.postTitle
	     , p.postContent
	     , GROUP_CONCAT( CONCAT(c.catPath
	                          , '-'
	                          , c.catName)
	                    ) AS categories
	  FROM PostTable AS p
	LEFT OUTER
	  JOIN CategoriesPostRelationTable AS cp 
	    ON cp.postID = p.postID
	LEFT OUTER
	  JOIN CategoriesTable AS c 
	    ON c.catID = cp.catID
	GROUP 
	    BY p.postID";
		$query = $pdo->prepare($sql);  
	    $query->execute();
		while($row = $query->fetch(PDO::FETCH_ASSOC)){
			$categories = explode(',',$row['categories']);
			
			$links = array();
			foreach($categories as $cat):			
				$part = explode('-',$cat);			
				$links[] = '<a href="categories.php?cat=' . $part[0] . '">' . $part[1] . '</a>';			
			endforeach;
			
			echo '<div class="title">' . $row['postTitle'] . '</div>
			<div class="content">' . substr($row['postContent'],0,175) . ' ...</div>
			<div class="links">READ MORE Posted in ' . implode(', ',$links) . '</div>'."\\r";
		}	
	
?>

LearnToCode · August 6, 2014, 9:41pm

Hello Drummin,
Thanks it worked. Its such a simple solution but I just couldn’t figure it out lol.
Everything is working now and I appreciate all the help from the people here at sitepoint.
Thanks everybody finally I have everything working