[mysql]SQL 命令的日期内 WHILE 循环

标签: MySQL PHP
发布时间: 2017/4/9 23:48:29
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有问题对我事件排序按日期,如同我在另一个表和事件日期事件 id。

代码如下所示。

$getEventIds = "SELECT * FROM rz6wq_ohanah_registrations WHERE email='".$userEmail."'";
$result = mysqli_query($conn, $getEventIds);

echo '<br />';

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $getEventTitle = "SELECT * FROM rz6wq_ohanah_events WHERE ohanah_event_id=" . $row['ohanah_event_id'] . " ORDER BY date ASC";
        $title = mysqli_query($conn, $getEventTitle);
        $rowTitle = $title->fetch_assoc();
        $originalDate = $rowTitle["date"];
        $newDate = date("d-m-Y", strtotime($originalDate));
        date_default_timezone_set('Europe/Copenhagen');
        $currentDate = date('d/m/Y');
        if(strtotime($currentDate) < strtotime($newDate)){        
        //echo "id: " . $row["ohanah_event_id"] . "<br>";
        echo "<div class='eventsTilmeldt'>";
        echo "<b>" . utf8_encode($rowTitle["title"]) . "</b><br>";
        echo "<br>";   
        echo "Status: "; if($row["paid"] == 0){echo "<span style='color:red;'>ej betalt</span>";}else{echo "<span style='color:green;'>betalt</span>";} 
        echo "<br>";           
        echo "Dato: " . $newDate . "<br>";
        echo "Tidspunkt: " . $rowTitle["start_time"] . "<br>";          
        echo "<br>";        
        echo "Adresse: " . utf8_encode($rowTitle["adress"]) . "<br>";
        echo "By: " . utf8_encode($rowTitle["geolocated_city"]) . "<br>";
        echo "Sted: " . utf8_encode($rowTitle["venue"]) . "<br>";                
        echo "</div>";
        echo "<br>"; 
        }else{
            echo "";
        }

    }
} else {
    echo "Ingen tilmeldte events.";
}

这一问题,它不正确地按日期排序我事件。我觉得它是有某事做与它被 while 循环在哪里我按日期排序?

"SELECT * FROM rz6wq_ohanah_events WHERE ohanah_event_id=" . $row['ohanah_event_id'] . " ORDER BY date ASC";

我需要先事件从另一个表,称为登记 — — 在此表格中是没有日期字段。

如何按日期排序此列表?

解决方法 1:

您可以使用 JOIN 相反︰

$getEventIds = "SELECT reg.*,event.* FROM rz6wq_ohanah_registrations as reg
JOIN rz6wq_ohanah_events as event ON reg.ohanah_event_id=event.ohanah_event_id
WHERE email='".$userEmail."'
ORDER BY event.date ASC";
官方微信
官方QQ群
31647020