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)
2
Answers
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:
And with 3 tables color table (not tested)
🙂
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.