php - CI中的多个mysql查询

Question

我有一些多重查询，想在 Codeigniter 中执行。以下是我尝试过的代码。

  function seememberfilter($course,$n)
        {


        $this->load->database();
        $sql = array();
        $sql[] = "CREATE TEMPORARY TABLE temp1";
        $sql[] = "select id,rand, name,lname, email,phone from `member` where course like ('%$course%')";
        $sql[] = "CREATE TEMPORARY table temp2";
        $sql[] = "select distinct t.id,t.rand, t.name,t.lname, t.email,t.phone, a.quest_id from temp1 t left join assignment a on t.rand= a.rand";
        $sql[] = "CREATE TEMPORARY table randid";
        $sql[] = "select rand, quest_id from temp2 where quest_id=$n";
        $sql[] = "select t.id,t.rand, t.name,t.lname, t.email,t.phone, r.quest_id from temp1 t left join randid r on t.rand= r.rand";

         foreach ($sql as $sql_command)
            {    
                if ($debugging == 0)
                {
                    $this->db->query($sql_command);
                } 
                elseif ($debugging == 1)
                {
                    echo $sql_command;
                } 
            } 
return $query->result();

        }

上面的代码在模型中，下面的代码在 Controller 中

 public function assign(){
            $this->load->model("user_model");
            $course=$this->input->post('course');
            $n=$this->input->post('id');
            $data['sel'] = $this->user_model->seememberfilter($course,$n);  
            $this->load->view('assign',$data);
        }

mysql 查询在 phpmyadmin 中执行良好。我查了一下

我想要什么:我想在 CI 中成功执行 mysql 查询。

我正在编辑此内容并向您提供 SQL 表的完整详细信息

DROP TABLE IF EXISTS `member`;
CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rand` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `dob` varchar(255) NOT NULL,
  `course` text NOT NULL,
  `gender` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `zip` varchar(255) NOT NULL,
  `comment` text NOT NULL,
  `Aboutme` text NOT NULL,
  `dat` varchar(255) NOT NULL,
  `tim` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;



INSERT INTO `member` (`id`, `rand`, `email`, `name`, `lname`, `phone`, `dob`, `course`, `gender`, `address`, `city`, `state`, `zip`, `comment`, `Aboutme`, `dat`, `tim`) VALUES
(1, 'jrf20180961828', 'kk122344545@hotmail.com', 'Kishan', 'Yadav', '9717111111', '2018-09-01', 'paper first,Political Science,Philosophy,', 'male', 'Diamond Auto Mobiles', 'Belthara road', 'ballia', '221715', 'Good person.', '', '06-09-2018', '11:24:16pm'),
(2, 'jrf20180914721', 'kk1@gmail.com', 'Rohan', 'Yadav', '9717111111', '2018-09-01', 'paper first,Philosophy,History,', 'male', 'Diamond Auto Mobiles', 'Belthara road', 'ballia', '221715', 'Good Person.', '', '06-09-2018', '11:25:44pm'),
(5, 'jrf20180958284', 'ykishan94612121@gmail.com', 'kiran', 'Singh', '9717111111', '2018-07-07', 'paper first,Political Science,History,', 'female', 'Diamond Auto Mobiles', 'Belthara road', 'ballia', '221715', 'nnbmnm', '', '10-09-2018', '10:11:34pm'),
(4, 'jrf20180932851', 'kk12@gmail.com', 'Pankaj', 'Yadav', '9717111111', '2018-09-01', 'paper first,Philosophy,Psychology,History,', 'male', 'Diamond Auto Mobiles', 'Belthara road', 'ballia', '221715', 'nmbmbn', '', '10-09-2018', '10:10:19pm'),
(6, 'jrf20180929250', 'hjh@gmail.com', 'John', 'Corter', '9717111111', '2018-09-06', 'paper first,History,', 'male', 'Diamond Auto Mobiles', 'Belthara road', 'ballia', '221715', 'sxs', '', '11-09-2018', '12:09:49am');
COMMIT;

第二个表是

DROP TABLE IF EXISTS `assignment`;
CREATE TABLE IF NOT EXISTS `assignment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rand` varchar(255) NOT NULL,
  `course` varchar(255) NOT NULL,
  `quest_id` varchar(255) NOT NULL,
  `time` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;



INSERT INTO `assignment` (`id`, `rand`, `course`, `quest_id`, `time`, `date`) VALUES
(1, 'jrf20180914721', 'paper first', '2', '10:05:48pm', '2018-09-10'),
(2, 'jrf20180961828', 'paper first', '2', '10:06:49pm', '10-09-2018'),
(3, 'jrf20180914721', 'History', '2', '10:08:03pm', '10-09-2018'),
(4, 'jrf20180958284', 'History', '2', '10:12:05pm', '10-09-2018'),
(5, 'jrf20180914721', 'paper first', '1', '10:19:23pm', '10-09-2018'),
(6, 'jrf20180932851', 'History', '3', '12:07:48am', '11-09-2018');
COMMIT;

我的输出应该是这样的，我连接两个表来获得这个输出。 Attached pic of my output

我使用上面的查询得到这个输出。对不起我的英语。

Answer 1

在 db-fiddle 中聊天后:

select distinct t.id, t.rand, name, lname, email, phone, if(quest_id='2','2',NULL) as quest-id
from `member` t
left join assignment a on t.rand= a.rand
where t.course like '%History%'
    and (a.course like '%History%') or a.id is null

成员仅限于类(class)，分配相同的类(class)，如果找到则显示 quest(ion)_id 2，否则将该字段保留为 NULL。