Question

PHP+MySQL多条件检索

一个论文检索系统，目前数据库只有一张data表（bid：学生ID ，byear ：年代，bname：学生姓名，btitle：题目，tname：导师姓名），现需要把tname拿出来放到另一张teacher表中，两张表分别是data表（bid：学生ID ，byear ：年代，bname：学生姓名，btitle：题目，tid：导师ID ），teacher表（tid：导师ID，tname：导师姓名），三个检索条件（年代，学生姓名，导师姓名）要求同时匹配，输出结果：年代，学生姓名，导师姓名，题目。一张表的情况下检索部分代码如下：
$condition = "";
if(isset($_POST["year"]) && ($_POST["year"] != "")){
$year = mysql_escape_string($_POST["year"]);
$year = str_replace("%", "\%", $year);
if ($condition == ""){
$condition = "WHERE byear LIKE \"%".$year."%\"";
} else{
$condition .= "AND byear LIKE \"%".$year."%\"";
}
}
if(isset($_POST["name"]) && ($_POST["name"] != "")){
$name = mysql_escape_string($_POST["name"]);
$name = str_replace("%", "\%", $name);
if ($condition == ""){
$condition = "WHERE bname LIKE \"%".$name."%\"";
} else{
$condition .= "AND bname LIKE \"%".$name."%\"";
}
}
if(isset($_POST["teacher"]) && ($_POST["teacher"] != "")){
$teacher = mysql_escape_string($_POST["teacher"]);
$teacher = str_replace("%", "\%", $teacher);
if ($condition == ""){
$condition = "WHERE tname LIKE \"%".$teacher."%\"";
} else{
$condition .= "AND tname LIKE \"%".$teacher."%\"";
}
}
$sql = "SELECT * FROM data ".$condition." ORDER BY bid";
$res = mysql_query($sql, $conn);

if ($condition == ""){
exit ("请输入内容");
}
print("<table border=\"1\" cellspacing=\"5\">");
print("<tr bgcolor=\"#00FFFF\"><td>年代</td><td>学生姓名</td><td>导师姓名</td><td>题目</td>");
while($row = mysql_fetch_array($res)) {
print("<tr>");
print("<td>".$row["byear"]."</td>");
print("<td>".$row["bname"]."</td>");
print("<td>".$row["tname"]."</td>");
print("<td><a href= \"http://localhost/pdf/".$row["bid"].".pdf\">".$row["btitle"]."</a></td>");
print("</tr>");
}
print("</table>");

请问改成两张表后SQL怎么写，试了SELECT byear,bname,tname FROM data,teacher WHERE data.tid=teacher.tid 检索不到结果。是不是应该修改$condition呢？完全新手，请详细点说明，谢谢
SELECT a.byear,a.bname,b.tname FROM data as a left join teacher on a.tid=b.tid where a.bname like '%xxxx%' and a.byear='xxxx' and b.tname like '%xxxx%'

你这样写，$condition 还能用到么？

Answer 1

SELECT a.byear,a.bname,b.tname FROM data as a left join teacher on a.tid=b.tid where a.bname like '%xxxx%' and a.byear='xxxx' and b.tname like '%xxxx%'

你的condition 只是where条件而已
依旧可以用，只不过，应该把内容中byear改成a.byear,tname 改成b.tname

SELECT a.*,b.tname FROM data as a left join teacher on a.tid=b.tid ".$condition." order by a.bid