+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How to get categories for posts? (/showthread.php?tid=51955)
How to get categories for posts? - El Forum - 05-24-2012
[eluser]Unknown[/eluser]
Hi.
I have general page that contain 10 posts for each post can be 0 or many categories.
How I can show categories for each post?
Here my tables
Code:
CREATE TABLE IF NOT EXISTS `articles` (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
descr VARCHAR(255) NOT NULL,
alt_title VARCHAR(255) NOT NULL,
short_story TEXT NOT NULL,
full_story TEXT NOT NULL,
`date` DATETIME NOT NULL,
category varchar NOT NULL,
PRIMARY KEY (id),
FULLTEXT INDEX index_search (short_story, full_story),
INDEX index_title (title)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `categories` (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
category VARCHAR(255) NOT NULL,
alt_category VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;I want show 10 posts on page.
First I get 10 entries from table `articles`.
In artciles entry category can have '1|2' - that id's table categories.
Can you show me example function that can get all categories for each post.
I don't want use foreach for every post. Couse I think it very slow way.
How to get categories for posts? - El Forum - 05-24-2012
[eluser]luismartin[/eluser]
This question would fit much more in a SQL forum. Anyway I see something that could be wrong: the foreign key in table 'articles' for table 'categories'. You seem to be using a varchar which is not an index in 'categories' to join them.
Also, if that's a many-to-many relationship, as it seems to be (an article has 0, 1 or more categories and a category has 0 to many articles), you need an intermediate table.
Then having these three tables: articles, articles-categories, categories, you'll want to do a left join from articles to articles-categories, then an inner join from articles-categories to categories.