php - 根据从另一个页面发送的值定义MYSQL查询

Question

我正在尝试根据通过 URL 从父页面发送的值是否存在(或不存在)来设置 MYSQL 查询。变量如下:

        $startdate = $_GET["selectstartdate"];
        $enddate = $_GET["selectenddate"];
        $ensemble = $_GET["selectensemble"];
        $venue = $_GET["selectvenue"];
        $city = $_GET["selectcity"];

前两个始终为非 NULL，但其余三个具有默认值 NULL，该值可能会也可能不会在父页面上重新定义。因此，我需要做的是根据我收到的这些变量来重构我的 SQL 查询。

如果仅发送前两个变量(所有其他变量均为 NULL)，则以下查询是正确的:

        $sql = "SELECT Concert.date, Ensemble.long_name, Concert.title, ensemble_web, Concert.info, Concert.repertoire, Venue.name, venue_web, Venue.address, Venue.city, Venue.map 
                FROM Concert, Ensemble, Venue
                WHERE Concert.ensemble_id = Ensemble.id AND Concert.venue_id = Venue.id
                AND Concert.date BETWEEN '$startdate' AND '$enddate'";

但是，例如，如果 $ensemble 不为 NULL，那么我需要在查询中添加额外的一行:

                AND Ensemble.short_name='$ensemble';

同样，如果 $venue 不为 NULL，那么我需要:

                AND Venue.name='$venue';

...当然，如果 $city 不为 NULL，那么我需要:

                AND Venue.city='$city';

我确实知道解决这个问题的最佳方法是什么。非常感谢您的帮助!

Answer 1

以下是您以更简单的方式管理位置条件的答案:

<?php
// note that this mysqli_real_escape_string will prevent sql injection and $con is your connection variable
$startdate = mysqli_real_escape_string($con, $_GET["selectstartdate"]);
$enddate = mysqli_real_escape_string($con, $_GET["selectenddate"]);
$ensemble = mysqli_real_escape_string($con, $_GET["selectensemble"]);
$venue = mysqli_real_escape_string($con, $_GET["selectvenue"]);
$city = mysqli_real_escape_string($con, $_GET["selectcity"]);


$sql = "SELECT Concert.date, Ensemble.long_name, Concert.title, ensemble_web, Concert.info, Concert.repertoire, Venue.name, venue_web, Venue.address, Venue.city, Venue.map 
        FROM Concert, Ensemble, Venue
        WHERE Concert.ensemble_id = Ensemble.id AND Concert.venue_id = Venue.id
        AND Concert.date BETWEEN '$startdate' AND '$enddate'";
$where_clause = ["Concert.ensemble_id = Ensemble.id", "Concert.venue_id = Venue.id", "Concert.date BETWEEN '$startdate' AND '$enddate'"];

if(!empty($ensemble)) {
    $where_clause[] = "Ensemble.short_name='$ensemble'";
}

if(!empty($venue)) {
    $where_clause[] = "Venue.name='$venue'";
}

if(!empty($city)) {
    $where_clause[] = "Venue.city='$city'";
}

$sql .= " WHERE " . implode(" AND ", $where_clause);