如何防止在从sql表中选择随机行时选择已删除的ID - How to prevent selecting deleted IDs when selecting random row from sql table

How to prevent selecting deleted IDs when selecting random row from sql table

本文关键字：选择删除 ID 随机 sql 何防止 | 更新日期: 2023-09-27

我想根据产品数据库中的唯一id从中随机选择行。我需要每页3-4个随机产品。。。

这就是我开始的方式

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `products` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `products` WHERE id = $offset LIMIT 1" );
$n=mysql_num_rows($result);
if ($n>0){
    while($row = mysql_fetch_array($result)){ 
        $id = $row["id"];
        $cod_produs = $row["cod_produs"];
        $nume_produs = $row["nume_produs"];
        $titlul_paginii = $row["titlul_paginii"];
        $nume_intermediar = str_replace (" ", "-", $nume_produs);
        $nou_nume_produs = strtolower($nume_intermediar);
        $detalii = $row["detalii"];
        $seo_descriere = $row["seo_descriere"];
        $materiale = $row["materiale"];
        $numere = $row["numere"];
        $pret = $row["pret"];
        $categorie = $row["categorie"];
        $subcategorie = $row["subcategorie"];
        $data_adaugare = strftime("%b %d, %Y", strtotime($row["data_adaugare"]));
    }
echo ("The id of the product is ".$id." and his name is ".$nume_produs);
    };
  ?>

问题是，我的代码会返回我随时间删除的ID。。。我该如何防止这样做？

我如何一次返回3或4个唯一的id，并对更好的方法持开放态度？

通常的方法是：

SELECT * FROM `table` ORDER BY RAND() LIMIT 0,1;

您可以在从MySQL数据库表中选择随机记录中阅读解释。

对于您的代码：

mysql_query( " SELECT * FROM `products` ORDER BY RAND() LIMIT 0,3" );

已编辑到期OP评论

然后，您可以获得数据的子集。修改您的代码：

$offset_result = mysql_query( "SELECT FLOOR(MAX(id) * RAND() - 1000) AS `offset` 
                               FROM `products` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `products` 
                         WHERE id between $offset and $offset + 1000
                         ORDER BY RAND() 
                         LIMIT 0,3" );

快速解决方案是获得3个随机ID：

$randoms_id = '-1';
for($i=0;$i<3;$i++) {
  $id_result = mysql_query( " SELECT id FROM `products` 
                              WHERE id >= 
                                  (SELECT FLOOR( MAX(id) * RAND()) 
                                   FROM `products` ) 
                              ORDER BY id LIMIT 1;");
  $id_row = mysql_fetch_object( $id_result ); 
  $id = $id_row->id;
  $randoms_id = $randoms_id . ',' . $id;
}
$result = mysql_query( " SELECT * FROM `products` 
                         WHERE id in ($randoms_id);")

如果脚本中有错误，请执行以下操作：

$offset_result = mysql_query("SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM `products`");
$offset_row = mysql_fetch_object($offset_result); 
$offset = $offset_row->offset;
$result = mysql_query("SELECT * FROM `products` LIMIT $offset, 1");
$n=mysql_num_rows($result);

但这将只返回一个记录。您可以获得连续的记录，也可以多次运行。

$offset_result = mysql_query("SELECT COUNT(*) AS total FROM `products`");
$offset_row = mysql_fetch_object($offset_result); 
$total = (($offset_row->total) - 1);
$range_arr = range(0, $total);
shuffle($range_arr);
$off_1 = $range_arr[0];
$off_2 = $range_arr[1];
$off_3 = $range_arr[2];
$result = mysql_query(" (SELECT * FROM `products` LIMIT $off_1, 1)
                        UNION
                        (SELECT * FROM `products` LIMIT $off_2, 1)
                        UNION
                        (SELECT * FROM `products` LIMIT $off_3, 1)");
$n=mysql_num_rows($result);