CODEWITHSUNDEEP
phpsql
Alaey
Alaey

Reputation: 41

select price Between a range in query

Hello guys I want to know how to select price btween a range for example: 1000...2000...[Min(2100)...Max(4000)]...5000...

The price column is in the books table

my query is:

$sql = "SELECT DISTINCT bk.title AS Title, YEAR(bk.date_released) AS Year, bk.price   AS Price, cat.name AS Category, aut.name AS Author FROM books bk

       JOIN categories cat
          ON cat.id = bk.category

       JOIN books_covers bk_co
          ON bk_co.book_id = bk.id

       JOIN covers co
          ON co.id = bk_co.cover_id

       JOIN books_authors bk_aut
          ON bk_aut.book_id = bk.id

       JOIN authors aut
          ON aut.id = bk_aut.author_id

       JOIN books_languages bk_lan
          ON bk_lan.book_id = bk.id

        JOIN languages lan
          ON lan.id = bk_lan.lang_id

       JOIN books_locations bk_loc
          ON bk_loc.book_id = bk.id

       JOIN locations loc 
          ON loc.id = bk_loc.location_id

          WHERE (bk.price >= 2100 AND bk.price <= 4000) OR (bk.price >= 1000 AND bk.price <= 2000)

";

if (isset($_GET['srch_for'])){

      $locations = array();
      $getters = array();
      $queries = array();

      foreach($_GET as $key => $value) {
        $temp = is_array($value) ? $value : trim($value);
        if (!empty($temp)) {
          list($key) = explode("-",$key);
          if ($key == 'srch_locations'){
            array_push($locations,$value);
          }
          if (!in_array($key,$getters)){
            $getters[$key] = $value;
          }
        }
      }

      if (!empty($locations)) {
        $loc_qry = implode(",",$locations);
      }

      if(!empty($getters)) {

        foreach($getters as $key => $value){
          ${$key} = $value;
          switch($key) {
            case 'srch_for':
            array_push($queries, "(bk.title LIKE '%$srch_for%' || bk.description LIKE '%$srch_for%' || bk.isbn LIKE '%$srch_for%')");
            break;
            case 'srch_category':
            array_push($queries, "bk.category = $srch_category");
            break;
            case 'srch_cover':
            array_push($queries, "bk_co.cover_id = $srch_cover");
            break;
            case 'srch_author':
            array_push($queries, "bk_aut.author_id = $srch_author");
            break;
            case 'srch_language':
            array_push($queries, "bk_lan.lang_id = $srch_language");
            break;
            case 'srch_year':
            array_push($queries, "YEAR(bk.date_released) = $srch_year");
            break;
            case 'srch_locations':
            array_push($queries, "bk_loc.location_id IN ($loc_qry)");
            break;
          }
      }
    }
     
     if(!empty($queries)) {
         $sql .= " WHERE ";
         $i=1;
         foreach($queries as $query) {
             if ($i < count($queries)) {
                 $sql .= $query." AND ";
             }else{
                 $sql .= $query;
             }
             $i++;
         }

     }

      $sql .= " ORDER BY bk.title ASC";
  }

I updated the code Thank you for advance

Upvotes: 1

Views: 5020

Answers (2)

c..
c..

Reputation: 239

Add

WHERE (bk.price >= 2100 AND bk.price <= 4000)

to the end of your query after the joins if you want the price range between [Min(2100)...Max(4000)]

You can do multiple price ranges using OR

WHERE (bk.price >= 2100 AND bk.price <= 4000)
OR    (bk.price >= 1000 AND bk.price <= 2000)

Upvotes: 1

eeetee
eeetee

Reputation: 527

You can use BETWEEN

WHERE bk.price BETWEEN '5000' AND '8000'

More info here

Upvotes: 2

Related Questions