Question posted in
You can find the official documentation here.

Fetch mysql with multiple values from single row – Jquery ajax

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)

Tags:

2

Answers


  1. 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

    🙂

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search