[eluser]eokorie[/eluser]
Hi I hope someone can help. I am trying to get a list of tags for every article posted and I am trying to get these listed underneath each individual post.
This is the SQL that I have in the model:
Code: public function get_all_entries($limit, $offset, $cat='')
{
$this->db->select('*');
$this->db->select("DATE_FORMAT(gk_blog_posts.date_art,'%W, %D %M %Y') AS article_date", FALSE );
$this->db->select("Count(gk_blog_comments.id_com) AS TotalComments");
($cat)? $this->db->where('idtop_art', $cat) : '' ;
$this->db->where('status_art','publish');
// $this->db->where('gk_blog_comments.is_approved','1');
$this->db->join('gk_users', 'gk_users.id_usr = gk_blog_posts.author_art', 'left');
$this->db->join('gk_blog_comments', 'gk_blog_comments.idart_com = gk_blog_posts.id_art', 'left');
$this->db->join('gk_blog_categories', 'gk_blog_categories.id_top = gk_blog_posts.idtop_art', 'left');
$this->db->order_by('date_art','desc');
$this->db->group_by('id_art');
$this->db->limit($limit, $offset);
$articles = $this->db->get()->result;
foreach($articles as $article) {
$this->db->select('*');
$this->db->from('gk_blog_tags');
$this->db->join('gk_blog_posts_tags', 'gk_blog_posts_tags.gk_blog_tag_id = gk_blog_tags.id_tag');
$this->db->where('gk_blog_posts_tags.gk_blog_post_id', $article->id_art);
$article->tags = $this->db->get()->result;
}
return $query->result();
}
Unfortunately, its just not working. My table structure is show below:
Code: CREATE TABLE `gk_blog_posts` (
`id_art` bigint(20) unsigned,
`author_art` bigint(20),
`idtop_art` bigint(20),
`title_art` text,
`desc_art` text,
`content_art` longtext,
`meta_key_art` varchar(255),
`meta_value_art` longtext,
`date_art` datetime,
`date_gmt_art` datetime,
`status_art` enum('publish','private','static','pending'),
`modified_art` datetime,
`modified_gmt_art` datetime,
`comment_status_art` enum('closed','open','registered only'),
`name_art` varchar(255),
`tags_art` varchar(255),
`read_count_art` bigint(20),
`tutorial_art` char(1),
PRIMARY KEY (`id_art`),
KEY `title_art` (`title_art`,`desc_art`,`content_art`)
) ENGINE=MyISAM DEFAULT CHARSET latin1;
CREATE TABLE `gk_blog_posts_tags` (
`post_tag_id` int(11),
`gk_blog_post_id` int(11),
`gk_blog_tag_id` int(11),
PRIMARY KEY (`post_tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET latin1;
CREATE TABLE `gk_blog_tags` (
`id_tag` int(10) unsigned,
`name_tag` varchar(255),
`count_tag` bigint(20),
PRIMARY KEY (`id_tag`)
) ENGINE=MyISAM DEFAULT CHARSET latin1;
Does the SQL I wrote look correct?
Thanks
[eluser]pistolPete[/eluser]
[quote author="eokorie" date="1250196782"]Unfortunately, its just not working. [/quote]
Describe the error / malfunction you are experiencing better, otherwise it's hard to help you!
[eluser]eokorie[/eluser]
silly me, my apologies... If I try to run the query, it just returns a database error. I have doubled checked it to make sure that nothing is wrong. I have even gone as far as removing the foreach loop so as to make sure I am getting a list of articles without the tags and that is listing properly. If I put the for look back in, the database error (1064) returns. The error I get is show below.
Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN gk_users ON gk_users.id_usr = gk_blog_posts.author_art LEFT JOIN gk_bl' at line 2
SELECT *, DATE_FORMAT(gk_blog_posts.date_art, '%W, %D %M %Y') AS article_date, Count(gk_blog_comments.id_com) AS TotalComments LEFT JOIN gk_users ON gk_users.id_usr = gk_blog_posts.author_art LEFT JOIN gk_blog_comments ON gk_blog_comments.idart_com = gk_blog_posts.id_art LEFT JOIN gk_blog_categories ON gk_blog_categories.id_top = gk_blog_posts.idtop_art WHERE `status_art` = 'publish' GROUP BY id_art ORDER BY date_art desc LIMIT 5
[eluser]mackerel[/eluser]
You're missing a db->from() in the query - hence no 'FROM' clause is appearing in the final SQL.
[eluser]eokorie[/eluser]
Ok I have made some changes to the SQL. I have added alias where needed and added the db->from() where it needed to appear. Although, it is returning articles from the database, it's unfortunately returning the same article for all the number of posts in the database (i.e. 65 records and in the view its showing the same article title 65 times).
Code: public function get_all_entries($limit, $offset, $cat='')
{
$this->db->select('*');
$this->db->select("DATE_FORMAT(gk_blog_posts.date_art,'%W, %D %M %Y') AS article_date", FALSE );
$this->db->select("Count(gk_blog_comments.id_com) AS TotalComments");
$this->db->from('gk_blog_posts bp');
($cat)? $this->db->where('idtop_art', $cat) : '' ;
$this->db->where('bp.status_art','publish');
// $this->db->where('gk_blog_comments.is_approved','1');
$this->db->join('gk_users', 'gk_users.id_usr = gk_blog_posts.author_art', 'left');
$this->db->join('gk_blog_comments', 'gk_blog_comments.idart_com = bp.id_art', 'left');
$this->db->join('gk_blog_categories', 'gk_blog_categories.id_top = bp.idtop_art', 'left');
$this->db->order_by('bp.date_art','desc');
$this->db->group_by('bp.id_art');
$this->db->limit($limit, $offset);
$articles = $this->db->get('gk_blog_posts')->result();
foreach($articles as $article) {
$this->db->select('*');
$this->db->from('gk_blog_tags bt');
$this->db->join('gk_blog_posts_tags bpt', 'bpt.gk_blog_tag_id = bt.id_tag');
$this->db->where('bpt.gk_blog_post_id', $article->id_art);
$article->tags = $this->db->get('gk_blog_tags')->result();
}
return $articles;
}
Any ideas how I can fix that? thanks
[eluser]Chad Fulton[/eluser]
One problem you have is that you cannot use an order_by clause before a group_by clause.
You may also want to consider not running a query within the foreach() loop. It means that each time the page is viewed, 65+ queries will be performed, and performance will suffer.
[eluser]mackerel[/eluser]
[quote author="Chad Fulton" date="1250202037"]One problem you have is that you cannot use an order_by clause before a group_by clause.[/quote]
Fortunately, codeigniter is clever enough to create the SQL query with these two clauses in the correct order, regardless of when you declare them in your code. So this is not the issue.
I think you need to review the SQL you are using - construct the SQL in PHPmyAdmin or similar and then convert it into acrive record statements. Or show us the code in the view, since this maybe where the issue lies. Or both. ;-)
Cheers
|
