从具有PHP的数据库中选择一个随机行，但不要选择相等的行 [英] Select a random row, but not an equal one from a database with PHP

问题描述

我的问题几乎是不言自明的，但是我不能完全解决它以使其尽可能高效。

我想从MySQL数据库中选择一个随机条目。我希望它尽可能快且尽可能高效（这始终是目标，不是吗？）。当我选择该行时，我想选择另一行，但与之前的行不同。如果我选择10行，我希望第11行与其他所有行都不同（让我们说unique :)）。但是，当我用完所有行时，我想报告错误。

My problem is pretty much self explanatory but I cant quite work it out to make it as efficient as possible.
I want to select a random entry from a MySQL database. I want it to be as fast as possible and as efficient as possible (that's always the goal, isn't it?). When I select that row I want to select another row, but not the same as the one before. If I select 10 rows I want the 11th row to be different from all others (lets say unique :) ). But when I run out of rows I want to "report an error".

了解问题的实质。我在MySQL上使用PHP。我有一个包含已选择标题的输入数组。然后，我获得了数据库中所有项目的计数，因此我知道可以循环遍历最大次数。让我们粘贴代码以查看我们在这里处理的内容。

To get to the heart of the problem. I am using PHP with MySQL. I have an input array containing titles which have already been selected. Then I get the count of all items in the database so I know how many times can I "loop through max". Lets paste the code to see what we're dealing with here.

try
{
    $db = new PDO("mysql:host=localhost;dbname=xxxxx;charset=utf8", "xxxx", "xxxx");

    $played = explode(":;:", $_POST['items']); //All already selected items are in $_POST separated by :;:

    $sql = "SELECT count(id) AS count FROM table"; //Lets get the total count of items

    $query = $db->prepare($sql);
    $query->execute();
    $result = $query->fetch(PDO::FETCH_ASSOC);

    $count = $result['count']; //There we are...
    $i = 0; //Index counter so we dont exceed records.. well kinda (more on that below)

    do //do while because we want something to be selected first
    {
        $sql = "SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM table"; //From here

        $query = $db->prepare($sql);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_ASSOC);
        $offset = $result['offset'];

        $sql = "SELECT itemXML FROM table LIMIT $offset, 1";

        $query = $db->prepare($sql);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_ASSOC); //To here is some code to randomly select a record "as efficiently as possible"..

        $output = Array();

        $xml = simplexml_load_string($result['itemXML']);

        $i++;
    } while (in_array($xml->{"title"}, $played) && $i < $count); //While record title is in array and while we have not exceeded the total number of records (that's the logic but it isint that simple is it?)

    if ($i >= $count)
    {
        die("400"); //Just a random status code which I parse with the client.
    }

    $itemArr = Array("whatever" => $xml->{"whatever-attr"}, "title" => $xml->{"title"});
    array_push($output, $itemArr); Lets push this to out array

    echo json_encode($output); //Aaaaand finally lets print out the results
}
catch (Exception $e) //If anything went wrong lets notify our client so he can respond properly
{
    $output = Array("error" => $e->getMessage());
    die(json_encode($output));
}

是的。问题是，如果有10条记录，那么9已选择行，并且索引计数器 $ i 大于或等于10，并且随机记录都在数组中。然后，我们应该选择了一行，但没有选择。

Yes well.. The problem is that WHAT IF there are 10 records, 9 rows have been selected and the index counter $i gets bigger or equal 10 and random records are all in the array. Then we have one row that should have been selected but its not.

该如何解决？

如果我对它的解释不够好，让我知道我会努力的。

And how do I fix this? Your help will be much appreciated!
If I didnt explain it well enough let me know an I will try harder.

推荐答案

假设您已经在下表中填充了数据：

Let's say you have the following table filled with data already:

TABLE mydata
  id INT AUTOINCREMENT PRIMARYKEY
  name VARCAHAR
  ...

我们为一些不是真的随机映射：

And we create the following table for some not-really-random mapping:

TABLE shufflemap
  id INT AUTOINCREMENT PRIMARYKEY
  data_id INT UNIQUEINDEX

我们执行以下操作：

$rs = $dbh->query('SELECT id FROM mydata');
shuffle($rs);
foreach($rs as $data_id) {
    $dbh->query('INSERT INTO shufflemap (data_id) VALUES (?)', array($data_id));
}

现在如果要添加行怎么办？您可以截断表并重新运行上面的代码，或者：

Now what if we want to add rows? You can either TRUNCATE the table and re-run the above code, or:

$my_new_id = 1234; //the ID of the new row inserted into `mydata`
$rs = $dbh->query('SELECT COUNT(*) AS 'count' from shufflemap');
$target = rand(0,$rs[0]['count']);
$rs = $dbh->query('SELECT id, data_id FROM shufflemap LIMIT ?,1', array($target));
$swap_id = $rs[0]['id'];
$swap_data_id = $rs[0]['data_id'];
$dbh->query('UPDATE shufflemap SET data_id = ? WHERE id = ?', array($my_new_id, $swap_id));
$dbh->query('INSERT INTO shufflemap (data_id) VALUES (?)', array($swap_data_id));

以合理有效的方式从shufflemap表中随机选择了哪个条目，将data_id替换为新的

Which picks on random entry from the shufflemap table in a reasonably efficient manner, replaces the data_id with the new one, and tacks the old one onto the end of the table.

使用这种方式，您可以拥有看似随机的数据而无需重复，并且仍然可以利用所有数据通过在JOIN，子查询或其他任何可以使用的方法中使用shufflemap表在表中找到适当的索引。

Using this manner you can have your seemingly-random data with no repetitions, and still make use of all the proper indexes in your table by using the shufflemap table in JOINs, subqueries, or whatever else you can come up with.

比方说，mydata表具有一个字段，指示每个字段与哪个客户端或用户相关联，即：

Let's say that the mydata table has a field indicating which client or user each field is associated with, ie:

TABLE mydata
  id INT AUTOINCREMENT PRIMARYKEY
  client_id INT
  name VARCAHAR
  ...

可以通过以下方法检索仅该客户数据的混排列表：

The shuffled listing of only that client's data can be retrieved by:

SELECT d.*
FROM mydata d INNER JOIN shufflemap s
  ON d.id = s.data_id
WHERE client_id = ?
ORDER BY s.id

不包括已经播放过的物品列表吗？

Excluding a list of already-played items?

SELECT d.*
FROM mydata d INNER JOIN shufflemap s
  ON d.id = s.data_id
WHERE client_id = ?
  AND d.id NOT IN(?,?,?,...)
ORDER BY s.id

这篇关于从具有PHP的数据库中选择一个随机行，但不要选择相等的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！