php - 如何避免mysql中随机查询结果重复

Question

我需要从表中选择 4 个随机 ID，但问题是它们有时会重复，如何避免这种情况？我曾尝试使用 DISTINCT，但没有结果，这里是代码。至于 CMS，我使用的是 opencart。

<h1>similar products</h1>
<?php
$id = $this->customer->getCustomerGroupId();
$cur_jur = $this->db->query("SELECT `value` FROM `" . DB_PREFIX . "currency` WHERE currency_id = '1'");
$cur_fiz = $this->db->query("SELECT `value` FROM `" . DB_PREFIX . "currency` WHERE currency_id = '3'");
$max_symb = 25;
$fee_id = $product_id;
$product_sql_test_fee = $this->db->query("SELECT `category_id` FROM `" . DB_PREFIX . "product_to_category` WHERE `product_id`='".$fee_id."' ORDER BY RAND() LIMIT 0,10");
$feed_id = $product_sql_test_fee->row['category_id'];
$i=1;
$imax = 5;
$products_id = '';
while ($i < $imax) 
{
    $product_fee = $this->db->query("SELECT DISTINCT `product_id` FROM `" . DB_PREFIX . "product_to_category` WHERE `category_id`='".$feed_id."' AND NOT `product_id` = '".$products_id."'  GROUP BY `product_id` ORDER BY RAND() LIMIT 0,10");
    if(isset($product_fee->row['product_id']))
    {
        $pr_id[$i] = $product_fee->row['product_id'];
    }

    $pr_id_[$i] = $this->model_catalog_product->getProduct($pr_id[$i]);
    $products_id .= $pr_id[$i].',';
    $product_duplicate = explode(',',$products_id);
    if ($product_duplicate[$i] == $pr_id[$i])
    {

    }


    //if ($product_duplicate[$i] == $pr_id[$i]){ //echo 'Duplicate';
    //continue;} else {
    //foreach ($product_duplicate as $id) {
    //if ($id == $pr_id[$i]) continue;
    //}
    //$price_plus = $pr_id_[$i]['price'] + ($pr_id_[$i]['price'] * 0.20);
    //$price_minus = $pr_id_[$i]['price'] - ($pr_id_[$i]['price'] * 0.20);
    //$price_of_product = (int)$price / $cur_fiz->row['value'];
    //if ($price_of_product < $price_plus && $price_of_product > $price_minus) {
    //echo $pr_id[$i].'||'.$price_minus.'||'.$price_plus.'||'.$pr_id_[$i]['price'].'||'.$price_of_product.'<br/>';
    //}

    $price_plus = $pr_id_[$i]['price'] + ($pr_id_[$i]['price'] * 0.30);
    $price_minus = $pr_id_[$i]['price'] - ($pr_id_[$i]['price'] * 0.30);
    $price_of_product = (int)$price / $cur_fiz->row['value'];
    if ($price_of_product < $price_plus && $price_of_product > $price_minus   ) 
    {
    }
}
?> 

Answer 1

你必须使用group by category_id问题才会解决。

SELECT DISTINCT `product_id` FROM `" . DB_PREFIX . "product_to_category` WHERE `category_id`='".$feed_id."' AND NOT `product_id` = '".$products_id."'  GROUP BY `category_id`