php - 列出两个单独表中 ID 匹配的记录

Question

我的数据库中有两个表，一个(表 1)用于用户，其中包括:ID、名字、姓氏、登录名和密码。另一个表(table2)有数据，它也有一个ID字段。我想知道当表2的ID=表1的ID时，如何只显示表2中的数据。

我认为我需要添加以下参数，但不确定如何执行

$sql="SELECT * FROM Triage WHERE completed= 'no'";
$result=mysql_query($sql);

任何澄清请告诉我，谢谢

列出表 2 中数据的代码页

require_once('auth.php');

 $host="*"; // Host name 
 $username="*"; // Mysql username 
 $password="*"; // Mysql password 
 $db_name="*"; // Database name 
 $tbl_name="*"; // Table name 

// Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM Triage WHERE completed= 'no'";
$result=mysql_query($sql);

 ?>
<style type="text/css">
body {
    background-color: #FFF;
}
</style>

<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<table width="1270" border="0" cellspacing="1" cellpadding="0">
  <tr>
    <td width="1270"><table width="1270" border="1" cellspacing="0" cellpadding="3">
      <tr>
        <td colspan="400"><p align="center"><strong>List of patients for triage</strong></p></td>
      </tr>
      <tr>
        <td width="70" align="center"><strong>Reference</strong></td>
        <td width="120" align="center"><strong>Forename</strong></td>
        <td width="120" align="center"><strong>Surname</strong></td>
        <td width="100" align="center"><strong>DOB</strong></td>
        <td width="120" align="center"><strong>Mobile Number</strong></td>
        <td width="120" align="center"><strong>Home Number</strong></td>
        <td width="100" align="center"><strong>Date of Call</strong></td>
        <td width="100" align="center"><strong>Time of call</strong></td>
        <td width="100" align="center"><strong>Physio ID</strong></td>
        <td width="120" align="center"><strong>Triage completed</strong></td>
        <td width="120" align="center"><strong>Update</strong></td>
      </tr>
      <?php
 while($rows=mysql_fetch_array($result)){
 ?>
      <tr>
        <td><? echo $rows['Reference']; ?></td>
        <td><? echo $rows['Forename']; ?></td>
        <td><? echo $rows['surname']; ?></td>
        <td><? echo $rows['DOB']; ?></td>
        <td><? echo $rows['Mobile']; ?></td>
        <td><? echo $rows['Home']; ?></td>
        <td><? echo $rows['Date']; ?></td>
        <td><? echo $rows['Time']; ?></td>
        <td><? echo $rows['PhysioID']; ?></td>
        <td><? echo $rows['completed']; ?></td>
        <td align="center"><a href="update.php?Reference=<? echo $rows['Reference']; ? >">update</a></td>
      </tr>
      <?php
 }
 ?>
    </table></td>
  </tr>
</table>
<p>
  <?php
 mysql_close();
 ?>
</p>
<p>&nbsp;</p>

更新

//Include database connection details
require_once('config.php');

//Array to store validation errors
$errmsg_arr = array();

//Validation error flag
$errflag = false;

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
    die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
    die("Unable to select database");
}

//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
    $str = @trim($str);
    if(get_magic_quotes_gpc()) {
        $str = stripslashes($str);
    }
    return mysql_real_escape_string($str);
}

//Sanitize the POST values
$login = clean($_POST['login']);
$password = clean($_POST['password']);

//Input Validations
if($login == '') {
    $errmsg_arr[] = 'Login ID missing';
    $errflag = true;
}
if($password == '') {
    $errmsg_arr[] = 'Password missing';
    $errflag = true;
}

//If there are input validations, redirect back to the login form
if($errflag) {
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    session_write_close();
    header("location: login-form.php");
    exit();
}

//Create query
$qry="SELECT * FROM members WHERE login='$login' AND passwd='".md5($_POST['password'])."'";
$result=mysql_query($qry);

//Check whether the query was successful or not
if($result) {
    if(mysql_num_rows($result) == 1) {
        //Login Successful
        session_regenerate_id();
        $member = mysql_fetch_assoc($result);
        $_SESSION['SESS_MEMBER_ID'] = $member['member_id'];
        $_SESSION['SESS_FIRST_NAME'] = $member['firstname'];
        $_SESSION['SESS_LAST_NAME'] = $member['lastname'];
        session_write_close();
        header("location: member-index.php");
        exit();
    }else {
        //Login failed
        header("location: login-failed.php");
        exit();
    }
}else {
    die("Query failed");
}
?>

Answer 1

您必须使用INNER JOIN (当表1和表2为1:1时)，或LEFT JOIN (当表1和表2为1:n时)

$sql="
  SELECT t1.*, t2.*
  FROM Triage t1
  INNER JOIN members t2 ON (t2.PhysioID = t1.member_id)
  WHERE t1.completed = 'no'
";

已编辑#1:

$sql="
  SELECT t1.*, t2.*
  FROM Triage t1
  INNER JOIN members t2 ON (t2.PhysioID = t1.member_id)
  WHERE t1.completed = 'no' AND t1.member_id = ".$_SESSION['SESS_MEMBER_ID']."
";

在 $_SESSION['SESS_MEMBER_ID']将是您登录用户的 ID。

编辑#2:

$sql="
  SELECT t2.*
  FROM Triage t1
  INNER JOIN members t2 ON (t2.PhysioID = t1.member_id)
  WHERE t1.completed = 'no' AND t1.member_id = ".$_SESSION['SESS_MEMBER_ID']."
";