javascript - 列出选定类别和价格范围内的产品

Question

我真的是 mysql 的新手，不能很好地理解解决我的问题的正确方法，所以如果有人有线索或解决方案，我们非常欢迎，我们开始吧。

我的数据库中有两个表:

产品:

• id_product
• 名称_产品
• 等...

产品类别:

• id_product
• 类别名称

当我保存具有多个类别的产品时，它在我的数据库中看起来像这样:

id_product  category_name
1           kids
1           men

在我的 html 中，我有一个这样的表单:

<div class="checkbox">
  <label><input type="checkbox" class="getcats"  value="Women">Women</label>
</div>
<div class="checkbox">
  <label><input type="checkbox" class="getcats" value="Men">Men</label>
</div>
<div class="checkbox">
  <label><input type="checkbox" class="getcats" value="Kids">Kids</label>
</div>
<div class="form-group" id="range">
  € <input type="number" min="0" id="min" name="min" class="form-control" placeholder="0"> 
  € <input type="number" min="0" id="max" name="max" class="form-control" placeholder="100">
 </div>
 <button class="boton" id="request" onclick="request()">Request</button>

然后在我进行 ajax 调用的 JS 文件中:

function request(){

  //get the values from price range
  var min = jQuery("#min").val();
  var max = jQuery("#max").val();

  //create array to save the selected categories
  var categories = [];

  //save all the selected categories into the array
  jQuery('.getcats').each(function(){

    if(jQuery(this).is(":checked")){
        categories.push(jQuery(this).val());
    }

  });

  jQuery.ajax({
        url: 'request.php',
        method: 'POST',
        data:{
            categories:categories,
            min:min,
            max:max
        },
        success: function(data){
            //on success, display data requested
        },
        dataType: 'json'
    }); // End of ajax call 

}

最后在我的 php 文件中:

<?php
header("Content-type: text/javascript");

require_once($_SERVER['DOCUMENT_ROOT'].'/../../config.php');
$conn = mysqli_connect($servername, $username, $password, $db);

//here should be some code to validate price range (min and max)

//after validation assign to variables
$min = $_POST["min"];
$max = $_POST["max"]; 

//here some code to validate selected categories (checkboxes)

//after validation assign to variable

$categories = $_POST["categories"];

$categoriesList = implode("','",$Intersectedresult); //$intersectedresult comes from array_intersect($categorias,$checkCategorias)
$resultList = "'".$categoriasList."'";

//Before I had this query working well but only when a product had 1 category, but now each product can have more than 1 category so I don't know how to make that query
$randomProducts = $conn->query("SELECT * FROM products WHERE category_name IN ($resultList) && product_price BETWEEN $min AND $max ORDER BY rand() LIMIT 3")

?>

我进行了一些研究，发现了一些术语，例如 INNER JOIN，但无法理解如何实现它才能执行如下操作:

$randomProducts = $conn->query("SELECT products.product_name, products.product_image, products.product_desc, products.product_url, products.product_price FROM products INNER JOIN product_category ON product_category.category_name=women, kids or men");

希望有人能给我push，非常感谢。

Answer 1

试试这个:

SELECT *
FROM products p
WHERE p.id_product IN (
 SELECT pc.id_product
 FROM product_category pc
 AND pc.category_name IN ($resultList)
)
AND p.product_price BETWEEN $min AND $max ORDER BY rand() LIMIT 3