一个论文检索系统，目前数据库只有一张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("");
print("年代学生姓名导师姓名题目");
while($row = mysql_fetch_array($res)) {
print("");
print("".$row["byear"]."");
print("".$row["bname"]."");
print("".$row["tname"]."");
print("http://localhost/pdf/".$row["bid"].".pdf\">".$row["btitle"]."");
print("");
}
print("");
请问改成两张表后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 还能用到么？
|
