SQL和PHP过滤器 [英] SQL and PHP filter

问题描述

我有一个SQL表，其中包含模型年从和模型年到，在过滤器中，我只需要选择一个参数年，并且我想获得所有在该年间隙中的模型.

I have a SQL table with model year from and model year to, in filter I need to select only one parameter year and I want to get all models which are in that year gap.

<?php 

    $make= $_POST['make'];
    $model= $_POST['model'];
    $from= $_POST['year'];
    if(!empty($make)){$mysql="and `make`='$make'";}
    if(!empty($model)){$mysql.=" and `model`='$model'";}
    if(!empty($from)){$mysql.=" and `from`='$from'";}
    $spec=$mysqli->query("SELECT * FROM `cars` WHERE (from <= to AND to>= 
    from) AND id!='' $mysql ");
    while($r = $spec->fetch_object()){
    echo "$r->id $r->make $r->model $r->from";
    echo"</br>";
?>

使用此代码，我只能获得一年的时间.如何获得包括年份在内的所有年份的模型? (例如:如果我选择1990年的Audi 100，则需要获得所有1990年制造的Audi 100).看一下我的sql表示例.

With this code I can get only year from. How to get all models with year including from and to? (example: if I choose Audi 100 1990, I need to get all Audi 100 which were made in 1990). Take a look at my sql table example.

推荐答案

似乎您没有提出完整的请求，但是您应该可以使用以下方法进行操作:

Seem like you don't have put the full request, but you should be able to do something with this :

WHERE
    $year >= `from`
    AND $year <= coalesce(`to`, 9999)

coalesce()在这里，以防您没有日期to但有NULL(仍在生产中).

The coalesce() is here in case you don't have a to date but a NULL instead (still in production).

这里是完整版本:(由于我实在受不了mysqli_*功能，并且

Here is the full version : (As I really can't stand mysqli_* function, and they are not well suited/secure for this use case, This is a PDO solution)

<?php
    // DB connect
try {
    $db = new PDO('mysql:host=localhost;dbname=DB_name', 'username', 'password');
        // output as object
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); 
        // error SQL as object
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $db->exec("SET CHARACTER SET utf8");

} catch (Exception $e) {
    echo '<strong>'.$e->getMessage().'</strong><br />'."\n";
}
    // default parameters
$param = array();
$sql = "SELECT * FROM `cars` WHERE `id` != '' ";

if(!empty($_POST['make'])){
    $param[':make'] = $_POST['make'];
    $sql .= 'AND `make` = :make ';
}

if(!empty($_POST['model'])){
    $param[':model'] = $_POST['model'];
    $sql .= 'AND `model` = :model ';
}

if(!empty($_POST['from'])){
    $param[':from'] = $_POST['from'];
    $sql .= 'AND :from >= coalesce(`from`, 0) AND :from <= coalesce(`to`, 9999) ';
}

    // we prepare our request
$stmt = $db->prepare($sql);
    // we execute with our parameters
$stmt->execute($param);

while($r = $stmt->fetch()){
    echo $r->id.' - '.$r->make.' - '.$r->model.' - '.$r->from;
    echo"</br>";
}

这篇关于SQL和PHP过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！