多参数不工作的mysql查询

Question

I am trying to implement multi-select checkbox price filters,currently I have 4 checkbox:

1.free
2.$0-$30
3.$30-$100
4.$100+

my php code is.

<input type="checkbox"  name = "price[]" <?=@$price_check?> class="filters" value="free">Free</label>
<input type="checkbox" name = "price[]" <?=@$price_check?> class="filters" value="affordable-price">0 - 30$
<input type="checkbox"  name = "price[]" <?=@$price_check?> class="filters" value="medium-price">30-100$</label>
<input type="checkbox"  name = "price[]" <?=@$price_check?> class="filters" value="high-price">100$+</label>


if(isset($_GET['price']) && $_GET['price']!="")
          {
     $query = "SELECT c.course_id,c.course_title,c.course_url,c.course_thumbnail_url,p.course_provider_name,c.course_category,p.course_provider_icon,c.course_rating,c.course_price from course_catalog_table c inner join course_providers_table p on c.course_provider_id = p.course_provider_id ";
            if(in_array('free',$_GET['price']))
            {
              $query .= "AND c.course_price = 0 ";
            }
            if(in_array('affordable-price', $_GET['price']))
            {
              $query .= "AND c.course_price between 0 and 30 ";
            }
            if(in_array('medium-price', $_GET['price']))
            {
              $query .= "AND c.course_price between 30 and 100 ";
            }
            if(in_array('high-price', $_GET['price']))
            {
              $query .= "AND c.course_price > 100 ";
            }
          }

Main Problem : My Query is working for only one checkbox, if user is selecting more than one checkbox,query is not returning any results, I tried printing the query and executed in phpmyadmin and no rows were returned, so now I know there is problem with my query but unable to find out what part is raising error.

Here is the mysql query when all checkbox were selected :

SELECT 
    c.course_id,
    c.course_title,
    c.course_url,
    c.course_thumbnail_url,
    p.course_provider_name,
    c.course_category,
    p.course_provider_icon,
    c.course_rating,
    c.course_price
FROM
    course_catalog_table c
        INNER JOIN
    course_providers_table p ON c.course_provider_id = p.course_provider_id
        AND c.course_price = 0
        AND c.course_price BETWEEN 0 AND 30
        AND c.course_price BETWEEN 30 AND 100
        AND c.course_price > 100

Answer 1

In your case, MySQL is not raising any errors, as the generated query is valid.

The problem lies in the logic of the query, if you check more than one checkbox, the query basically demands that the price is e.g. between 0 and 30 and between 30 and 100, which is alway false.

You should construct your query like this: [SELECTION STUFF] AND (c.course_price = 0 OR c.course_price between 0 and 30) and use OR to connect the various range specifications.

Also, as stated by Muzamil, you should seperate the JOIN ON condition from the range selection logic, using a WHERE before the first range statement.