CODEWITHSUNDEEP
phpmysql
Thelson Richardson
Thelson Richardson

Reputation: 107

mysql statement with LIKE and BETWEEN not working

I have this mysql statement that uses both a LIKE and a BETWEEN statement:

$stmt1 = $db->prepare("SELECT * FROM forsale WHERE `make` LIKE ? 
OR `model` LIKE ? AND `year` BETWEEN ? AND ? OR `price` BETWEEN ? 
AND ? LIMIT $start,$perpage");

$stmt1->execute(array($searchQuery1,$searchQuery2,$searchQuery3,
$searchQuery4,$searchQuery5,$searchQuery6));
$rows1 = $stmt1->fetchAll();

When I write the statement like this:

$firstWorking = $db->prepare("SELECT * FROM forsale WHERE `make` LIKE ? 
OR `model` LIKE ? LIMIT $start,$perpage");

It works fine, and the same goes when i isolate the BETWEEN:

$secondWorking = $db->prepare("SELECT * FROM forsale WHERE `year` BETWEEN ? AND ? 
OR `price` BETWEEN ? AND ? LIMIT $start,$perpage");

All of the LIKE statements are executing fine, but the issue im having is the BETWEEN statements are not working when the LIKE statements are in front of them. When I pick a range (eg. 1999-2008) no results are returned, but if I pick a date range, with a specified model or make, it only shows the result for the model and make, ignoring the between statement. Any help is appreciated!

Upvotes: 0

Views: 64

Answers (1)

Thelson Richardson
Thelson Richardson

Reputation: 107

I'm much wiser in programming than I was a year ago. I had to build a custom solution when the $_GET variables were empty, they would be replaced with "%%" wildcards or a custom int such as '1982' for the start year. Here's the answer ::

    if(isset($_GET['selectMake'])){
    $selectMake = '%'.$_GET['selectMake'].'%';
    } else {
    $selectMake = '%%';
    }

    if(isset($_GET['selectModel'])){
    $selectModel = '%'.$_GET['selectModel'].'%';
    } else {
    $selectModel = '%%';
    }

    if(isset($_GET['engineType'])){
    $engineType = '%'.$_GET['engineType'].'%';
    } else {
    $engineType = '%%';
    }

    if(isset($_GET['transmission'])){
    $transmission = '%'.$_GET['transmission'].'%';
    } else {
    $transmission = '%%';
    }

    if(!empty($_GET['startYear'])){
    $startYear = $_GET['startYear']; 
    } else {
    $startYear = '1982';
    }

    if(!empty($_GET['endYear'])){
    $endYear = $_GET['endYear']; 
    } else {
    $endYear = '2099';
    }

    if(!empty($_GET['minPrice'])){
    $minPrice = $_GET['minPrice']; 
    } else {
    $minPrice = '5000.00';
    }

    if(!empty($_GET['maxPrice'])){
    $maxPrice = $_GET['maxPrice']; 
    } else {
    $maxPrice = '1000000.00';
    }
    $stmt1 = $db->prepare("SELECT * FROM forsale WHERE (`make` LIKE ? OR `model` LIKE ?) AND (`transmission` LIKE ?) AND (`year` BETWEEN ? AND ?) AND (`price` BETWEEN ? AND ?) AND (`engineType` BETWEEN ? AND ?) LIMIT ?,?");
    $stmt1->execute(array($selectMake,$selectModel,$transmission,$startYear,$endYear,$minPrice,$maxPrice,$minEngine,$maxEngine,$start,$perpage));
    $rows1 = $stmt1->fetchAll();

Upvotes: 1

Related Questions