[eluser]Lee.[/eluser]
I'm a beginner to PHP and i'm building a little script just to learn a little, using code igniter.
I have the following function;
Code: public function index()
{
$this->db->select('*');
$this->db->from('articles');
$this->db->join('comments', 'comments.article_id = articles.id');
$query = $this->db->get();
$data = array(
'article_entries' => $query->result_array(),
);
$this->load->view('header');
$this->parser->parse('index_page', $data);
$this->load->view('footer');
}
and the following template;
Code: {article_entries}
<h1>{title}</h1>
<h1>{subtitle}</h1>
<p>{content}</p>
<hr />
{comment} by {username}
{/article_entries}
What happens is, if the article has no comments nothing is selected. But if the article has more than one comment, it is selected over and over again. No doubt i'm making a very silly mistake here, but I was under the impression what I was doing is selecting the articles and then the comments that relate to each article. What actually appears to be happening is i'm selecting the article over and over again for each comment.
What I want to do is select all the articles, then if there is any comments - display those too.
Any help for a new comer to PHP?
[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums...and the awesome world PHP!
The problem you're having is due to the structure of your data.
First of all, you should use a LEFT JOIN. Code: $this->db->join('comments', 'comments.article_id = articles.id');
What you have at the moment is an INNER JOIN, which means that any articles that don't have any comments will be excluded from the result set.
Ideally, you need one of set of data for each article, and within each of those, an array of comments. So the array you're passing into the view might look something like this:
Code: // I'm aware that you'll actually have more fields than I've specified.
Array
(
[0] => Array
(
[article_id] => 1
[title] => Title 1
[content] => foo
[comments] => Array
(
[0] => Array
(
[comment_id] => 1
[user_id] => 1
[comment] => Cool blog!
)
[1] => Array
(
[comment_id] => 2
[user_id] => 2
[comment] => Yeah, man. It's AWZM! LOL KK TTFN!
)
)
)
[1] => Array
(
[article_id] => 2
[title] => Title 2
[content] => bar
[comments] => Array
(
[0] => Array
(
[comment_id] => 3
[user_id] => 1
[comment] => This ain't as good as your last blog!
)
[1] => Array
(
[comment_id] => 4
[user_id] => 2
[comment] => Yeah, man. It SUX! Mmmkaaaay.
)
)
)
)
Basically, you're going to need to loop through the database results first, to put it into a nicer format for your view. Then in your view, you'd do something like this:
Code: {article_entries}
<h1>{title}</h1>
<p>{content}</p>
<hr />
{comments}
{comment} by {user_id}
{/comments}
{/article_entries}
I've changed a few values, because username won't actually be available. For that, you'll need to join your users table onto your articles table and also your comments table.
I have a dev helper with the following function:
Code: function dprint_r($arg)
{
die('<pre>' . print_r($arg, TRUE) . '</pre>');
}
Once the helper has been loaded loaded, you can call it like this:
Code: dprint_r($some_arg);
and it will halt execution, and dump data on the supplied object in an easy to read format. It comes in handy for inspecting data to see how it looks.
I hope I haven't confused you too much. If you have any questions, please feel free to ask.
[eluser]Lee.[/eluser]
I understand what you are saying with storing the data in an array as you have done, but in that example you have used real values whereas mine will come from a database.
Would it be possible to show me an example using the following tables and fields?
articles
Article_Id
Title
Subtitle
Username
User_id
Content
comments
Comment_id
Article_id
Comment
Username
User_id
Date
And associate the comments with their respective article as stored in article_id...
Thanks for any help..
[eluser]TheFuzzy0ne[/eluser]
Please could you also show me the columns in your users table? I assume you have one.
You can safely remove the Username column from both tables, since that data will come from the users table, so long as we have a user ID. If you don't have one, you definitely should, otherwise you are duplicating data in your database, as if a user changed their username, you'd have to update the username columns in both the articles and comments tables, and any other tables where it was stored, which is messy.
You may have to wait for a working example (no more than a day), as I'm extremely busy at the moment.
[eluser]Lee.[/eluser]
I don't have a users table yet - that's coming next.
But you are right, with the editing of usernames. Just use user I'd for not and assume the user table is as follows;
User_id
Username
Password
Email
Just for now and ill set it up like that now 
After I have this working I'm going to work on a login engine, but for now i just want to get articles displayed.
Thanks!
[eluser]TheFuzzy0ne[/eluser]
No worries. It just makes more sense to start as you mean to go on, than do it the wrong way, only to have to change it. For now you we can fill the users table with dummy data, which will do nicely.
As soon as I can spare 20 minutes, I'll throw something together for you.
[eluser]Lee.[/eluser]
Thanks, appreciated!
[eluser]Lee.[/eluser]
Hi there, I have followed the tutorial you linked and have that working as I want.
So now I have a form to submit the data and a index page to display an overview and auto generated page for each article.
But I still need to link the comments to the articles, so any advice would be appreciated still.
Thanks!
[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;
}
}
|
