SQL 选择字符串不起作用 [英] SQL Select string not working

问题描述

我已经发布了这个，但我想更好地解释它.我有一个网站，学生在其中插入他们的 4 门课程和每门课程的 4 条评论，这些是 SQL 表列:课程 1、课程 2、课程 3、课程 4、评论 1、评论 2、评论 3、评论 4.

我有一个搜索，任何学生都可以在其中输入地理(可以保存在四个课程列中的任何一个中)，并且我希望我的 SQL 查询返回地理的所有评论.例如，如果学生在 Course2 位置保存了 Geography，我希望我的 SQL 查询选择 comment2，其中 course2 = geography.他可能已经保存在course1中了，所以要灵活一些，但只能选择学生选择的课程.这是我当前的 SQL 查询:

$SQL = "SELECT (Comment1 FROM Students WHERE Course1 = 'geography'), (Comment2 FROM Students WHERE Course2 = 'geography'), (Comment3 FROM Students WHERE Course3 = 'geography'), (Comment4 FROM学生 WHERE Course4 = '地理')";

目前，此 SQL 查询不起作用.我知道这种结构可能看起来很奇怪，但从逻辑上讲，正如您可能理解的那样，这是有道理的，尽管这可能不是正确的编码方式.然后我像这样打印所有的地理评论:

$null = '';if(mysql_num_rows($result)) {echo "
";而 ($row=mysql_fetch_array($result)) {if($row["Class"]!=$null) {if($null!='') {echo "</ol><ol type='1'>";}}echo "<li><p>"."".$row["Comment1"]."".$row["Comment2"]." " .$row["Comment3"] ."".$row["Comment4"] ."</li></p>";$i = $i +1;}echo "</ol>";

解决方案

假设表结构真的(原文如此)不能改变(不再)我会选择 UNION 这里

 ( SELECT Comment1 as comment FROM soFoo WHERE Course1='geography' )联合所有( SELECT Comment2 as comment FROM soFoo WHERE Course2='geography' )联合所有( SELECT Comment3 as comment FROM soFoo WHERE Course3='geography' )联合所有( SELECT Comment4 as comment FROM soFoo WHERE Course4='geography' )

它至少给 MySQL 一个使用索引查找匹配记录的机会.

I've already posted this but I want to explain it better. I have a website in which students insert their 4 courses and 4 comments for each course, these are the SQL table columns: Course1, Course2, Course 3, Course 4, Comment1, Comment2, Comment 3, Comment 4.

I have a search in which any student inputs, for instance, geography (which may be saved in any of the four course columns), and I want my SQL query to return all the comments for geography. For example, if a student saved Geography in position Course2, I want my SQL query to select comment2 where course2 = geography. He may have saved it in course1, so it has to be flexible, but only select the course chosen by the student. This is my current SQL query:

$SQL = "SELECT (Comment1 FROM Students WHERE Course1 = 'geography'), (Comment2 FROM Students WHERE Course2 = 'geography'), (Comment3 FROM Students WHERE Course3 = 'geography'),  (Comment4 FROM Students WHERE Course4 = 'geography')";

Currently, this SQL query isn't working. I know the structure may seem odd, but logically, as you may understand, this makes sense, though it's probably not the right way to code it. I then print all the geography comments like this:

$null = '';
if(mysql_num_rows($result)) {
  echo "<ol>";  
 while ($row=mysql_fetch_array($result)) {
  if($row["Class"]!=$null) {  
  if($null!='') {  
  echo "</ol><ol type='1'>";   
  } 
 }

 echo "<li><p>" . " " . $row["Comment1"]. " " . $row["Comment2"]." " .    $row["Comment3"] . " " . $row["Comment4"] . "</li></p>";
$i = $i +1;
}

echo "</ol>";

解决方案

Assuming that table structure really(sic) can't be changed (anymore) I'd go with a UNION here

    ( SELECT Comment1 as comment FROM soFoo WHERE Course1='geography' )
UNION ALL
    ( SELECT Comment2 as comment FROM soFoo WHERE Course2='geography' )
UNION ALL
    ( SELECT Comment3 as comment FROM soFoo WHERE Course3='geography' )
UNION ALL
    ( SELECT Comment4 as comment FROM soFoo WHERE Course4='geography' )

It gives MySQL at least a fighting chance to use indices to find the matching records.

这篇关于SQL 选择字符串不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！