如何使这个PHP / MySQL游戏地图更有效率？

Question

I am currently working on a grid-based game map prototype. Each "tile" is 24px by 24px and the script draws out 31 x-based tiles and 21 y-based tiles for a total of 651 tiles on the screen.

The problem I'm having is that the way I have my script going, it's making a database call for every single tile that's drawn at every page refresh, meaning it's making 651 database calls to check what the tile is and the coordinates of the tile. To me this seems obscenely inefficient.

The x and y coordinates are stored in a session variable. When the user clicks one of the directional buttons below the map, it offsets the appropriate coordinate accordingly and redraws the map.

If I were to create a game based on the logic I'm using, and playing the number game let's hypothetically say I've got 100 players playing the game at any one time and they're all browsing through the map, I would have 65,100 database calls simultaneously. That can't be good for system resources.

I am not a noob when it comes to PHP/MySQL programming as I have created and launched a few different browser-based games, however, this is my first venture into generating a map and navigating around it.

The tile map can be viewed here:

http://fordserver.com/gamemap/

The actual code for this can be found here:

http://fordserver.com/gamemap/index.txt

Can anyone recommend a good way to get this running more smoothly? You'll notice that when using one of the directional buttons that the game can take quite awhile to generate the actual map, especially if you are just visiting it for the first time.

I've tried to think of different ways to use arrays and everything else, but really, the game needs to check the database for each individual tile as I plan to incorporate functions so that it shows where a specific user currently is, or a building, etc.

I appreciate any help I can get with this.

Ricky

Answer 1

I once implemented a simple game that also used a cartesian coordinate system and had rows of data in the database for each point. I got around it simply by placing a single call along these lines:

$xCord=34;
$xCord=16;
// Example data


select
    ID,
    tileImage,
    xcord,
    ycord,
    otherStuff
from
    mainLocationTable
where
    abs(xCord-$xCord)<15
    and abs(yCord-$yCord)<15

This will give you 15 tiles either side of the player location itself. If course you can customize this to suit your needs, but the principle is the same. If you store the x,y cartesian coordinates in the database, it's easy as pie to pull out all the data you need in a single query and go from there.

As you also store the data of the player location in a session, you can simply have the code itself do further checking for details when it comes to the tile that the player is on - or use a second call to get much more detailed information for a single location.