[RESOLVED] mysql between tile location

Beffic

Okay so we just figured out my last problem but one of my other databases isn't layed out the same way. I understand that mysql does between references on dates but can it do it on a tile based location?

Such as:

$xtop = $char['posx'] + $char['foresight'];
$xbottom = $char['posx'] - $char['foresight'];
$ytop = $char['posy'] + $char['foresight'];
$ybottom = $char['posy'] - $char['foresight'];
$findOre = mysql_query("SELECT * FROM ore WHERE location BETWEEN '".$xbottom.", ".$ybottom."' AND '".$xtop.", ".$ytop."'");

Not sure if this is even possible. Haven't given it a try yet. So would this work or am I screwed and have to write out a long array?

bradgrafelman

Again, the syntax:

[i]expr[/i] BETWEEN [i]min[/i] AND [i]max[/i]

is equivalent to writing:

[i]expr[/i] >= [i]min[/i] AND [i]expr[/i] <= [i]max[/i]

Since MySQL doesn't have an X-Y position or "point" data type, you need to normalize your database so that the X coordinate and the Y coordinate are in separate columns. Otherwise, location is a multi-valued column which violates the First Normal Form of database design.

Beffic

Alright, I will go ahead and separate location into two columns instead then. Probably needed to happen in the first place anyways.

Thanks again.

bradgrafelman

Beffic;10999727 wrote:
Probably needed to happen in the first place anyways.

Yup. But again, that's only because MySQL doesn't know what a "point" is (which is more of a complex datatype composed of two simpler datatypes).

If you were using a different DBMS, the same might not be true. For example, PostgreSQL might be perfectly capable of having a single "location" column since it does indeed have a point datatype.