Fetch mysql with multiple values from single row

Question

i have a filter functionality with php and jquery/ajax which my table with products looks like thist:

|id|status|name|colors_id|
-------------------
| 1|  1   | toy| 1,4,7   | <-- this are id`s of few filters, red,blue,etc...

My sql looks like that:

$query = "SELECT * FROM products WHERE status=1";
if(isset($_POST["color"])){
    $color_filter = implode(",", $_POST["color"]);
    $query .= "
     AND color_id IN(".implode(",", $_POST["color"]).")"; 
}

When i filter i put an echo on query and result is this

SELECT * FROM products WHERE status = 1  AND color_id IN(1,4,7)

Bassically user check 3 colors and i want to go in database and fetch all rows which have 1,4,7 and display them.

My html looks like this:

<div class="sidebar-widget color-widgets">
    <h6>FILTER BY COLOR </h6>
        <ul>
            <li>
                <input type="checkbox" class="common_selector color" id="white" value="1">
                  <label for="white">white</label>
            </li>
            <li>
                <input type="checkbox" class="common_selector color" id="red" value="7">
                <label for="red">red</label>
            </li>
            <li>
                 <input type="checkbox" class="common_selector color" id="yellow" value="2">
                 <label for="yellow">yellow</label>
            </li>
            <li>
                 <input type="checkbox" class="common_selector color" id="blue" value="4">
                 <label for="blue">blue</label>
            </li>
        </ul>
    </div>

I if check 1,4 is getting me only the first in this case is 1 and doesnt find 4. (I know about security is a project for learning ajax with mysql and php, next step is PDO)

Now you see why a proper database design does not include columns with comma delimited keys in — RiggsFolly, Jul 15 '20 at 16:49
Do you can give me an advice for an alternative?thanks, i hope have a nice day. — , Jul 15 '20 at 16:51
you can create a new table "colors" that links your first table to each coolors_id, where each row has 3 columns: ID - IDfirstTable - idColor. in your example you would have 3 rows in this new table, one for each colors_id (1,4,7) like: row1: 1 - 1 -1, row2: 2 -1 -4, row3: 3 - 1 -7 — Hugo, Jul 15 '20 at 17:05

Buczek · Answer 1 · 2020-07-15T17:21:19.517

0

Hi (do not waste time to jquery) not ajax but clear js fetch() and FormData for sending data and files

Sql see here: https://pl.wikibooks.org/wiki/PHP/Biblioteka_PDO

And sql injection prevent https://doc.bccnsoft.com/docs/php-docs-7-en/pdostatement.execute.html

Regards

Learn from the best ;)

https://www.youtube.com/channel/UC29ju8bIPH5as8OGnQzwJyA

Your table should looks:

toys:
id|status|name
1|1|Toy1
2|1|Toy2

toy_colors:
id|rf_toy_id|color_id
1|1|1
2|1|3
3|2|5
4|2|1

select * from toy_colors left join toys ON toy_colors.rf_toy_id =    toy.id where toy.status = 1

And with 3 tables color table (not tested)

colors:
id|name
1|Red
3|Gold
5|Silver

select * from toy_colors left join toys ON toy_colors.rf_toy_id =    toy.id LEFT JOIN colors ON colors.id = toy_colors.color_id where toy.status = 1

:)

edited Jul 15 '20 at 17:21

answered Jul 15 '20 at 16:54

Buczek

11
2

I will try this method because i have already a table where i keep all colors when i save product. – Jul 15 '20 at 17:15
Sample with color table – Buczek Jul 15 '20 at 17:21
Is something wrong there, i tested your solution and is retriving me 2 products when i filter like this one: 'select * from toy_colors left join toys ON toy_colors.rf_toy_id = toy.id where toy.status = 1 GROUP BY toy_colors.id AND toy_colors.id IN(4)' Is retriving me 2 products 1 have toy_colors id 4 and another one which doesnt have id 4... why is giving me 2 products, thank you for help. – Jul 15 '20 at 20:19

score 0 · Answer 2 · answered Jul 15 '20 at 17:01

0

Do you need the exact match as per color_id in your table or partial matching will also return row?

Better you create another table where color id will be saved linking main table. Something similar to below main_table_id | color_id 11 1|2 1|3 2|2 2|3

etc.

Then you can use JOIN to retrieve your expected results.

answered Jul 15 '20 at 17:01

Maftahur Rahman

100
5

exact match because one product can have multiple colors – Jul 15 '20 at 17:15

Fetch mysql with multiple values from single row

2 Answers2