[MySql] range query

[menyo]

Hi,

I want to make a world map for a game but before i (can) do that i need to learn how to and what the possibilities are. Therefor i am building a test environment. But i'm already kinda stuck on my first query.

I have made this table called "world" with these columns:
systemID -> not really used yet as there is just 1 system/world.
orderID -> dunno if i really need this one as i could probably order my table from the xpos and ypos.
xpos
ypos
tileID -> refers to the primary key of the table where i store my tiles.

I have filled this table with 25 rows to represent a 5x5 grid. where all the outer tiles are 3 (water) the middle tile (3,3) is 2 (desert) and the rest are 1 (grass).

Now my query needs to select all the "tiles" around the players location. Unfortunately my query only returns the current position to me where i expect it should give me 9 values. Here's the code:

$xpos = 3; //this variable should get feeded by a $_GET or $_POST or something.
$ypos = 3; //this variable should get feeded by a $_GET or $_POST or something.

//build query
$mapquery = mysql_query ("
SELECT
tileID
FROM
world
WHERE
(xpos BETWEEN '$xpos - 1' AND '$xpos + 1')
AND
(ypos BETWEEN '$ypos - 1' AND '$ypos + 1')
") or die (mysql_error());

$mapgen = mysql_fetch_assoc($mapquery);

print_r ($mapgen);  //this only gives me the tile i'm currently on ->   Array ( [tileID] => 2 )

[PeejAvery]

It would be best if you calculate the minimum and maximum X and Y coordinates before the query. Then use those instead of your addition/subtraction within the SQL query.

$minX = $xpos - 1;
$maxX = $xpos + 1;
$minY = $ypos - 1;
$maxY = $ypos + 1; 

[menyo]

You might be right, but i found out that it actually works the way i did it without the ' marks at the BETWEEN command. Becaus if i do a num_rows on my query it actually returns 9.

The problem is probably that it overwrites the array[tileID] so is there a mysql function that prevents this from happening or should i write a function that puts it correctly into an array?

[PeejAvery]

You aren't even looping through the query return!

$mapIDs = array();
while ($row = mysql_fetch_object($mapquery)) {
  $mapIDs[] = $row->tileID;
}

print_r($mapIDs); 

[menyo]

Shame on me... i should have figured that out myself. Thanks for helping out!

In planning for today: figuring out how to get this data into my HTML file with javascript/AJAX. Could i for instance make a foreachloop like this:

<?php
$mapgen = array();
while ($row = mysql_fetch_object($mapquery)) {
  $mapgen[] = $row->tileID;
}

foreach ($mapgen as $tile) 
{
?>
    <div class="tile tile-<?php echo "$tile";?>"></div>
<?php
}
?>

in the PHP file and get this div output into my map container inside the html file? Or should i look to somehow get the array data into my HTML file and make the loop there to generate the map asynchronous with javascript/AJAX?

[PeejAvery]

You could...but then you're actually causing extra processing which doesn't need to be. Nor do you need to be using up extra system resources.

Rather than create the whole $mapgen array, simply output the <div> in the while() loop.