从具有 2 个条件和 3 个表的表中检索信息

Question

<?php

define('DB_HOST', 'localhost');

define('DB_USER', 'root');

define('DB_PASS', '');

define('DB_NAME', 'websitefinal');

//connecting to database and getting the connection object

$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

//Checking if any error occured while connecting

if (mysqli_connect_errno()) {

    echo "Failed to connect to MySQL: " . mysqli_connect_error();

    die();

} else {

}

if ($_SERVER['REQUEST_METHOD'] == 'GET') {

    $id = $_POST['user_id'] ?? '';

    $query = "SELECT UL.* FROM users UL INNER JOIN active_crew L JOIN search SL      ON L.user_id = UL.user_id AND L.search_id = SL.search_id";

    $sql = mysqli_query($conn, $query);

    $result2 = array();

    while ($row = mysqli_fetch_array($sql)) {

        array_push($result2, array(

            'user_id' => $row[0],

            'name_full' => $row[1],

            'Propic' => $row[4]));

    }

    echo json_encode($result2);

    mysqli_close($conn);

}

我正在尝试检索在特定搜索“搜索表”中值班的活跃船员表中的用户“用户表”的信息，但我没有收到任何信息

我想从“user_id”主键和外键检索数据的用户表

crew 表只有 2 列 search_id 和 user_id 都是外键

应具有职责描述“search_id”主键和外键的搜索表

键关系对所有列都有效 search_id.crew with search_id.search user_id.crew with user_id.user

Answer 1

ON您必须在each 的子句中设置每个连接的条件JOIN，而不是在 last 中设置所有连接的条件ON：SELECT UL.*&nbsp;FROM users UL&nbsp;INNER JOIN active_crew L ON L.user_id = UL.user_id&nbsp;INNER JOIN search SL ON L.search_id = SL.search_id