SQL and PHP filter

Question

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>";
?>

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.

where i need to put this comand? sorry, im still learning... — Justin
– Justin, Commented Apr 13, 2017 at 13:44

Blag · Accepted Answer · 2017-04-15 23:32:28Z

1

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)

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

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>";
}

edited Apr 15, 2017 at 23:32

answered Apr 12, 2017 at 23:26

Blag

5,8942 gold badges26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Blag Over a year ago

@Justin could you add a bit more code ? I'll need from the SELECT you put in $mysql to the display of the result of mysql

Justin Over a year ago

take a look now. but i googled that coalesce command, it's really not for me, i dont need to put "from" and "to" together, i need to get all variety from that year gap. If in filter i choose 1990, i need to get answer all models in that gap, example if audi 100 was made from 1989 to 1992, and i chose 1990 in filter, it have to show in my result..

Blag Over a year ago

@Justin I can assure you that you probably should using coalesce(), but it's not the "answer", it's just to deal with the case a car don't have a to value, like when still in production : car xxxx, from 2016, to NULL ; I'll edit my answer

Justin Over a year ago

yeah u right i will need it in future for new cars., but now i have to deal with this problem... your answer with coalesce command is good, because i never used it before, now i know when and where i will use it:) thanks for that!

Blag Over a year ago

@Justin are you mandatory to use mysqli_ functions ? because they are sh** in this case, and a good PDO is way more easy... ( as explained here pontikis.net/blog/dynamically-bind_param-array-mysqli )

|

Collectives™ on Stack Overflow

SQL and PHP filter

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related