MySQL - BETWEEN and Negative numbers... AGH!!

[intellix]

Languages: MySQL 5.0.67, PHP 5
Ok so, im making a game for my final year project at uni and one thing is killing me... Each player is placed on a map square on a map which is assigned a co-ordinate (X,Y) which range from -100 to 100 (40,401 in total). It was working fine when I was randomly selecting a co-ordinate for X and Y, then doing a UPDATE map table WHERE X= the random X and Y = the random Y.

Then I tried to make it so each square on the grid is a random map_terrain type... where when im filling the table, it will fill the table randomly but to a percentage of how many of the terrain I want to appear. one type should random at around 33% of the map for example (the type I want users to be able to settle on)

Now with that! I need to randomly select minimum X, max X, minimum Y and Max Y and check the database to see if a record exists between the two randomly generated numbers WHERE map_terrain = 1. (As now I cannot directly pick a X and Y as its not certain it will be of map terrain 1).

I was storing the co-ordinates in the database as VARCHAR as I could then store the negative character before(not sure if this is the problem). In the registration process this is what happens (post bits of the code):

--------------
if ($usercount >= 0 && $usercount <= 50){
$minx = rand(0,50);
$maxx = rand(0,50);
$miny = rand(0,50);
$maxy = rand(0,50);

case "sw":
$min_x = "-".$minx;
$max_x = "-".$maxx;
$min_y = "-".$miny;
$max_y = "-".$maxy;
break;

$selectmap = "SELECT map_x, map_y, map_id FROM map WHERE map_x BETWEEN ".$min_x." AND ".$max_y." AND map_y BETWEEN ".$min_y." AND ".$max_y." AND map_terrain = '1' LIMIT 1"; //limit one result so I only get one row that the village should be created on
--------------

The annoying thing is that it SOMETIMES works... when it brings up an error, I try the query in MyPHPAdmin which will return ZERO rows as what happened. But then I start removing, adding the single quotes and randomly how its done, it'll work...
So I try making sure the first BETWEEN value is lower than the second... SOMETIMES WORKS!?! I try a BETWEEN where the number is lowest then highest regardless of the negative number... SOMETIMES WORKS!!
Then I try vice versa... again the same result... AGH!

Anyone got any ideas? I've got another page which actually displays the Map,this seems to work flawlessly, no matter what BETWEEN value you run through it... STRANGE!!

I would greatly appreciate your help to understand this problem, I've tried googling around and I seem to pull up a few posts on people struggling with latitude and longitude values with BETWEEN which is kinda the same thing but smaller numbers. Thanks

 

[SimonCHere]

Trying to do betweens on a varchar field will cause your problems.
if, in a table, you had :
'-10'
'-1'
'1'
'5'
and did
where col between '-1' and '3'
result will be '-10','-1','1'
Not what you want.

However if you miss out the ' ' marks. It will try to convert one or the other into the others type and it may or may not work depending on which way it did it.

Change the numbers into number fields and try again.

Hope that helps.

 

[GravyMonster]

Is there no way in MySQL to explicitly convert a value of one type to another?

In MSSQL it'd just be...

... WHERE CAST(Map_X AS int(4)) BETWEEN -10 AND 1

 

[SimonCHere]

Is there no way in MySQL to explicitly convert a value of one type to another?

In MSSQL it'd just be...

... WHERE CAST(Map_X AS int(4)) BETWEEN -10 AND 1

That should work, or possibly convert()
But he's still storing signed numbers in a varchar field with isn't good.
Especially if it's for a project, it should be correct.

Simon

 

[intellix]

thanks for the help guys, your help is more than enough for me to figure it out. Making the changes now... can't quite remember why I had them as VARCHAR... I haven't quite recorded much and its all gonna be from my head remembering what I did when it comes to the writeup lol

Thanks