Mysql Get products by size using php

Question

Using Filter option to get products by their sizes.

This is the product database structure:

id | size
------------
 1 | S,M
 2 | S,M,L
 3 | S
 4 | M,L

This is the Shirtsize database structure:

id | size
------------
 1 | S
 2 | M
 3 | L

This is the code of input value get for filter

<div class="list-group">
  <h3>Size</h3>
    <?php
       $query = "SELECT DISTINCT(shirtsize) FROM shirtsize ORDER BY id DESC";
       $statement = $connect->prepare($query);
       $statement->execute();
       $result = $statement->fetchAll();
       foreach($result as $row)
       {
        ?>
       <div class="list-group-item checkbox">
        <label>
          <input type="checkbox" class="common_selector size" value="<?php echo $row['shirtsize']; ?>"> 
           <?php echo $row['shirtsize']; ?>
        </label>
       </div>
    <?php
      }
     ?>  
</div>

This code to get the values from database:

if(isset($_POST["size"]))
{
    $size_filter = implode("','", $_POST["size"]);

    if (is_array($size_filter))
    {
        foreach ($size_filter as $value)
        {
           $query .= "AND size like('%".$value."%')";
        }
    }
    else{
    $query .= "AND size like('%".$size_filter."%')";
    }
}

In here i have database like s,m,l like this and post data also look like this s,m i tried this way to get values but the result is null.. I am not good in english so if any inconvinient sorry for that guys.

score 1 · Answer 1 · answered Mar 11 '20 at 06:05

To handle your current data model directly from MySQL would probably require some SQL olympics. Instead, I will offer a better normalized data structure:

id | size
------------
 1 | S
 1 | M
 2 | S
 2 | M
 2 | L
 3 | S
 4 | M
 4 | L

Now if you want to get the distinct sizes, you only need:

SELECT DISTINCT(size) FROM shirtsize;

If you want to get a count of sizes per id, just use:

SELECT id, COUNT(*) AS cnt FROM shirtsize GROUP BY id;

In general, never store CSV data in a SQL table.

I stored values of sizes in product table – Krish Mar 11 '20 at 06:08 — Krish, Mar 11 '20 at 06:08

score 1 · Answer 2 · answered Mar 11 '20 at 06:29

1

First of all, I double @Tim Biegeleisen to recommend you to normilize your data.
However, you can achieve the result by using FIND_IN_SET() function instead of LIKE.

SELECT * 
FROM `shirtsize` 
WHERE FIND_IN_SET('S', `size`)

The function returns 1-based position of the string in the string list. Or NULL if string not found.

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

answered Mar 11 '20 at 06:29

artoodetoo

918
10
55

I want to get products of S,M,L – Krish Mar 11 '20 at 06:57
Then you have to use several find_in_set() joined by AND operation – artoodetoo Mar 11 '20 at 13:45
The `like` operator can be wrong if the context is longer than the reference substring. Test 'XXL' vs. '%L%' or '%XL%'. In the same time, find_in_set() searches for exact match of substring only! – artoodetoo Mar 11 '20 at 13:53

Krish · Answer 3 · 2020-03-12T07:46:50.233

0

Thanks guys i got an answer

SELECT * FROM products WHERE status = '1' AND size like('%L%') OR size like('%M%')

or

SELECT size from `products` WHERE size rlike "L|M"

edited Mar 12 '20 at 07:46

answered Mar 11 '20 at 07:04

Krish

79
6

What if you have size equal to 'L,XXL' and looking for 'XL'. I guess it would false positive result with LIKE operation. Use FIND_IN_SET() instead, it's safe. – artoodetoo Mar 11 '20 at 13:48

Mysql Get products by size using php

3 Answers3