How do I filter clothing on sizes with PHP and AJAX?

I am trying to make a e-commerce filter system with PHP and AJAX. I have watched some guy on YouTube and it worked, but my e-commerce website is in the clothing industry and his was with electronics… So he did not explain how to filter sizes which is stored with quantity (for example: S:2, M:3, L:4) with the filter systems. Now if I add a filter for a product, the product shows that size correctly. But if I try it the second time, it does not execute. I think because of the way my column is set up in MySQL. Please help.

Here is my code for the filter system:

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">

<!-- jQuery library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

<!-- Popper JS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>

<!-- Latest compiled JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>

<ul class="list-group">
                    <li class="list-group-item">
                        <div class="form-check">
                            <label class="form-check-label">
                                <input type="checkbox" class="form-check-input product_check" value="S" id="Maat_Voorraad">S
                            </label>
                        </div>
                    </li>
                    <li class="list-group-item">
                        <div class="form-check">
                            <label class="form-check-label">
                                <input type="checkbox" class="form-check-input product_check" value="M" id="Maat_Voorraad">M
                            </label>
                        </div>
                    </li>
                    <li class="list-group-item">
                        <div class="form-check">
                            <label class="form-check-label">
                                <input type="checkbox" class="form-check-input product_check" value="L" id="Maat_Voorraad">L
                            </label>
                        </div>
                    </li>
                    <li class="list-group-item">
                        <div class="form-check">
                            <label class="form-check-label">
                                <input type="checkbox" class="form-check-input product_check" value="XL" id="Maat_Voorraad">XL
                            </label>
                        </div>
                    </li>
                </ul>

<script type="text/javascript">
    $(document).ready(function(){
        
        $(".product_check").click(function(){
            $("#loader").show();
            
            var action = 'data';
            var type = get_filter_text('type');
            var maat_voorraad = get_filter_text('Maat_Voorraad');
            
            $.ajax({
                url:'action.php',
                method:'POST',
                data:{action:action,type:type,maat_voorraad:maat_voorraad},
                success:function(response){
                    $("#result").html(response);
                    $("#loader").hide();
                    $("#textChange").text("Gefilterde producten");
                }
            });
            
        });

        function get_filter_text(text_id){
            var filterData = [];
            $('#'+text_id+':checked').each(function(){
                filterData.push($(this).val());
            });
            
            return filterData;
        }
        
    });

</script>


// action.php

if(isset($_POST['maat_voorraad'])){
   $maat_voorraad = implode("','", $_POST['maat_voorraad']);
   $sql .= "AND Maat_Voorraad LIKE '%$maat_voorraad%'";
}

// Maat_Voorraad refers to my table column in database. There the size and quantity is 
   stored per shirt like: S:2, M:4, L:3
// maat_voorraad means size_and_quantity and that variable sends only the size

See examplehere I would really appreciate the help!

Answer

From the sample code you have shared, I can see you have an issue with your database design. I would potentially have a table that stores shirt information like:

1. ID
2. Shirt Name
3. Shirt Price (Price of lowest SKU).
4. timestamps (When the item was added). You can add as many time stamps as are relevant to the product.

Another Table that stores shirt sizes:

1. id
2. name (e.g. “S”)

Another Table (PIVOT Table) That holds information about a shirt, the sizes it has and associated price and availability:

1. shirt_id (Foreign Key references Shirts Table)
2. size_id (Foreign Key references Shirt Size Table)
3. unit_price (Price of this particular size of the said shirt)
4. discount_price (If there is a special offer on shirt)

A table to hold images of each shirt:

1. id
2. shirt_id (Foreign Key references Shirts Table)
3. url (Location of shirt image)
4. timestamps (When the image was added and when it was last updated).

Lastly, order table

1. id (order id)
2. shirt_id (Foreign Key references Shirts Table)
3. shirt_size_id (Foreign Key references Shirt Size Table)
4. quantity
5. total
6. date (Order created, Order fulfilled)

This is just a guide you can use to design your Database more efficiently. I omitted some columns as I figured you might already have them. If you want to implement multiple items within an order, just create a table for order items where you store individual items that have been ordered, then have a table that stores order total.

The link below will shed more light on what I’ve written above.

It’s safe at this point to normalize your database to make querying easier. Refer to Normalization for more understanding.