Mysql BD random record preventing consecutive equal records

Asked

Viewed 533 times

6

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

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

The problem is that the database contains the entry order of the jockeys' records in the competition and no jockey 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 run the course consecutively because he would have to change horses very quickly. In fact, if it is drawn, it can only be drawn again after three other competitors. I would appreciate any ideas on how to do this.

php mysql random
  • 3

    Gustavo, this is Stackoverflow in English. Please translate your question.

  • @mutlei lack the title...

  • 1

    @Jorgeb. Ready to go

  • 1

    Gustavo wants to do this only with SQL? or using PHP? What have you tried so far?

  • 4

    Why not manage this directly in the application? You can do this much more simply.

  • Hello, after drawing what do you do? Save to a table? Which one? If not how do you know that you have already been drawn?

Show 1 more comment

2 answers

1

One of the ways is:

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

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

Ai later in the application of an UPDATE mytable SET back = 1 WHERE id = ?;

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

I hope I helped, good luck!

1

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

Since I don’t know the structure of your bench, if you have another table with the races and your 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 race, and catch randomly more N runners than were left (using LIMIT N). In this case, remember to use DISTINCT(id) for the jockeys before making the "draw".

Browser other questions tagged php mysql random

You are not signed in. Login or sign up in order to post.