查询在MYSQL中工作，但不在MYSQLI中

My query was working in MYSQL but not in MYSQLI.

我的查询在MYSQL中工作，但在MYSQLI中没有。

I'm selecting students that have done the pre AND post test.

我正在选择完成前后测试的学生。

 SELECT 
  studid as Username, 
  prepoints as 'Fitness Assessment Points Grade', 
  end AS 'End-of-Line Indicator' 
 FROM 
  fittest, points 
 WHERE
  YEAR(submitted) = '2017' 
  AND semester = 'summer2' 
 GROUP BY studid
 HAVING 
  (MAX(prepost = 'pre' ) + MAX(prepost = 'post')) = 2 AND COUNT(DISTINCT prepost) = 2

Any ideas what changes need to be made to get it working again?

任何想法需要做些什么来让它再次运作？

Here is some sample data:

以下是一些示例数据：

 CREATE TABLE `fittest` (   `id` int(11) NOT NULL,   `submitted`
 datetime DEFAULT NULL,   `studid` varchar(100) DEFAULT NULL,  
 `semester` varchar(50) DEFAULT NULL,   `instructor` varchar(30)
 DEFAULT NULL,   `course` enum('PHED 1164') DEFAULT NULL,   `section`
 enum('5001','5003','5005','5007','5009','5011','5013','5015','5017','5019','5021','5023','5025','5027','5029','5031','5033','5035','5037','5039','5041','5043','5045','5047','5049','5051','5053','5055','5057','5059','5061','5063','5065','5067','5069')
 DEFAULT NULL,   `age` varchar(50) DEFAULT NULL,   `gender`
 enum('m','f') DEFAULT NULL,   `ethnicity` enum('Hispanic','African
 American','Asian','White-Non Hispanic','Other') DEFAULT NULL,  
 `height` char(4) DEFAULT NULL,   `weight` int(3) DEFAULT NULL,  
 `flexibility` int(2) DEFAULT NULL,   `crunches` int(3) DEFAULT NULL,  
 `pushups` int(3) DEFAULT NULL,   `treadtimemin` int(2) DEFAULT NULL,  
 `treadtimesec` int(2) NOT NULL,   `treadhr` int(3) DEFAULT NULL,  
 `prepost` enum('pre','post') NOT NULL,   `end` char(1) NOT NULL
 DEFAULT '#' ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 INSERT INTO `fittest` (`id`, `submitted`, `studid`, `semester`,
 `instructor`, `course`, `section`, `age`, `gender`, `ethnicity`,
 `height`, `weight`, `flexibility`, `crunches`, `pushups`,
 `treadtimemin`, `treadtimesec`, `treadhr`, `prepost`, `end`) VALUES
 (17, '2017-01-02 21:55:33', 'slacker', 'spring', 'Tim', 'PHED 1164',
 '5001', '32', 'm', NULL, '69.5', 155, NULL, 29, 34, 22, 15, 76, 'pre',
 '#'), (16, '2017-01-02 21:31:34', 'bfun', 'spring', 'Tim', 'PHED
 1164', '5001', '32', 'm', NULL, '69.5', 122, NULL, 37, 36, 18, 14, 76,
 'post', '#'), (15, '2017-01-02 21:31:09', 'bfun', 'spring', 'Tim',
 'PHED 1164', '5001', '32', 'm', NULL, '69.5', 129, NULL, 21, 20, 23,
 14, 76, 'pre', '#'),

1 个解决方案

#1

I just figured out the issue isn't with the query. It was with the input. All the input had post for the prepost value so there were no results that were showing. Thanks for your help.

我只是弄清楚问题不在于查询。这是输入。所有输入都有帖子的前置值，因此没有显示结果。谢谢你的帮助。