您好,我有一个带有内部联接的查询,其中有一个问题和来自数据库的4个答案。我想让这个查询只接受lang table'en'的问题
这是查询:
$mysql->query("SELECT Q.id AS id, Q.question, QA.answer1, QA.answer2,
QA.answer3, QA.answer4, QA.correct, QC.name AS cat_name
FROM question Q
INNER JOIN question_answers QA ON QA.questionFK=Q.id
INNER JOIN question_cats QC ON QC.id=Q.categoryFK
ORDER BY rand()
LIMIT 1");
我尝试使它像这样:
$mysql->query("SELECT Q.id AS id, Q.question, QA.answer1, QA.answer2,
QA.answer3, QA.answer4, QA.correct, QC.name AS cat_name
FROM question Q
INNER JOIN question_answers QA ON QA.questionFK=Q.id
INNER JOIN question_cats QC ON QC.id=Q.categoryFK
WHERE Q.lang='en'
ORDER BY rand()
LIMIT 1");
但这没有用,选择了一切...
我在哪里错了,应该怎么做?
这些是2个表:
CREATE TABLE IF NOT EXISTS `question` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`question` varchar(255) NOT NULL,
`cnt` int(10) NOT NULL DEFAULT '0',
`correct` int(10) NOT NULL DEFAULT '0',
`categoryFK` int(3) NOT NULL DEFAULT '0',
`from_userFK` int(10) NOT NULL DEFAULT '0',
`correct_points` int(10) DEFAULT NULL,
`ut` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`del` enum('yes','no') NOT NULL DEFAULT 'no',
`lang` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;
CREATE TABLE IF NOT EXISTS `question_answers` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`questionFK` int(10) NOT NULL,
`answer1` varchar(255) CHARACTER SET cp1251 NOT NULL,
`answer2` varchar(255) CHARACTER SET cp1251 NOT NULL,
`answer3` varchar(255) CHARACTER SET cp1251 NOT NULL,
`answer4` varchar(255) CHARACTER SET cp1251 NOT NULL,
`correct` int(1) NOT NULL,
`ut` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`del` enum('yes','no') CHARACTER SET cp1251 NOT NULL DEFAULT 'no',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
最佳答案
在我看来,您的提交人条件WHERE Q.lang='en'
与任何记录都不匹配,因此结果不匹配。在比较之前考虑修剪它
WHERE TRIM(Q.lang) ='en'