everyone. I wrote this code for filtering sql results. As you can see, I'm using an array of values to filter relevant brands of products. Variable $opts is dynamically populated via jQuery/AJAX. My question is: How can I modify this code (sql query) to filter results by brand name and for example color or size? I have two tables in the database, one is for products (id, name, description, color, picture, brand_name) and the another is for brands (id & brand_name). It's important to know that $color variable should also be an array, similar to $opts. What is the best way to do this? Should I use bind_param or something else?
<?php
include ("database.php");
$pdo = database::connect();
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] :$_GET['brand_id'];
$qMarks = str_repeat('?,', count($opts)-1). '?';
$statement = $pdo -> prepare("SELECT products.id, name, description, price, picture, color FROM products INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks)");
$statement -> execute($opts);
$results = $statement -> fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
While trying to figure out how to do this, I tried adding something like this:
$color = "white";
$statement = $pdo -> prepare("SELECT products.id, name, description,price, picture, color FROM products INNER JOIN brand ON brand_id = brand.id WHERE name IN ($qMarks) AND color LIKE $color");
OR this:
$color = array();
array_push($color,"bela");
$color1 = implode(",", $color);
$statement = $pdo -> prepare("SELECT pozivnice.id, ime, naslov, cena, slika, boja FROM pozivnice INNER JOIN brand ON brand_id = brand.id WHERE ime IN ($qMarks) AND WHERE boja IN $color1");
It doesn't seem to work... This is my testing site> http://www.nemchus.pe.hu/products.php?brand_id=retro