php mysql 如何连接两个表

Question

我有这些代码:

<?php

$records = mysql_connect('localhost', 'root', '') or die(mysql_error());

mysql_select_db('records', $records);
if(isset($_GET['src']))
{

$sql = "SELECT * FROM students where studentnumber like '%{$_GET['src']}%'";
$cnt = mysql_num_rows(mysql_query($sql));
if ($cnt == 0)
{
echo "<script>alert('No Record Found');</script>";
}

$result = mysql_query($sql, $records);
echo "<table border='0' class='table table-striped table-bordered table-hover'>";
echo "<tr class='info'><td width='10%'>Name</td><td width='11%'>Course Yr-Sec</td><td width='10%'>Student Number</td><td width='10%'>Violation</td><td width='10%'>Punishment</td><td width='9%'>Violation Date</td><td width='7%'>Punishment Date</td><td width='5%'>CS Length</td><td width='4%'>CS Done</td><td width='4%'>CS Left</td><td width='17%'><center>Action</center></td></tr></tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>";
echo $row['Lastname'];
echo ", ";
echo $row['Firstname'];
echo " ";
echo $row['Middleinitial'];
echo "</td>";
echo "<td>";
echo $row['Course'];
echo " ";
echo $row['Year'];
echo "-";
echo $row['Section'];
echo "</td>";
echo "<td>";
echo $row['Studentnumber'];
echo "</td>";
echo "<td>";
echo $row['Violation'];
echo "</td>";
echo "<td>";
echo $row['Punishment'];
echo "</td>";
echo "<td>";
echo $row['Violationdate'];
echo "</td>";
echo "<td>";
echo $row['Punishmentstartdate'];
echo "</td>";
echo "<td>";
echo $row['CSlength'];
echo "</td>";
echo "<td>";
echo $row['CSDone'];
echo "</td>";
echo "<td>";
echo $row['CSLeft'];
echo "</td>";
echo "<td>";
echo "<a href='edit.php?no={$row['ID']}'><input type='button' name='edit' value='Edit' class='btn btn-success'></a>";
echo "   <a href='delete.php?no={$row['ID']}'><input type='button' name='delete' value='Delete' class='btn btn-danger'></a>";
echo "   <input type='button' name='view' value='View' class='btn btn-info'>";echo "</td>";
echo "</tr>";
}
echo "</table>";
}
else
{
$sql = 'SELECT * FROM students';
$result = mysql_query($sql, $records);
echo "<table border='0' class='table table-striped table-bordered table-hover'>";
echo "<tr class='info'><td width='10%'>Name</td><td width='11%'>Course Yr-Sec</td><td width='10%'>Student Number</td><td width='10%'>Violation</td><td width='10%'>Punishment</td><td width='9%'>Violation Date</td><td width='7%'>Punishment Date</td><td width='5%'>CS Length</td><td width='4%'>CS Done</td><td width='4%'>CS Left</td><td width='17%'><center>Action</center></td></tr></tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>";
echo $row['Lastname'];
echo ", ";
echo $row['Firstname'];
echo " ";
echo $row['Middleinitial'];
echo "</td>";
echo "<td>";
echo $row['Course'];
echo " ";
echo $row['Year'];
echo "-";
echo $row['Section'];
echo "</td>";
echo "<td>";
echo $row['Studentnumber'];
echo "</td>";
echo "<td>";
echo $row['Violation'];
echo "</td>";
echo "<td>";
echo $row['Punishment'];
echo "</td>";
echo "<td>";
echo $row['Violationdate'];
echo "</td>";
echo "<td>";
echo $row['Punishmentstartdate'];
echo "</td>";
echo "<td>";
echo $row['CSlength'];
echo "</td>";
echo "<td>";
echo $row['CSDone'];
echo "</td>";
echo "<td>";
echo $row['CSLeft'];
echo "</td>";
echo "<td>";
echo "<a href='edit.php?no={$row['ID']}'><input type='button' name='edit' value='Edit' class='btn btn-success'></a>";
echo "   <a href='delete.php?no={$row['ID']}'><input type='button' name='delete' value='Delete' class='btn btn-danger'></a>";
echo "   <input type='button' name='view' value='View' class='btn btn-info'>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
}
?>      

它包含搜索、编辑、删除和查看功能...现在我的问题是...我想通过学生编号列连接数据库中的两个表...我的表 Students 包含列 Lastname、Firstname、Middleinitial、Course、Year、Section、Studentnumber、Violation、Punishment、Violationdate、Punishmentstartdate、CSlength、ID、CSDone、CSLeft...现在我的另一个名为 Students2 的表包含以下行 ID， Studentnumber、Violation、Punishment、Violationdate、Punishmentstartdate、CSlength、CSDone、CSLeft...我想显示两个表中的信息...例如，我想查看数据库中学号为 20101000 的所有记录...我必须内部连接表吗？我只是 php 的新手...预先感谢您...:)

Answer 1

这是一个LEFT JOIN 。它将返回 students1 中的所有记录。 ，以及来自 students2 的任何记录其中记录具有相同的 studentnumber作为students1中的记录:

SELECT * FROM students1
LEFT JOIN students2
ON students1.studentnumber = students2.studentnumber
AND students1.studentnumber = 20101000

安INNER JOIN仅返回产生匹配的记录，因此您只会获得具有相同 studentnumber 的记录。两者皆 students1和students2 。根据您的评论，我相信这就是您正在寻找的风格:

SELECT * FROM students1
INNER JOIN students2
ON students1.studentnumber = students2.studentnumber
AND students1.studentnumber = 20101000

如果您想了解如何使用 JOIN，我建议您尝试这两个语句来观察结果。然后尝试其他一些方法，也许使用 this excellent tutorial on the Coding Horror Blog .