## PHP

<?php

function GetPostAndCommentsAsXML($postID, $db)
{
	$xml = new DomDocument;

	$posts = $xml->createElement("posts");
	$xml->appendChild($posts);

	if(!$db) return $xml;

	$result = $db->query(sprintf("call list_post_comments(%d)", $postID));

	if(!$result) return $xml;

	$row = $result->fetch_assoc();

	$post = $xml->createElement("post");

	foreach($row as $col => $val) $post->setAttribute($col, $val);

	$result->free();

	$posts->appendChild($post);

	$db->next_result();
	$result = $db->use_result();

	while($row = $result->fetch_assoc()){

		$comment = $xml->createElement("comment");

		foreach($row as $col => $val) $comment->setAttribute($col, $val);

		if(!is_null($post)) $post->appendChild($comment);
	}
	$result->free();

	return $xml;
}

header("Content-type: text/xml");

$postID = 1;

$db = new Mysqli("localhost", "foo_dbo", "pass", "foo_db");

$xml = GetPostAndCommentsAsXML($postID, $db);

echo $xml->saveXML();

$db->close();

?>

## SQL

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;

drop table if exists posts;
create table posts
(
post_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
created_date datetime not null,
subject varchar(255) not null,
key posts_user_idx(user_id)
)
engine=innodb;


drop table if exists post_comments;
create table post_comments
(
comment_id int unsigned not null auto_increment primary key,
post_id int unsigned not null,
user_id int unsigned not null,
created_date datetime not null,
comment varchar(1024) not null,
key post_comments_posts_idx(post_id),
key post_comments_user_idx(user_id)
)
engine=innodb;

-- VIEWS

drop view if exists posts_view;
create view posts_view as
select
 p.*,
 date_format(p.created_date, '%e-%b-%Y') as created_date_fmt,
 u.username
from
 posts p
inner join users u on p.user_id = u.user_id;


drop view if exists post_comments_view;
create view post_comments_view as
select
 c.*,
 date_format(c.created_date, '%e-%b-%Y - %H:%i') as created_date_fmt,
 p.subject,
 u.username
from
 post_comments c
inner join posts p on c.post_id = p.post_id
inner join users u on c.user_id = u.user_id;


-- STORED PROCEDURES

drop procedure if exists list_post_comments;

delimiter #

create procedure list_post_comments
(
in p_post_id int unsigned
)
proc_main:begin

  select * from posts_view where post_id = p_post_id;

  select * from post_comments_view where post_id = p_post_id;

end proc_main #

delimiter ;

-- TEST DATA

insert into users (username) values ('f00'),('bar'),('alpha'),('beta');

insert into posts (user_id, created_date, subject) values
(1,now() - interval 3 day, 'post 1'),
(2,now() - interval 2 day, 'post 2'),
(3,now() - interval 1 day, 'post 3'),
(4,now(), 'post 4');

insert into post_comments (post_id,user_id,created_date,comment) values
(1,2,now(),'post 1 comment 1'),
(1,3,now(),'post 1 comment 2'),
(1,4,now(),'post 1 comment 3');

-- TESTING

call list_post_comments(1);