|
0
|
1 |
SELECT f.forum_id,f.forum_type,f.forum_name,f.forum_desc,
|
|
|
2 |
COUNT(t.topic_id) AS num_topics, COUNT(p.post_id) AS num_posts,
|
|
|
3 |
p.post_id,t.topic_id,t.topic_title,u.username,u.user_level,p.timestamp FROM decir_forums AS f
|
|
|
4 |
LEFT JOIN decir_topics AS t
|
|
|
5 |
ON (t.forum_id=f.forum_id)
|
|
|
6 |
LEFT JOIN decir_posts AS p
|
|
|
7 |
ON (p.topic_id=t.topic_id)
|
|
|
8 |
LEFT JOIN users AS u
|
|
|
9 |
ON (u.user_id=f.last_post_user)
|
|
|
10 |
WHERE ( t.topic_id=f.last_post_topic AND p.post_id=f.last_post_id ) OR ( f.last_post_topic IS NULL AND f.last_post_id IS NULL )
|
|
|
11 |
GROUP BY f.parent,f.forum_id
|
|
|
12 |
ORDER BY f.forum_order;
|
|
|
13 |
|
|
|
14 |
SELECT COUNT(t.topic_id) AS num_topics, COUNT(p.post_id) AS num_posts FROM decir_forums AS f
|
|
|
15 |
LEFT JOIN decir_topics AS t
|
|
|
16 |
ON (t.forum_id=f.forum_id)
|
|
|
17 |
LEFT JOIN decir_posts AS p
|
|
|
18 |
ON (p.topic_id=t.topic_id)
|
|
|
19 |
GROUP BY f.forum_id
|
|
|
20 |
ORDER BY f.forum_order;
|
|
|
21 |
|
|
|
22 |
INSERT INTO decir_forums(forum_id,forum_type,forum_name,forum_order) VALUES(1,2,'Test category',1);
|
|
|
23 |
INSERT INTO decir_forums(forum_id,forum_type,forum_name,forum_desc,parent,forum_order,last_post_id,last_post_topic,last_post_user) VALUES(3,1,'Test forum 1','This is just a test forum.',1,2,3,3,2);
|
|
|
24 |
INSERT INTO decir_topics(topic_id,forum_id,topic_title,topic_icon,topic_starter,timestamp) VALUES(1,3,'Test topic 1',1,2,UNIX_TIMESTAMP());
|
|
|
25 |
INSERT INTO decir_posts(post_id,topic_id,poster_id,poster_name,timestamp) VALUES(1,1,2,'Dan',UNIX_TIMESTAMP());
|
|
|
26 |
INSERT INTO decir_posts_text(post_id,post_text,bbcode_uid) VALUES(1,'This post was created manually using SQL queries.
|
|
|
27 |
It is nothing more than a [b:0123456789]proof of concept[/b:0123456789]!
|
|
|
28 |
|
|
|
29 |
-Dan','0123456789');
|
|
|
30 |
INSERT INTO decir_forums(forum_id,forum_type,forum_name,forum_desc,parent,forum_order,last_post_id,last_post_topic,last_post_user) VALUES(4,1,'Test forum 2','This is just a test forum.',1,3,2,2,2);
|
|
|
31 |
INSERT INTO decir_topics(topic_id,forum_id,topic_title,topic_icon,topic_starter,timestamp) VALUES(2,4,'Test topic 2',1,2,UNIX_TIMESTAMP());
|
|
|
32 |
INSERT INTO decir_posts(post_id,topic_id,poster_id,poster_name,timestamp) VALUES(2,2,2,'Dan',UNIX_TIMESTAMP());
|
|
|
33 |
INSERT INTO decir_posts_text(post_id,post_text,bbcode_uid) VALUES(2,'This post was created manually using SQL queries.
|
|
|
34 |
It is nothing more than a [b:0123456789]proof of concept[/b:0123456789]!
|
|
|
35 |
|
|
|
36 |
-Dan','0123456789');
|
|
|
37 |
INSERT INTO decir_topics(topic_id,forum_id,topic_title,topic_icon,topic_starter,timestamp) VALUES(3,3,'Test topic 3',1,2,UNIX_TIMESTAMP());
|
|
|
38 |
INSERT INTO decir_posts(post_id,topic_id,poster_id,poster_name,timestamp) VALUES(3,3,2,'Dan',UNIX_TIMESTAMP());
|
|
|
39 |
INSERT INTO decir_posts_text(post_id,post_text,bbcode_uid) VALUES(3,'This post was created manually using SQL queries.
|
|
|
40 |
It is nothing more than a [b:0123456789]proof of concept[/b:0123456789]!
|
|
|
41 |
|
|
|
42 |
-Dan','0123456789');
|
|
|
43 |
|