Unique random numbers

Monkofdoom

Hello,

I will try to explain my problem as cleary as possible but if i've missed something just let me know and ill get it added 🙂

I'm trying to create a simple (square) map running from 0 - 9 along both axis so the top left co-ordinate is (0,0) and the bottom right co-ordinate is (9,9).

When a new person registers an account they are given a unique random co-ordinate, for example (4,4). Both numbers are stored in a table - individually, the first number being in the field "xcoord" and the second in the field "ycoord".

Now a person can NOT be given the same set of co-ordinates as someone else, the x or the y co-ords may be the same but NOT both.

I realise you "could" just run a loop making random numbers and testing to see if they are already in use - but by the time the grid is nearly full the script could take a long time to pick a free co-ordinate.

Thank you for your time and reading this thread - Any help will be greatfully recieved

Monkofdoom

Roger_Ramjet

This is one of those things that is so simple when you see it.

Create a table with 1 column and populate it with the nos in your range (0-9).
Now you can use the power of sql to do it for you with a left join and rand ordering.

table nos
int

1
2
3
4
5
6
7
8
9


$xaxis = "SELECT int FROM nos LEFT JOIN people ON(nos.int=people.xcoord) 
WHERE people.xcoord IS NULL ORDER BY RAND LIMIT 1";

$yaxis = "SELECT int FROM nos LEFT JOIN people ON(nos.int=people.ycoord) 
WHERE people.ycoord IS NULL ORDER BY RAND LIMIT 1";

These 2 queries work as follows. The left join with is null returns all the nos that have not yet been used. It orders them randomly, and the limit 1 restricts the result to 1 row.

😃 😃 😃

Monkofdoom

Firstly thank you for your reply but it doesnt quite solve the problem - I was probably unclear in what i meant.

The code you stated will only give 10 co-ordinates such that you could generate (1,1) from it but then it wouldn't do (1,2) (1,3) etc..

The idea is once 100 people have created accounts there will be 100 unique co-ordinates.

Starting from (0,0) (0,1) (0,2) (0,3) .... finishing at (9,7) (9,8) (9,9)

Now i guess i could use your method and write out all 100 combinations and then just randomaly pick from them, removing it from the list after selection BUT The idea is to have several grids going at a time which would mena have several tables... Was hoping for an easier way (well better)

Roger_Ramjet

Well now. As is, of course, my queries wont solve all your problems. I was hoping you would see how to extend the principle to your needs.

The only unique index you can create on these is a joint index on the 2 columns.
To generate the equivalent in the base table you need to join it to itself - but join every row to every row:

$matrix = "SELECT nos.int AS xcoord, nos1.int AS ycoord FROM nos, nos AS nos1";

That will return 100 rows - the full matrix of x and y coordinates.

Now, to slect one that has not been used you do the left join to that.

$rand_xy = "SELECT m.xcoord, m.ycoord FROM 
   (SELECT nos.int AS xcoord, nos1.int AS ycoord FROM nos, nos AS nos1) AS m
   LEFT JOIN people p ON(m.xcoord=p.xcoord AND m.ycoord=p.ycoord)
   WHERE p.xcoord IS NULL AND p.ycoord IS NULL 
   ORDER BY RAND LIMIT 1";

The subquery in the FROM clause returns a table called m with the full matrix that you can join to just like any table you read from disk.

You can even dispense with the disk table containing the range 0-9 and just create a memory table in the query itself, but I thought that might confuse the issue for people.

Monkofdoom

Thank you hug

I did have to put () after the RAND but otherwise it worked fine, my map i alive - Yay 😃