· 8 years ago · Aug 14, 2018, 09:02 AM
1## PHP
2
3<?php
4
5function GetPostAndCommentsAsXML($postID, $db)
6{
7 $xml = new DomDocument;
8
9 $posts = $xml->createElement("posts");
10 $xml->appendChild($posts);
11
12 if(!$db) return $xml;
13
14 $result = $db->query(sprintf("call list_post_comments(%d)", $postID));
15
16 if(!$result) return $xml;
17
18 $row = $result->fetch_assoc();
19
20 $post = $xml->createElement("post");
21
22 foreach($row as $col => $val) $post->setAttribute($col, $val);
23
24 $result->free();
25
26 $posts->appendChild($post);
27
28 $db->next_result();
29 $result = $db->use_result();
30
31 while($row = $result->fetch_assoc()){
32
33 $comment = $xml->createElement("comment");
34
35 foreach($row as $col => $val) $comment->setAttribute($col, $val);
36
37 if(!is_null($post)) $post->appendChild($comment);
38 }
39 $result->free();
40
41 return $xml;
42}
43
44header("Content-type: text/xml");
45
46$postID = 1;
47
48$db = new Mysqli("localhost", "foo_dbo", "pass", "foo_db");
49
50$xml = GetPostAndCommentsAsXML($postID, $db);
51
52echo $xml->saveXML();
53
54$db->close();
55
56?>
57
58## SQL
59
60-- TABLES
61
62drop table if exists users;
63create table users
64(
65user_id int unsigned not null auto_increment primary key,
66username varbinary(32) unique not null
67)
68engine=innodb;
69
70drop table if exists posts;
71create table posts
72(
73post_id int unsigned not null auto_increment primary key,
74user_id int unsigned not null,
75created_date datetime not null,
76subject varchar(255) not null,
77key posts_user_idx(user_id)
78)
79engine=innodb;
80
81
82drop table if exists post_comments;
83create table post_comments
84(
85comment_id int unsigned not null auto_increment primary key,
86post_id int unsigned not null,
87user_id int unsigned not null,
88created_date datetime not null,
89comment varchar(1024) not null,
90key post_comments_posts_idx(post_id),
91key post_comments_user_idx(user_id)
92)
93engine=innodb;
94
95-- VIEWS
96
97drop view if exists posts_view;
98create view posts_view as
99select
100 p.*,
101 date_format(p.created_date, '%e-%b-%Y') as created_date_fmt,
102 u.username
103from
104 posts p
105inner join users u on p.user_id = u.user_id;
106
107
108drop view if exists post_comments_view;
109create view post_comments_view as
110select
111 c.*,
112 date_format(c.created_date, '%e-%b-%Y - %H:%i') as created_date_fmt,
113 p.subject,
114 u.username
115from
116 post_comments c
117inner join posts p on c.post_id = p.post_id
118inner join users u on c.user_id = u.user_id;
119
120
121-- STORED PROCEDURES
122
123drop procedure if exists list_post_comments;
124
125delimiter #
126
127create procedure list_post_comments
128(
129in p_post_id int unsigned
130)
131proc_main:begin
132
133 select * from posts_view where post_id = p_post_id;
134
135 select * from post_comments_view where post_id = p_post_id;
136
137end proc_main #
138
139delimiter ;
140
141-- TEST DATA
142
143insert into users (username) values ('f00'),('bar'),('alpha'),('beta');
144
145insert into posts (user_id, created_date, subject) values
146(1,now() - interval 3 day, 'post 1'),
147(2,now() - interval 2 day, 'post 2'),
148(3,now() - interval 1 day, 'post 3'),
149(4,now(), 'post 4');
150
151insert into post_comments (post_id,user_id,created_date,comment) values
152(1,2,now(),'post 1 comment 1'),
153(1,3,now(),'post 1 comment 2'),
154(1,4,now(),'post 1 comment 3');
155
156-- TESTING
157
158call list_post_comments(1);