Hello.

There was a task - to extract a pseudo-ordinal record from the database, however, its ID should not fall into the range of available ones (for example, those used earlier).

The result is such an "algorithm".

$ids = explode(',',$_SESSION['vars']); if (count($ids) == 4){ $_SESSION['vars'] = '0'; die(); } $get = $this->db->query("SELECT COUNT(*) AS `count` FROM `quests`"); $get = $get->fetch(PDO::FETCH_ASSOC); $cnt = $get['count']; while(!$flag){ $rnd = rand(1,$cnt); if (in_array($rnd,$ids)){ $flag = false; echo 'Элемент с ID '.$rnd.' существует! <br>'; }else{ $flag = true; } } $get = $this->db->query("SELECT * FROM `quests` WHERE `id` = ".$rnd); $get = $get->fetch(PDO::FETCH_ASSOC); $vars.=','.$rnd; $_SESSION['vars'] = $vars;

`The question is: is it possible to implement this as using only SQL?

  • one
    The only question is in how many and where you have an id which is not difficult to be. Normal select * from quests where id not in(1,5,10,76) order by rand() limit 1 will give the desired result in principle, but is this optimal in your case - Mike
  • @Mike, for clarity, the code has been attached. The IDs of the records used are in the session in a simple line. Because records are retrieved alternately one by one, the number of them is no longer important, so that we would not be able to monitor anything extra. And thanks, I will try your option. - Vitaliy RS
  • one
    I asked about the number, because the length of the request is in principle limited and the use of hundreds of IDs inside not in can be a problem. And order by rand() will read the entire table. Fetching a specific record by a previously known ID is an order of magnitude faster. True, your current code requires that all IDs be strictly in the table from 1 to the number of records - Mike
  • @Mike, in not in will be no more than 14 ID. There are a couple of hundred entries in the table. And how is it limited? What does he require? If the count of records in the table is initially extracted, and on the basis of it, a random number is generated as a limit. I missed something, apparently? - Vitaliy RS
  • 2
    Well, 14 is not scary and a couple hundred in general is not critical. Although slower than your current algorithm. And in your current problem there will be if in the database there are records 1,2,3, and then immediately 7,8,9 you will count () only 6 means you can not generate numbers 7,8,9. And if rand returns the number 5, the number search cycle will be completed, and the record with ID = 5 will not be found in the database. So, to protect against holes in numbering, it is better to take max (id) instead of count () and select to do it inside the search cycle for a suitable number. - Mike

1 answer 1

Judging by the code, you keep the previously received Id separated by commas in the context of the session, and then you get a new one. If you do not have more than 15 processed IDs in a session, why not get all 15 IDs from the database in one request and store them in a session?

Those. request of the form:

 SELECT GROUP_CONCAT(Id SEPARATOR ',') FROM quests ORDER BY rand() LIMIT 30 /*с запасом*/

We get 15 random entries from quests. write them into the context of the session in the "raw", then one by one ID select and write to the processed. Without turning to the database for a new Id! The truth will have to turn to other fields in the quests table, but by index - and this is a trifle compared to sorting by rand ().

If suddenly we don’t have enough of these Id's in the session - make a new request after the next pack. Request type:

 SELECT GROUP_CONCAT(Id SEPARATOR ',') FROM quests WHERE Id NOT IN (/*список обработанных Id'ов*/) ORDER BY rand() LIMIT 30

And then in a circle, if the session is “very long”. Thus, provided that the session handles the records smaller than the pack size, the sorting of ORDER BY rand () will be performed only once.