嵌套查询mysql

Question

嗨，基本上，我做了一个剧本来展示不同公寓的预订。

我的桌子如下：

id | name     | booking | property_id | arrival    | url             | agent | created    | completed | sub_id
1    john doe   1123121   1             2015-08-09   http://some.url   john    2015-08-09   1           876544
2    mike doe   1111121   2             2015-08-10   http://some.url   john    2015-08-09   1           876344
3    sue doe    1331121   3             2015-08-10   http://some.url   john    2015-08-09   1           876321
4    lauren doe 1330021   4             2015-08-10   http://some.url   john    2015-08-09   1           876021

我希望输出像

财产ID 1

• 无名氏

财产ID 2

• 麦克·多伊

财产ID 3

• 无名氏

财产ID 4

• 劳伦·多伊

如果不存在某项财产的预订，则不应显示该属性。

我有一些循环解决方案，但它不起作用，预订随机出现并消失。

$getgroups = mysqli_query($con, "SELECT DISTINCT(`property_id`) FROM `Data` WHERE `arrival` >= NOW()");

while($row = mysqli_fetch_assoc($getgroups)) {
    if (mysqli_num_rows($getgroups) != 0) {    
        $property_id = $row["property_id"];
        if ($row["property_id"] == "1") {
            echo "<h3>Property 1</h3>";
        } elseif ($row["property_id"] == "2") {
            echo "<h3>Property 2</h3>";
        } elseif ($row["property_id"] == "3") {
            echo "<h3>Property 3</h3>";
        } elseif ($row["property_id"] == "4") {
            echo "<h3>Property 4</h3>";
        }
    }


    $getbookings = mysqli_query($con, "SELECT * FROM `Data` WHERE `property_id` = \"$property_id\" AND `arrival` >= NOW() ORDER BY `arrival` ASC");

    echo "<ul class=\"list-group\">";
    while($row = mysqli_fetch_assoc($getbookings)) {
            $numberofitems++;

            $agent = strtoupper(substr($row["agent"],0,3));



            echo "<li class=\"list-group-item\" id=\"" . $row["id"] . "\" ><b>" . $row["name"] . "</b><div class=\"pull-right\"><span class=\"agent hidden-xs hidden-sm badge\" data-agent=\"" . $row["agent"] . "\" data-id=\"" . $row["id"] . "\">$agent</span> <span class=\"hidden-xs hidden-sm label label-primary\">" . $row["booking"] . "</span> <span class=\"hidden-xs hidden-sm label label-info\">$arrival</span> ";
            if ($row["completed"] == "1") {
                if ($row["sub_id"] != "") {
                    echo "<a href=\"\" class=\"label label-success\">Completed</a> ";
                } else {
                    echo "<span class=\"label label-success\">Completed</span> ";  
                }
                echo "<span class=\"incomplete glyphicon glyphicon-remove\" data-id=\"" . $row["id"] . "\"></span> ";
            } else {
                echo "<span class=\"showlink label label-danger\" data-booking=\"" . $row["booking"] . "\">Incomplete</span> <span class=\"complete glyphicon glyphicon-ok\" data-id=\"" . $row["id"] . "\"></span> ";
            }
            echo "<span class=\"delete glyphicon glyphicon-trash\" data-cust_name=\"" . $row["name"] . "\" data-id=\"" . $row["id"] . "\"></span>";
            echo "</div></li>";

    }
    echo "</ul>";
}

Answer 1

您使用相同的变量名来存储来自每个请求的每一行。我鼓励您对变量使用“有意义的”名称，如$group和$booking (在这两种情况下都不用$row )。但这是个细节。

尽管如此，我认为问题来自于对数据库的$con连接处理程序的重用:您执行第一个请求，将一个处理程序发回一个带有一些结果的处理程序。然后，使用getBookings，使用相同的$con处理程序执行第二个请求，并返回一些其他结果。因此，您删除/替换第一个结果列表，而第一个while循环已损坏。您需要使用另一个$con处理程序来避免混合这两种结果。