Something Wrong with My Logic? - User IDs or Coordinates?

[Thauwa]

Hi all!

I hope that you are all having a great time with PHP.

Well I am too.

 

Problem:

I want all users who register to a mock site to have a unique, yet random ID. This ID is used to give the user virtual coordinates. So what I thought was to give the users a 'coordinate' like  x=123, y=123 .  i.e. (123,123)

One of my goals is to calculate the virtual distance between two users. I figured out the real-world solution to this problem (I hope that it is correct!). It is as follows:

User 1: (123,123), User 2: (111,111).
Distance: (((123-111)^2)+((123-111)^2)))^1/2

 

Now to my problem... Is it possible for me to arrange a list of user-records for each user in order of the 'closest' (shortest distance) users to those farthest? Without having my databases ruined with too much entries, that is...

 

My other problem is as to how to make the 'coordinates' unique to each user. All I know about that is about assigning random numbers for each, and this is not that efficient.

 

If you think that my entire approach to the situation is bonkers  , do let me know.

 

I thank you for any help in advance!

 

Regards,

Thauwa

[dcro2]

Well, random numbers sound fine to me honestly.

//somewhat pseudo code
do {
$x = rand(0,200);
$y = rand(0,200);
} while ( num_rows "SELECT * FROM users WHERE x='$x' AND y='$y'" > 0 )
query "INSERT INTO users (x,y) VALUES ('$x', '$y')"

As for the distance sorting, I would guess something like

$userX = 123;
$userY = 123;
query "SELECT ABS( SQRT( POW($userX - x, 2) + POW($userY - y, 2) ) ) as distance FROM users SORT BY distance ASC";

Don't know if that's actually valid SQL but it's a start.

[Thauwa]

Thank you so much for your reply.

 

I got the hang of it, and I used your code and tried some changes on it, but to no avail.

I am really grateful for putting me on the right track though...

 

I get a 'not valid MySQL' source error.

 

Here is the code I used:

 

$result2 = mysql_query("SELECT * FROM users WHERE username='$usernamee'");
$row = mysql_fetch_assoc($result2) ;
$x_coordinate = $row['x_coordinate'] ;
$y_coordinate = $row['y_coordinate'] ;

$result3 = mysql_query ("SELECT ABS( SQRT( POW($x_coordinate - x_coordinate, 2) + POW($y_coordinate - y_coordinate, 2) ) ) AS distance FROM users SORT BY distance ASC");

while ($riw = mysql_fetch_assoc($result3)) {
echo $riw['username'];;
}

 

The random number inserting part goes fine.

I even tried switching the ends of the AS part (so that 'distance' comes in front of AS and the math part after).

And I understand the logic behind what you gave me (and I am grateful for that).

 

I guess I need more advice...

 

Thanks in advance,

Thauwa

[batwimp]

Try ORDER BY instead of SORT BY

[Psycho]

One of the problems you are going to face is that there will be no simple way to assign unique coordinates to users and ensure there are duplicates. Using 0-200 for the X and Y coordinates gives you a total of 40,401 unique coordinates. You could generate a set of random coordinates, check if they are already used and, if so, discard and try again. If you were to only support a small number of users relative to the total available this should work. But, the more users you have relative to the total available the more that process will be a problem.

 

Another option would be to create a table of all available coordinates and then remove them as they are assigned. If you are only going to have 40K options that is doable. But, if your total options was to increase significantly that wouldn't be advisable either. So, you need to strike a balance.

 

As for

[Thauwa]

Thanks batwimp and psycho, for your replies.

@batwimp - Thank you for the code suggestion. I tried that. Still am getting the error. This is the first time I'm considering math within a mysql_query... any tips?

@psycho - Thanks for your advice. I will try out the generating-and-discarding-if-taken method.

 

Anyone got any hints as to how I could make the query work?

The error I get is:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in

 

Thanks and Regards,

Thauwa

[Thauwa]

Hi all!

 

I got the code to work.

 

mysql_query ("SELECT * FROM users ORDER BY (SQRT(POW('$x_coordinate' - x_coordinate, 2) + POW('$y_coordinate' - y_coordinate, 2) ) ) ASC");

 

I verified the math by manually calculating the entries in my database and checking them against the list generated by the php.

I thank all for helping me out of this. I sincerely do.

 

Thanks and Regards,

Thauwa