在查询中使用两个嵌套的select

Question

I am using phpmyadmin 127.0.0.1.

The following code shows error but I cannot find anything wrong

$sde=" select * from 
(select * from students1
WHERE 
((PartCode = '$s1' OR PartCode =CONCAT('D', '$s1')) AND     (ElectiveSubject1='$s11' OR ElectiveSubject2='$s11' OR ElectiveSubject3='$s11')) 
ORDER BY PartCode, AdmitCode, RollCode 
LIMIT ${'rr'.$rnn.'1b'}, ${'rr'.$rnn.'1'})  
LIMIT $qa1, $qa2
";

$rde2=mysql_query($sde);

Please help me in finding the error. The error removes if we omit the portions outside the brakets ().

Answer 1

The specific problem is that you are missing the table alias on the subquery. I think it would be easier to write the second condition using in:

select s.* 
from (select s.*
      from students1 s
      where ((PartCode = '$s1' OR PartCode = CONCAT('D', '$s1')) AND  
             '$s11' in (ElectiveSubject1, ElectiveSubject2, ElectiveSubject3)
      order by PartCode, AdmitCode, RollCode 
      limit ${'rr'.$rnn.'1b'}, ${'rr'.$rnn.'1'}
     ) s
LIMIT $qa1, $qa2;

Next. You need to move off the "mysql_" interface. This is no longer supported. When you switch to "mysqli_", then you can parameterize the column values. This is a better approach.

Next. You have three columns which only seem to differ by an integer at the end of their name. That is a sign of poor database design. In general, this means that your data model should have a junction table, with one row per student and elective subject.