xVFbWVcY

1Show proper categories for blogposts
2CREATE TABLE IF NOT EXISTS `blogposts` (
3  `id` int(10) NOT NULL AUTO_INCREMENT,
4  `id_user_created` int(10) DEFAULT '0',
5  `id_user_edited` int(10) DEFAULT '0',
6  `post_subject` varchar(100) NOT NULL,
7  `post_message` text NOT NULL,
8  `post_categories` text NOT NULL,
9  `date_published` datetime NOT NULL,
10  `date_edited` datetime NOT NULL,
11  `info_ipaddress_created` text NOT NULL,
12  `info_ipaddress_edited` text NOT NULL,
13  `is_shared` enum('0','1') DEFAULT '0',
14  PRIMARY KEY (`id`),
15  UNIQUE KEY `id` (`id`)
16)
17
18
19CREATE TABLE IF NOT EXISTS `categories` (
20  `id` int(10) NOT NULL AUTO_INCREMENT,
21  `id_user` int(10) DEFAULT '0',
22  `post_name` text NOT NULL,
23  `date_added` datetime NOT NULL,
24  `date_edited` datetime NOT NULL,
25  `info_ipaddress` text NOT NULL,
26  PRIMARY KEY (`id`),
27  UNIQUE KEY `id` (`id`)
28)
29
30
31$get_categories = "SELECT * FROM blogposts, categories
32                   WHERE blogposts.id = '".(int)$blog['id']."'
33                   AND blogposts.post_categories = categories.id
34                  ";
35
36foreach($sql->query($get_categories) AS $category) {
37    echo '<a href="'.url('blog/category/'.$category['post_name']).'" class="grey-link">';
38        echo $category['post_name'];
39    echo '</a>';
40}
41
42
43INSERT INTO blogposts` (id, id_user_created, id_user_edited, post_subject, post_message, post_categories, date_published, date_edited, info_ipaddress_created, info_ipaddress_edited, is_shared)
44VALUES (1, 1, 1, 'test', 'testar', '1', '', '', '', '', '0'),
45(2, 1, 1, 'med kategorier', 'wiho!', '1|2', '', '', '', '', '0');
46
47INSERT INTO categories` (id, id_user, post_name, date_added, date_edited, info_ipaddress)
48VALUES (1, 1, 'test', '', '', ''),
49(2, 1, 'test2', '', '', '');
50	
51CREATE TABLE IF NOT EXISTS `blogposts` ( 
52  `id` int(10) NOT NULL AUTO_INCREMENT, 
53  `post_message` text NOT NULL,
54  PRIMARY KEY (`id`), 
55  UNIQUE KEY `id` (`id`) 
56) 
57
58CREATE TABLE IF NOT EXISTS `categories` ( 
59  `id` int(10) NOT NULL AUTO_INCREMENT, 
60  `category_name` text NOT NULL, 
61  PRIMARY KEY (`id`), 
62  UNIQUE KEY `id` (`id`) 
63) 
64
65CREATE TABLE IF NOT EXISTS `post_categories` ( 
66  `post_id` int(10) NOT NULL AUTO_INCREMENT, 
67  `category_id` text NOT NULL
68)
69	
70SELECT * 
71FROM blogposts b
72INNER JOIN post_categories pc ON pc.post_id=p.id
73INNER JOIN categories c ON c.id=pc.category_id
74WHERE b.id=<yourvariable>