I want to filter prices with checkboxes and php from a database

Question

I want to filter data on the database using a set of ranges. I have my checkbox down below.

<body>
<form id="form" method="post" action="">
<input type="checkbox" name="price1" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $0 - 5<br>
<input type="checkbox" name="price2" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $6 - 10<br>
<input type="checkbox" name="price3" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $11 - 20<br>
 </form>
 </body>

 <script type="text/javascript">  
    $(function(){
     $('.checkbox').on('change',function(){
        $('#form').submit();
        });
    });
</script>

Below is what I have for my PHP code

<?php
if (isset($_POST["price1"])){
  $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5");
 }

if (isset($_POST["price2"])){
  $query = $conn->queryn("SELECT * FROM item WHERE price BETWEEN 6 AND 10");
 }

if (isset($_POST["price3"])){
 $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 11 AND 20");
 }

else { $query = $conn->query("SELECT price * FROM item");
}
?>

Whenever I hit a checkbox mark it doesn't do anything it just shows a test Item I am pulling individually. The code below is just a test to make sure its pulling data from the database. Right now I am just pulling price ranges from 0 - 5 and it works. But the above code for the user to have a choice on selecting price ranges it wont do anything. Anything helps.

?php
        //get product rows
        $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5 ");

        if($query->num_rows > 0){
                while($row = $query->fetch_assoc()){
            ?>

                <div class="list-item">
                    <h2><?php echo $row["name"]; ?></h2>
                    <h4>Price: <?php echo $row["price"]; ?></h4>
                </div>
        <?php }
        }else{
            echo  'Product(s) not found';
        } ?>
    </div>
</div>

Code is below:

<?php 
$conn_error = "Could not connect";
// SQL connection credentials

//They are blanked out since it is connected to the server already

$mysql_host = "";
$mysql_user = "";
$mysql_pass = "";
$mysql_name = "";

$conn = new mysqli($mysql_host, $mysql_user, $mysql_pass,$mysql_name);

if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

<?php
        //get product rows test to see database is working 
        $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5 ");

        if($query->num_rows > 0){
                while($row = $query->fetch_assoc()){
            ?>

                <div class="list-item">
                    <h2><?php echo $row["name"]; ?></h2>
                    <h4>Price: <?php echo $row["price"]; ?></h4>
                </div>
        <?php }
        }else{
            echo  'Product(s) not found';
        } ?>
    </div>
</div> 

<body>
<form id="form" method="post" action="">
<input type="checkbox" name="price1" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $0 - 5<br>
<input type="checkbox" name="price2" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $6 - 10<br>
<input type="checkbox" name="price3" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $11 - 20<br>
 </form>
 </body>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.0/jquery.min.js"></script>
<script type="text/javascript">
    $(function(){
        $('.checkbox').on('change',function(){
            $('#form').submit();
        });
    });
</script>
<?php
    if (isset($_POST["price1"])){
        $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5");

    } elseif (isset($_POST["price2"])){
        $query = $conn->queryn("SELECT * FROM item WHERE price BETWEEN 6 AND 10");
    } elseif (isset($_POST["price3"])){
        $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 11 AND 20");
    } else {
        $query = $conn->query("SELECT price * FROM item");
    }
?>

User1010 · Accepted Answer · 2020-04-17T22:33:31.813

This is due to the ordering of your if statements,

<?php
if (isset($_POST["price1"])){
  $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5");
 }

if (isset($_POST["price2"])){
  $query = $conn->queryn("SELECT * FROM item WHERE price BETWEEN 6 AND 10");
 }

if (isset($_POST["price3"])){
 $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5");
 }

else { $query = $conn->query("SELECT price * FROM item");
}
?>

The else only counts on the last if statement try, I have replaced the middle two if's with "elseif" so that it forms one if statement,

<?php
    if (isset($_POST["price1"])){
        $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5");
    } elseif (isset($_POST["price2"])){
        $query = $conn->queryn("SELECT * FROM item WHERE price BETWEEN 6 AND 10");
    } elseif (isset($_POST["price3"])){
        $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 11 AND 20");
    } else {
        $query = $conn->query("SELECT price * FROM item");
    }
?>

I have also updated the last one to be 11-20 over 0-5 to match your check box text.

To clarify on the JS elements, you are using the jQuery $ selector, you must have first included jQuery to get this to work,

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.0/jquery.min.js"></script>
<script type="text/javascript">
    $(function(){
        $('.checkbox').on('change',function(){
            $('#form').submit();
        });
    });
</script>

Full example,

<?php
$conn_error = "Could not connect";
// SQL connection credentials

//They are blanked out since it is connected to the server already

$mysql_host = "";
$mysql_user = "";
$mysql_pass = "";
$mysql_name = "";

$conn = new mysqli($mysql_host, $mysql_user, $mysql_pass,$mysql_name);

if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>
    </div>
    </div>

    <body>
    <form id="form" method="post" action="">
        <input type="checkbox" name="price1" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $0 - 5<br>
        <input type="checkbox" name="price2" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $6 - 10<br>
        <input type="checkbox" name="price3" class="checkbox" <?=(isset($_POST['price'])?' checked':'')?>/> $11 - 20<br>
    </form>

    <?php
        if($_SERVER["REQUEST_METHOD"] == "POST") {
            if (isset($_POST["price1"])){
                $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 0 AND 5");
            } elseif (isset($_POST["price2"])){
                $query = $conn->queryn("SELECT * FROM item WHERE price BETWEEN 6 AND 10");
            } elseif (isset($_POST["price3"])){
                $query = $conn->query("SELECT * FROM item WHERE price BETWEEN 11 AND 20");
            } else {
                $query = $conn->query("SELECT price * FROM item");
            }

            if($query->num_rows > 0){
                while($row = $query->fetch_assoc()){
                    ?>

                    <div class="list-item">
                        <h2><?php echo $row["name"]; ?></h2>
                        <h4>Price: <?php echo $row["price"]; ?></h4>
                    </div>
                <?php }
            }else{
                echo  'Product(s) not found';
            }
        }
    ?>
    </body>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.0/jquery.min.js"></script>
    <script type="text/javascript">
        $(function(){
            $('.checkbox').on('change',function(){
                $('#form').submit();
            });
        });
    </script>

I didn't catch that issue of 0 - 5 again thanks. I have the JavaScript file but it doesn't seem to do anything still would I need to add an actual submit button to refresh the page so it returns the query that has been selected? — aaagggg1233, Apr 17 '20 at 21:47
@aaagggg1233 Have you included jQuery? I have extended my answer to include this. — User1010, Apr 17 '20 at 21:56
Yes I did include it. I am assuming I am going wrong somewhere else. The page refreshes but doesn't actually pull any of the queries from it. — aaagggg1233, Apr 17 '20 at 22:04
@aaagggg1233 Are you able to edit your question to include the full code with form and php in one so I can see the whole picture? — User1010, Apr 17 '20 at 22:06
yeah I can I willl post it below the original question just what I'm working with the rest is just CSS.Updated the post. So it is connecting to the database so that's not an issue. I know this because the one I use to test does pull the items in that price range. — aaagggg1233, Apr 17 '20 at 22:15
@aaagggg1233 You need to check the posted data and change the query on the fly as the page loads following the post. Please see my updated answer, if this works for you please accept the answer. — User1010, Apr 17 '20 at 22:34
yes it does work. If I wanted to set a default to display all items if nothing is clicked. Would that be included in my else statement at the end? Or would I need to create a default case such as Default: "SELECT price FROM item" Also one last thing is there any reason why the second checkbox doesn't execute? I do have price ranges within 6 - 10. Just reloads to a blank page. I did read up on another post it is due to the else if statements, but I am not sure if that's the case here. — aaagggg1233, Apr 17 '20 at 22:51
@aaagggg1233 It would use your else statement, although as there is no actual submit button this is likely not to be seen — User1010, Apr 17 '20 at 22:55
Okay I see I will most likely end up changing it to a submit button instead to avoid that issue from happening. — aaagggg1233, Apr 17 '20 at 23:07
Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Apr 17 '20 at 23:46

I want to filter prices with checkboxes and php from a database

1 Answers1