我正在与一个已经在这里部分讨论过的概念作斗争,但我正在试图扩展它,并且被难住了。
我的查询从三个表中提取数据(我正在使用存储过程):

Select a.adID, a.typeID, l.description, l.available, l.cost, a.heading,
a.adDate, a.adActive, a.plannerID, t.typename
From advertisements as a, advert_types as t, locations as l
Where a.plannerID = 1 And a.typeID = t.typeID And l.locationID =
a.locationID
Order by a.adDate, a.typeID;

使用MySQL workbench中的查询编辑器,输出似乎是正常的,但我正尝试使用Month(a.adDate)作为第一个标题,然后使用字段t.typename作为第二个标题写出结果,因此应该如下所示:
January
___________________________________________________
Bistro    Heading    Date
------------------------------
E-newsletter  $100  ...
Digital Ad    $50   ...
Magazine Ad   $150  ...


February
___________________________________________________
Kitchen   Heading    Date
-------------------------------
Social Media   $200  ...
E-flyer Ad     $180  ...


March
___________________________________________________
Restaurant  Heading   Date
-------------------------------
Front cover   $500    ...
Back cover    $350    ...

每个月都有一个副标题(小酒馆、厨房、餐厅等),那么每个相应的副标题下都应该有下面列出的项目,并且也在正确的月份下。
我的PHP代码是:
$query = "Call listads_per_planner('$planner');";
$result = mysqli_query($conn, $query) or die("Query failed: " .
                                             mysqli_error($conn));
$num_rows = mysqli_num_rows($result);

$priorDate = null;
$priorType = null;
$counter = 0;
while ($row = $result->fetch_assoc()) {
    $theDate = $row['adDate'];
    $thestrDate = strtotime($theDate);
    $theadYear = date('Y', $thestrDate);
    $theadMonth = date('F', $thestrDate);
    $adMonthInt = date('m', $thestrDate);
    $typeName = $row['typename'];
    $heading = $row['heading'];
    $thetypeID = $row['typeID'];

    if ($priorDate != $adMonthInt) {

        if ($priorDate != null) {
            echo "";
        }
        if ($counter > 0){
            echo "<br /><br />\n";
        }

        echo "<h3>". $theadMonth ."</h3>\n";
        echo "<hr style=\"padding: 0; margin: 0\"/>\n";
        ($priorDate = $adMonthInt);
    }

    if ($thetypeID != $priorType){
        echo "<table border=\"0\" style=\"width: 100%; cellpadding: 2px;
     cellspacing: 1px\" >\n";
        echo "<tr>\n";
        echo "<td style=\"background-color: #cccccc; heigh: 26px; width: 30%\">\n";
        echo "<span style=\"margin-left: 3px\"><strong>". $typeName ."</strong>
     </span></td>\n";
        echo "<td style=\"background-color: #cccccc;\"><input type=\"text\"
     name=\"heading\" size=\"22\" maxlength=\"100\" value=\"". $heading ."\">
     </td>\n";
        echo "<td style=\"background-color: #cccccc;\"><span style=\"margin-left:
     2px\">Date: <select name=\"day\">\n";
        echo "<option value=\"1\">1</option>\n";
        echo "<option value=\"2\">2</option>\n";
        echo "<option value=\"3\">3</option>\n";
        echo "<option value=\"4\">4</option>\n";
        echo "<option value=\"5\">5</option>\n";
        //...
        echo "<option value=\"31\">31</option>\n";
        echo "</select>&nbsp;<select name=\"month\">\n";
        echo "<option value=\"1\">January</option>\n";
        echo "<option value=\"2\">February</option>\n";
        echo "<option value=\"3\">March</option>\n";
        echo "<option value=\"4\">April</option>\n";
        //...
        echo "<option value=\"12\">December</option>\n";
        echo "</select></span></td>\n";
        echo "<td style=\"background-color: #cccccc;\"><div align=\"right\">
     </td>\n";
        echo "<td style=\"background-color: #cccccc;\"><table border=\"0\"
     style=\"width: 100%\" cellspacing=\"0\" cellpadding=\"0\"><tr><td><div
     align=\"right\"><button type=\"submit\" class=\"btn btn-
     success\">Save</button></div></td><td></td><td><div align=\"right\"><button
     type=\"button\" class=\"btn btn-danger\">Remove</button></td></tr></table>
     </td>\n";
        echo "</tr>\n";
        echo "<tr>\n";
        echo "<td><strong>Location</strong> <a href=\"#\">> Add Location +</a></td>
     <td><strong>Available</strong></td><td><strong>Cost</strong></td><td>
     <strong>Default Cost</strong></td><td><strong>Delete?</strong></td>\n";
        echo "</tr>\n";
        $priorType = $thetypeID;
    }

    echo "<tr>\n";
    echo "<td style=\"height: 26px\">". $row['description'] ."</td>\n";
    echo "<td><input type=\"text\" name=\"avail\" size=\"2\" value=\"".
        $row['available'] ."\"></td>\n";
    echo "<td><input type=\"text\" name=\"cost\" size=\"7\" maxlength=\"8\"
     value=\"". $row['cost'] ."\"></td>\n";
    echo "<td>". $row['cost'] ."</td>\n";
    echo "<td><div align=\"left\" style=\"padding-left: 4px\"><a href=\"#\"
     title=\"Delete this location?\"><img src=\"img/delete.png\" border=\"0\">
     </a></div></td>\n";
    echo "</tr>\n";

    $counter++;
}

echo "</table>\n";

我在浏览器输出中看到意外结果,副标题填充不正确,列表结果显示在错误的月份下。
如果有人能提出一些见解,我将不胜感激。我可以只用一个标题,但似乎不能用副标题。
非常感谢。
更新:以下是数据库中的一些示例数据:
php - PHP和MySQL-在标题和子标题下列出查询结果-LMLPHP
下面是我得到的输出的截图:一月应该有两个位置,十一月应该只有一个。
php - PHP和MySQL-在标题和子标题下列出查询结果-LMLPHP

最佳答案

无论何时开始新的月份,都需要结束前一个类型ID的表,并将$priorType重置为null。否则,如果连续几个月具有相同的类型ID,则它不会启动新表。
当开始一个新的类型ID时,需要结束前一个类型ID的表。

    $query = "Call listads_per_planner('$planner');";
    $result = mysqli_query($conn, $query) or die("Query failed: " .
                                                 mysqli_error($conn));
    $num_rows = mysqli_num_rows($result);

    $priorDate = null;
    $priorType = null;
    $counter = 0;
    while ($row = $result->fetch_assoc()) {
        $theDate = $row['adDate'];
        $thestrDate = strtotime($theDate);
        $theadYear = date('Y', $thestrDate);
        $theadMonth = date('F', $thestrDate);
        $adMonthInt = date('m', $thestrDate);
        $typeName = $row['typename'];
        $heading = $row['heading'];
        $thetypeID = $row['typeID'];

        if ($priorDate != $adMonthInt) {
            if ($priorDate != null) {
                if ($priorType != null) {
                    echo "</table>\n";
                    $prioType = null;
                }
                echo "";
            }
            if ($counter > 0){
                echo "<br /><br />\n";
            }

            echo "<h3>". $theadMonth ."</h3>\n";
            echo "<hr style=\"padding: 0; margin: 0\"/>\n";
            ($priorDate = $adMonthInt);
        }

        if ($thetypeID != $priorType){
            if ($priorType != null) {
                echo "</table>\n";
            }
            echo "<table border=\"0\" style=\"width: 100%; cellpadding: 2px;
         cellspacing: 1px\" >\n";
            echo "<tr>\n";
            echo "<td style=\"background-color: #cccccc; heigh: 26px; width: 30%\">\n";
            echo "<span style=\"margin-left: 3px\"><strong>". $typeName ."</strong>
         </span></td>\n";
            echo "<td style=\"background-color: #cccccc;\"><input type=\"text\"
         name=\"heading\" size=\"22\" maxlength=\"100\" value=\"". $heading ."\">
         </td>\n";
            echo "<td style=\"background-color: #cccccc;\"><span style=\"margin-left:
         2px\">Date: <select name=\"day\">\n";
            echo "<option value=\"1\">1</option>\n";
            echo "<option value=\"2\">2</option>\n";
            echo "<option value=\"3\">3</option>\n";
            echo "<option value=\"4\">4</option>\n";
            echo "<option value=\"5\">5</option>\n";
            //...
            echo "<option value=\"31\">31</option>\n";
            echo "</select>&nbsp;<select name=\"month\">\n";
            echo "<option value=\"1\">January</option>\n";
            echo "<option value=\"2\">February</option>\n";
            echo "<option value=\"3\">March</option>\n";
            echo "<option value=\"4\">April</option>\n";
            //...
            echo "<option value=\"12\">December</option>\n";
            echo "</select></span></td>\n";
            echo "<td style=\"background-color: #cccccc;\"><div align=\"right\">
         </td>\n";
            echo "<td style=\"background-color: #cccccc;\"><table border=\"0\"
         style=\"width: 100%\" cellspacing=\"0\" cellpadding=\"0\"><tr><td><div
         align=\"right\"><button type=\"submit\" class=\"btn btn-
         success\">Save</button></div></td><td></td><td><div align=\"right\"><button
         type=\"button\" class=\"btn btn-danger\">Remove</button></td></tr></table>
         </td>\n";
            echo "</tr>\n";
            echo "<tr>\n";
            echo "<td><strong>Location</strong> <a href=\"#\">> Add Location +</a></td>
         <td><strong>Available</strong></td><td><strong>Cost</strong></td><td>
         <strong>Default Cost</strong></td><td><strong>Delete?</strong></td>\n";
            echo "</tr>\n";
            $priorType = $thetypeID;
        }

        echo "<tr>\n";
        echo "<td style=\"height: 26px\">". $row['description'] ."</td>\n";
        echo "<td><input type=\"text\" name=\"avail\" size=\"2\" value=\"".
            $row['available'] ."\"></td>\n";
        echo "<td><input type=\"text\" name=\"cost\" size=\"7\" maxlength=\"8\"
         value=\"". $row['cost'] ."\"></td>\n";
        echo "<td>". $row['cost'] ."</td>\n";
        echo "<td><div align=\"left\" style=\"padding-left: 4px\"><a href=\"#\"
         title=\"Delete this location?\"><img src=\"img/delete.png\" border=\"0\">
         </a></div></td>\n";
        echo "</tr>\n";

        $counter++;
    }

if ($priorType != null) {
    echo "</table>";
}

关于php - PHP和MySQL-在标题和子标题下列出查询结果,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51777176/

10-12 12:50
查看更多