[eluser]TheFuzzy0ne[/eluser]
OK, here we go. First, here's the database I used:
Code: CREATE TABLE IF NOT EXISTS `articles` (
`article_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`created_on` datetime NOT NULL,
`title` varchar(60) NOT NULL,
`content` mediumtext NOT NULL,
PRIMARY KEY (`article_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
INSERT INTO `articles` (`article_id`, `user_id`, `created_on`, `title`, `content`) VALUES
(1, 1, '2013-01-01 00:00:00', 'Article 1', 'User1''s first blog entry.'),
(2, 1, '2013-01-01 00:00:00', 'Article 2', 'User1''s second blog entry.'),
(3, 1, '2013-01-01 00:00:00', 'Article 3', 'User1''s third blog entry.'),
(4, 2, '2013-01-01 00:00:00', 'Article 4', 'User2''s first blog entry.'),
(5, 3, '2013-01-01 00:00:00', 'Article 5', 'User3''s first blog entry.');
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `comments` (
`comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`article_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`created_on` datetime NOT NULL,
`comment` mediumtext NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `article_id` (`article_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
INSERT INTO `comments` (`comment_id`, `article_id`, `user_id`, `created_on`, `comment`) VALUES
(1, 1, 2, '2013-01-01 00:00:00', 'User2''s first comment on User1''s first blog entry.'),
(2, 1, 3, '2013-01-01 00:00:00', 'User3''s first comment on User1''s first blog entry.'),
(3, 1, 4, '2013-01-01 00:00:00', 'User4''s first comment on User1''s first blog entry.'),
(4, 1, 3, '2013-01-01 00:00:00', 'User3''s second comment on User1''s first blog entry.'),
(5, 2, 4, '2013-01-01 00:00:00', 'User4''s first comment on User1''s second blog entry.'),
(6, 2, 3, '2013-01-01 00:00:00', 'User3''s first comment on User1''s second blog entry.'),
(7, 3, 4, '2013-01-01 00:00:00', 'User4''s first comment on User1''s third blog entry.'),
(8, 3, 5, '2013-01-01 00:00:00', 'User5''s first comment on User1''s third blog entry.'),
(9, 3, 4, '2013-01-01 00:00:00', 'User4''s second comment on User1''s third blog entry.'),
(10, 4, 1, '2013-01-01 00:00:00', 'User1''s first comment on User2''s first blog entry.');
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(120) NOT NULL,
PRIMARY KEY (`user_id`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `users` (`user_id`, `username`, `email`) VALUES
(1, 'user1', '[email protected]'),
(2, 'user2', '[email protected]'),
(3, 'user3', '[email protected]'),
(4, 'user4', '[email protected]');
Now, in the interest of doing things properly, I've put your business logic into a model. This makes it easier to re-use the API in other parts of your site.
./application/models/blog_m.php Code: <?php defined('BASEPATH') or ('No direct script access allowed.');
class Blog_m extends CI_Model {
function __construct()
{
parent::__construct();
$this->load->database();
}
function index()
{
// Grab the data from the database.
$result = $this->db
->select(array(
'a.article_id as a_id',
'a.user_id AS a_user_id',
'a.content AS a_content',
'a.title AS a_title',
'a.created_on as a_created_on',
'au.username AS a_username',
'c.comment_id AS c_comment_id',
'c.comment AS c_comment',
'c.user_id AS c_user_id',
'cu.username AS c_username',
'c.created_on AS c_created_on'
))
->from('articles AS a')
->join('comments AS c', 'a.article_id = c.article_id', 'left')
->join('users AS au', 'au.user_id = a.user_id', 'left')
->join('users AS cu', 'cu.user_id = c.user_id', 'left')
->get()->result_array();
// Do we have a result?
if ( ! $result)
{
return FALSE;
}
// Now we need to format the array for the view.
$ret = array();
foreach ($result as &$res)
{
$article_id = $res['a_id'];
// If the article hasn't been added to the return array, add it now.
if ( ! isset($ret[$article_id]))
{
$ret[$article_id] = array(
'a_id' => $article_id,
'a_user_id' => $res['a_user_id'],
'a_username' => $res['a_username'],
'a_created_on' => $res['a_created_on'],
'a_title' => $res['a_title'],
'a_content' => $res['a_content'],
'a_comments' => array(),
);
}
// If we have a comment, add it to the article.
if ($res['c_comment'])
{
$ret[$article_id]['comments'][] = array(
'c_id' => $res['c_comment_id'],
'c_user_id' => $res['c_user_id'],
'c_username' => $res['c_username'],
'c_created_on' => $res['a_created_on'],
'c_comment' => $res['c_comment'],
);
}
}
// Free up some memory.
unset($result);
// Return the array we just built.
return $ret;
}
}
|
