当php var为空时，SQL param对应于'*'？

Question

I have some SQL functions, an HTML/JS form and a PHP script (which calls SQL functions with form parameters). The form just sends two parameters :

• date
• language

If date is empty, no problem, I can get the date of the day by PHP and pass it to my SQL request. But when $language is empty, I want to display results for all languages..

The php part :

$date = isset($_GET['date']) ? $_GET['date'] : '';
if (empty($date)) {
    $date = date("Y-m-d");
}
$language = isset($_GET['language']) ? $_GET['language'] : '';
if (empty($language)) {
    $language = '%';
}

$events = retrieve_events_by_type ($DB, $query, $language, $date);

My SQL function :

function retrieve_events_by_type ($DB, $type, $language, $date) {
    $req = $DB->prepare("SELECT organizer, eventname, eventplace, language, eventdate, eventhour, eventminutes FROM events where eventtype = ? AND language like ? AND eventdate = ?");

    $req -> bindParam(1, $type, PDO::PARAM_STR);
    $req -> bindParam(2, $language, PDO::PARAM_STR);
    $req -> bindParam(3, $date, PDO::PARAM_STR);
    $req -> execute();

    $events = $req->fetchAll(PDO::FETCH_ASSOC);
    $req->closeCursor();

    return ($events);

}

Can I get all languages with this SQL function ? Or am I forced to create other functions for all cases ..?

Thanks.

Answer 1

You can build your SQL statement to only include the WHERE portions for language and type if the corresponding vars are set:

function retrieve_events_by_type ($DB, $type, $language, $date) {
    $sql = "SELECT organizer, eventname, eventplace, language, eventdate, eventhour, eventminutes FROM events WHERE eventdate = :date ";

    $sql .= empty($language) ? "" : " AND language LIKE :language ";
    $sql .= empty($type) ? "" : " AND eventtype LIKE :type ";

    $req = $DB->prepare($sql);

    $req -> bindParam(':date', $date, PDO::PARAM_STR);

    if (!empty($language)
        $req -> bindParam(':language', $language, PDO::PARAM_STR);

    if (!empty($type)
        $req -> bindParam(':type', $type, PDO::PARAM_STR);

    $req -> execute();

    $events = $req->fetchAll(PDO::FETCH_ASSOC);
    $req->closeCursor();

    return ($events);
}

EDIT

Changed to named parameters instead of ?, as the index numbers would be different if you only passed two parameters instead of three.