多参数不工作的mysql查询

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

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.

I think problem is in the query section here

on c.course_provider_id = p.course_provider_id AND c.course_price = 0

You need to add " WHERE " after p.course_provider_id before first "AND" operator.

The new query should look like

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 WHERE 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

And there is one more issue in the logic check out one more time.

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

How can all above conditions be true ? I don't actually know what you want to achieve. I think you should use "OR" operator or use one condition " c.course_price > 0 "

Let me know if it didn't work. And please send the error also thanks.