php - 使用 php 循环遍历多个连接表

Question

我正在一个网站上工作，从多个表中输出收集的数据，前两个表保存基本信息，后两个表保存属性和图像。目前，我已经完成了几行代码，这些代码返回表的值并输出最终将其附加到 html 时需要显示的大多数数据。我遇到的几个问题是复制问题，我理解这些问题与内部联接的领域有关。我的 php 文件已在一定程度上过滤掉了重复项，但在我尝试创建的两个数组中失败了，即图像数组或特征数组。所以我有

if (isset($_POST['getAll'])) {

$sortBy = $_POST['getAll'];

$sqlQuery = "
SELECT 
    u.user_id, 
    u.user_username, 
    pr.project_id, 
    pr.created_date, 
    pr.closing_date, 
    pr.prize, 
    pr.project_desc, 
    pr.project_title, 
    pr.state, 
    prr.room_id, 
    prr.room_name, 
    prr.room_type, 
    prp.prop_id, 
    prp.comment_extra_details, 
    prp.feature_name, 
    prf.caption, 
    prf.filename, 
    prf.filetype, 
    prf.file_id,
     prf.public_name 
FROM users AS u 
    INNER JOIN projects as pr ON u.user_id = pr.creator_id 
    INNER JOIN project_rooms as prr ON prr.project_id = pr.project_id 
    INNER JOIN project_properties as prp ON prp.room_id = prr.room_id 
    INNER JOIN project_files as prf ON prf.room_id = prp.room_id
WHERE 
    state = 1 
ORDER BY 
    `created_date` DESC";
}

$result = $conn->query($sqlQuery);

$proj_id = 0;
$room_id = 0;
$prop_id = 0;
$file_id = 0;

$projects = array();
$rooms = array();
$features = array();
$images = array();

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {

if ($proj_id != $row["project_id"]) {

    $details = array(
        "user_id" => $row["user_id"],
        "username" => $row["user_username"],
        "project_id" => $row["project_id"],
        "created_date" => $row["created_date"],
        "closing_date" => $row["closing_date"],
        "prize" => $row["prize"],
        "project_desc" => $row["project_desc"],
        "project_title" => $row["project_title"],
        "rooms" => array()
    );

    $proj_id = $row["project_id"];
    $projects[] = $details;
    $info = array();

}

if ($room_id != $row["room_id"]) {

        $info = array(
                "room_id" => $row["room_id"],
                "room_name" => $row["room_name"],
                "room_type" => $row["room_type"],
                "comment_extra_details" => $row["comment_extra_details"],
                "caption" => $row["caption"],
                "feats" => array(),
                "images" => array()
        );

        $room_id = $row["room_id"];
        $rooms[] = $info;
        $feat = array();
    $counter = sizeof($projects) - 1;
    $projects[$counter]["rooms"] = $info;


}

//This is giving me the trouble
//if ($prop_id != $row["prop_id"]) {

//  $feat = array(
//    "feature_name" => $row["feature_name"]
//);

//$prop_id = $row["prop_id"];
//$features[] = $feat;

//$projects[$counter]["rooms"]["feats"] = $features;

//}


else if ($file_id != $row["file_id"]) {

    $img[] = array(
        "filename" => $row["filename"],
        "filetype" => $row["filetype"],
        "file_id" => $row["file_id"],
        "public_name" => $row["public_name"]
    );

    $file_id = $row["file_id"];
    $images = $img;

    $projects[$counter]["rooms"]["images"] = $images;
}

}

输出:

My Semi Desired Output

当我把这个代码块扔在那里时，它会循环遍历我的功能行

if ($prop_id != $row["prop_id"]) {

  $feat = array(
    "feature_name" => $row["feature_name"]
);

$prop_id = $row["prop_id"];
$features[] = $feat;

$projects[$counter]["rooms"]["feats"] = $features;

}

我得到了这个不需要的输出，我只能假设这是由于逻辑流程和循环结束造成的:

The bad...

现在我知道绝对不是 15 个功能，而是有 5 个和 3 个图像，但我似乎无法在功能或图像不以某种方式破坏的情况下隔离其中一个或另一个。

编辑 - 我已经包含了查询返回的表数据的可视化，我希望这会有所帮助。

+---------+---------------+------------+---------------------+---------------------+-------+------------------+-------------------+------------+---------+-----------+-------------+---------+-----------------------+--------------+---------+-----------------------------------------------+------------+---------+-------------+
| user_id | user_username | project_id |    created_date     |    closing_date     | prize |   project_desc   |   project_title   |   state    | room_id | room_name |  room_type  | prop_id | comment_extra_details | feature_name | caption |                   filename                    |  filetype  | file_id | public_name |
+---------+---------------+------------+---------------------+---------------------+-------+------------------+-------------------+------------+---------+-----------+-------------+---------+-----------------------+--------------+---------+-----------------------------------------------+------------+---------+-------------+
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      42 | NULL                  | Decor        |         | b59b3bb7cac73e8cbba0f68795aba254_1459993038.p | image/png  |      38 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      43 | NULL                  | Furniture    |         | b59b3bb7cac73e8cbba0f68795aba254_1459993038.p | image/png  |      38 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      44 | NULL                  | Paint        |         | b59b3bb7cac73e8cbba0f68795aba254_1459993038.p | image/png  |      38 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      45 | NULL                  | Flooring     |         | b59b3bb7cac73e8cbba0f68795aba254_1459993038.p | image/png  |      38 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      46 | NULL                  | Apolstery    |         | b59b3bb7cac73e8cbba0f68795aba254_1459993038.p | image/png  |      38 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      42 | NULL                  | Decor        |         | 21c3b842752b3866a37912048e6d0bbd_1459993053.j | image/jpeg |      39 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      43 | NULL                  | Furniture    |         | 21c3b842752b3866a37912048e6d0bbd_1459993053.j | image/jpeg |      39 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      44 | NULL                  | Paint        |         | 21c3b842752b3866a37912048e6d0bbd_1459993053.j | image/jpeg |      39 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      45 | NULL                  | Flooring     |         | 21c3b842752b3866a37912048e6d0bbd_1459993053.j | image/jpeg |      39 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      46 | NULL                  | Apolstery    |         | 21c3b842752b3866a37912048e6d0bbd_1459993053.j | image/jpeg |      39 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      42 | NULL                  | Decor        |         | 5b4645c9ee88704eae9a12e422d86ba2_1459993063.j | image/jpeg |      40 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      43 | NULL                  | Furniture    |         | 5b4645c9ee88704eae9a12e422d86ba2_1459993063.j | image/jpeg |      40 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      44 | NULL                  | Paint        |         | 5b4645c9ee88704eae9a12e422d86ba2_1459993063.j | image/jpeg |      40 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      45 | NULL                  | Flooring     |         | 5b4645c9ee88704eae9a12e422d86ba2_1459993063.j | image/jpeg |      40 | NULL        |
|      10 | CalebB        |         27 | 2016-04-06 21:38:00 | 2016-04-27 21:38:00 |  5500 | heres some janks | Just another test | qualifying |      26 | NULL      | Family Room |      46 | NULL                  | Apolstery    |         | 5b4645c9ee88704eae9a12e422d86ba2_1459993063.j | image/jpeg |      40 | NULL        |
+---------+---------------+------------+---------------------+---------------------+-------+------------------+-------------------+------------+---------+-----------+-------------+---------+-----------------------+--------------+---------+-----------------------------------------------+------------+---------+-------------+

如果您能提供任何帮助或指出正确的方向，我们将不胜感激

Answer 1

由于我还不能发表评论，我会说我怀疑你有笛卡尔连接。

这些表中可能存在构成关系的附加标识符。

例如，您似乎正在获取该房间中所有项目的所有项目属性，而我假设您只需要与该用户 ID 关联的项目。

添加表格结构以获得更好的帮助

编辑:查看表结构后，您可能需要在联接中包含一个附加表。但要对此进行调试，您可以开始在选择中包含更多数据并查看结果，直到找到必须在作业条件中使用的问题列。

现在尝试一下:

    FROM users AS u
Inner join project_submissions as ps on
u.user_id = ps.user_id

INNER JOIN projects as pr ON u.user_id = pr.creator_id
And pr.project_id = ps.project_id
 INNER JOIN project_rooms as prr ON prr.project_id = pr.project_id
INNER JOIN project_properties as prp ON prp.room_id = prr.room_id
INNER JOIN project_files as prf ON prf.room_id = prp.room_id