0

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.

https://i.stack.imgur.com/4fgSo.png

halfer
  • 19,824
  • 17
  • 99
  • 186
Justin
  • 3
  • 4

1 Answers1

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>";
}
Blag
  • 5,818
  • 2
  • 22
  • 45
  • @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` – Blag Apr 13 '17 at 20:38
  • 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.. – Justin Apr 13 '17 at 21:19
  • @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 – Blag Apr 13 '17 at 21:25
  • 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! – Justin Apr 13 '17 at 21:30
  • @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 http://www.pontikis.net/blog/dynamically-bind_param-array-mysqli ) – Blag Apr 13 '17 at 21:51
  • I'm beginner, just using what i know:) i will read that and will change my code then without mysqli_:) but it will not solve my problem with dates:D – Justin Apr 13 '17 at 22:06
  • @Justin Ok, so I updated my answer with a PDO solution, it could seem a bit more complicated a first, but you should never use you php $var directly inside the SQL statement, like this `select * from t where id=$id`, read this http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work to learn why ;) . Ask if you don't understand anything – Blag Apr 13 '17 at 22:10
  • Parse error: syntax error, unexpected T_VARIABLE in .... eval()’d code on line 26 – Justin Apr 13 '17 at 22:22
  • @Justin my bad XD forgot the `;` of the death after `$stmt = $db->prepare($sql)` just add it and it should be ok ;) – Blag Apr 13 '17 at 22:23
  • Fatal error: Call to a member function prepare() on a non-object in ...... eval()'d code on line 27 ($stmt = $db->prepare($sql);) line 27 here – Justin Apr 13 '17 at 22:44
  • @Justin have you changed the `$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);` to suite your config ? – Blag Apr 13 '17 at 22:47
  • i was made with this example: $conn = new mysqli('db_server', 'db_user', 'db_passwd', 'db_name'); if($conn->connect_error) { $this->last_error = 'Cannot connect to database. ' . $conn->connect_error; } – Justin Apr 13 '17 at 23:00
  • now i made with $db = new PDO('mysql:host=localhost;dbname=test', $user, $pass); and look what i get: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user ''@'localhost' (using password: NO)' in ...... eval()'d code:3 Stack trace: #0 ....... eval()'d code(3): PDO->__construct('mysql:host=loca...', NULL, NULL) #1 ........ ExecPhp_Runtime->eval_php('filter_user_content(' – Justin Apr 13 '17 at 23:01
  • i tested with print_r, with your $db = new PDO('mysql:host=localhost;dbname=test', $user, $pass); i'm not connected with DB, with $conn = new mysqli('db_server', 'db_user', 'db_passwd', 'db_name'); if($conn->connect_error) { $this->last_error = 'Cannot connect to database. ' . $conn->connect_error; } im connected.. can i get your email? it will be simple to show then.. – Justin Apr 13 '17 at 23:15
  • `Access denied for user ''@'localhost' (using password: NO)` => speech for itself no ? the equal version of `mysqli('db_server', 'db_user', 'db_passwd', 'db_name'); ` in PDO is : `$db = new PDO('mysql:host=db_server;dbname=db_name', 'db_user', 'db_passwd');` – Blag Apr 13 '17 at 23:19
  • yeah i understand that smth goes wrong, because password: NO. thats ok, im connected, now problem in line 28... while($r = $stmt->fetch_object()){... sorry if im bored u.. Fatal error: Call to undefined method PDOStatement::fetch_object() in – Justin Apr 13 '17 at 23:28
  • @Justin here is the update, and this time it work ;) – Blag Apr 15 '17 at 11:47