PHP Mysql查询代码编写器 - PHP Mysql Query in codeigniter

PHP Mysql Query in codeigniter

本文关键字：代码 Mysql 查询 PHP | 更新日期: 2023-09-27

我有两个表一个是"forum_topic"第二个是"forum_comments"

我想要一个CodeIgniter查询得到一个forum_topic的细节，例如，我有一个查询"select * from forum_topic where topic_id = 1"，然后我想要所有的评论从forum_comments表基于"forum_comments。Topic_id = forum_topic。Topic_id "，但论坛评论应该在树视图格式，因为我们也有n级回复的每个评论，也存储在"forum_comments"表，你可以看到在forum_comments表有一个字段"parent"，持有"comment_id"的回复。

1 - forum_topic

CREATE TABLE IF NOT EXISTS `forum_topic` (
  `topic_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `category` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_date` datetime NOT NULL,
  `view_count` int(11) NOT NULL,
  `last_activity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('publish','unpublish') NOT NULL,
  PRIMARY KEY (`topic_id`)
)

2 - forum_comments

CREATE TABLE IF NOT EXISTS `forum_comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `comment_by` int(11) NOT NULL,
  `topic_id` int(11) NOT NULL,
  `parent` int(11) NOT NULL DEFAULT '0',
  `comment` text NOT NULL,
  `commented_date` datetime NOT NULL,
  `commented_type` enum('user','admin') NOT NULL DEFAULT 'user',
  `status` enum('publish','unpublish','block') NOT NULL,
  PRIMARY KEY (`comment_id`)

)

如果你正在寻找大量循环或连接的效率，我会说使用"左右树"格式的'forum_comments'表:

http://www.sitepoint.com/hierarchical-data-database-2/

http://en.wikipedia.org/wiki/Nested_set_model

您可以使用query

这样的模型函数

function getComments($topic_id){
    $query  =   "   SELECT
                        fc.*
                    FROM forum_topic AS ft
                    INNER JOIN forum_comments AS fc ON ft.topic_id  =   fc.topic_id
                    WHERE ft.topic_id   =   $topic_id
                    ORDER BY fc.parent";
    return $this->db->query($query)->result();
}

您可以尝试这个查询，看看它是否有效:

Select top.*,com.* from forum_topic as top left join forum_comments as com on com.topic_id = top.topic_id where top.topic_id = 1 order by top.topic_id desc