MySQL BD random record preventing consecutive equal records

Question

MySQL BD random record preventing consecutive equal records

Navigation

#1 by (1 votes)
#2 by (1 votes)

6

I have a database where I want to get a random record. I can do this perfectly with:

$query = "SELECT * FROM 'mytable' ORDER BY RAND()

The problem is that the database contains the order of entry of the records of the jockeys in the competition and no jock can run the course consecutively. I have the following table:

+----------------+---------+-----+
| jumper         | horse   |  id |
+----------------+---------+------
| Pedro          | gfs     |   1 |
| Gustavo        | psg     |   2 |
| Breno          | sdwed   |   3 |
| bruno          | sdsd    |   4 |
| Carlos         | powkd   |   5 |
| Andrea         | linda   |   6 |
| Gustavo        | handara |   7 |
+----------------+---------+-----+

I have to prevent the jockey Gustavo from being drawn to the course consecutively because he would have to change horses very quickly. In fact, if it is raffled, it can only be raffled again after three other competitors. I would appreciate any ideas on how to do this.

php mysql random

asked by anonymous 27.02.2015 / 05:04

2 answers

Problem with mcrypt function * Image Cutting

score 1 · Answer 1

I agree with the comments that point to a solution via application and not via DB. But if you want to do it right inside the DB, I suggest creating a new column that will store something like DRAFT! With this, you can use a WHERE in your query looking only for DRAWING! = TRUE. The problem with this solution is that you would have to keep marking and unchecking the jockeys drawn.

As I do not know the structure of your bank, if you have another table with the races and their participants, you can also use this information (instead of creating a new column in the jockey table). In this solution, I imagine it would be the case to do a JOIN of the tables, filter out the last run, and randomly pick up more N runners than left over (using LIMIT N). In this case, you have to remember to use the DISTINCT (id) for the jockeys before making the "draw".

score 1 · Answer 2

One of the form is:

Create a new column for control, I created one with the name "back" The query looks like this:

 SELECT * FROM 'mytable'
 WHERE volta = 0  
 OR (SELECT COUNT(volta) FROM mytable
 WHERE volta = 1) >= 3
 ORDER BY RAND()
 LIMIT 1;

Then after applying an UPDATE mytable SET back = 1 WHERE id =?;

If you need to do everything by the database, then you will need to create a procedure, then you save the id a variable, from the update, and only then return the select to the application using the variable as a filter

I hope to have helped, good luck!