🔒 Locked

[web] Php+javascript+canvas+map loading

Started by Black Knight Sep 14, 2010 at 11:35 AM 17 replies 3.7k views

Original Post

Sep 14, 2010 at 11:35 AM

Old topic:
http://www.gamedev.net/community/forums/topic.asp?topic_id=572233

So I have made some progress on my browser game and now I want to store maps in a mysql database(still no idea how to do that either).

This link can paint tiles on a map :

Map Editor

The tiles that are available are stored in a database in a table. They can be modified here :

Tile Editor

Now what I'm thinking is adding a save map button to the map editor and it will take all the tiles in the map (40x20 right now but will probably be bigger later on) and somehow create a new table in the database or add all the tile info to a table. I have no idea how to add the maps to the database. Should I create a table per map or store each map as a row of a table? What about variable length stuff for example there might be buildings on the map which will be added by the editor.

Open for suggestions :)

Cygnus_X

Sep 14, 2010 at 12:51 PM

I think I'd have one table called MAPS, and give it the following columns:

MapID | X | Y | TileID

Then, you could query the database by saying:

Select * from Maps where MapID = '$MapID'

Then you could do a simple loop through X, Y, outputting the TileType (found via TileID) as you go.

Black Knight

Sep 14, 2010 at 1:34 PM

But isnt MapID | X | Y | TileID storing only 1 tile? One map will have around 3600 tiles.

Edit: Or are you telling something like this ,

MAPS TABLE :

MapID | X | Y | TileID

1 , 0,0,1
1, 1,0,2
1, 2,0,1
1, 3,0,5
1, 4,0,7
1, 5,0,2
1, 6,0,3
1, 7,0,2
...

So there will be 1 row for each tile totaling 3600 rows for a map all having 1 for MapID and different x,y, and tile ids values.

Cygnus_X

Sep 14, 2010 at 1:40 PM

Yes, 3600 Rows. The X, Y just gives the coordinates of the tile. The TileID gives you the tile type (ie, water, land, forest).

You'd have something like:

1 1 1 1 (ie, Map 1, x = 1, y = 1, tileid = 1 (plains))
1 1 2 1 (ie, Map 1, x = 1, y = 2, tileid = 1 (plains))
1 2 1 2 (ie, Map 1, x = 2, y = 1, tileid = 2 (forest))
1 2 2 3 (ie, Map 1, x = 2, y = 2, tileid = 3 (water))

The above would be for a 2x2 grid space.

It shouldn't take much time to loop through even a 3600 row tile set and place the tiles on a grid.

jolid

Sep 14, 2010 at 4:47 PM

You could shorten the database by selecting a "default" background and/or coming up with a simple system for large sections of the same background (it would depend on your regular patterns).

I didn't follow the old link, but if your maps aren't going to change often, you can simply generate the map and save the image (instead of generating it each time). Then your database will only be a long version for editing and its makeup won't matter (much) because you rarely load the map that way. Saving will simultaneously update the database and replace the old map image.

As to your main question: depending on your database, different structures will be faster -- I'll leave recommendations to someone who does more with different DBs, since I don't have any first-hand knowledge of best practices with lots of DB software. Don't be afraid to create multiple systems and benchmark them to see what works best for you.

Black Knight

Sep 14, 2010 at 5:33 PM

So I have all my tiles in a &#106avascript array and I have added a button to my map editor which is basically this :

<button id="command_savemap" onClick="parent.location='saveMap.php'"><img width=32 height=32 src="images/save.png"/></button>

When it is clicked it runs the saveMap.php script which looks like this right now :

<?phpinclude "db_connect";$connection = dbConnect();//save the data from javascript array to map_tile_info table in mysqlmysql_close($connection);// return back to mapEditorHeader('Location: mapEditor.php');exit();?>

Problem is I don't know how to pass the array that has 3600 tiles from &#106avascript to php. If I can do that I can make a for loop inside the php script and run 3600 insert queries :)

Cygnus_X

Sep 14, 2010 at 5:57 PM

If you use jQuery, I believe you can do it in one line.

$.post("saveMap.php", { 'postvarname[]': myarrayvar });

If you want to alert the user that the page has been saved, use:

$.post("saveMap.php", { 'postvarname[]': myarrayvar },
function(data){
alert("data);
});

In the second example, you just need to echo "Save was successful" or "Save Failed", and it will get sent to function(data) and outputted.

You might also require a click event to set this in motion...

Black Knight

Sep 14, 2010 at 6:25 PM

I have never used jQuery so I am trying to do it with plain html/&#106avascript/php right now.

I am trying to post the data by a form to php but I don't know how to put the &#106avascript array into the form. Here is what I tried : <div class="source"><pre> <form name="input" action="saveMap.php" method ="post"> <button type="submit" title="Save Map" name="tiles[]" value=g_TileData > <img width=32 height=32 src="images/save.png"/> </button> </form> </pre></div> This runs the saveMap.php file but I only get a 1 element array with g_TileData in it I guess its sending it as a string, I don't know how to refer to the JS variable g_TileData.

Cygnus_X

Sep 14, 2010 at 7:36 PM

Your code won't work. I see what you're trying to do, but you have no data in "value=g_TileData" Its literally just passing the string 'g_TileData' to your script.

This is a rather nasty work around....

So, I'm going to recommend again jQuery. Just go to jQuery.com, download their file, and insert it into the folder that contains your &#106avascript code. Then make a reference to it in your html:

Black Knight

Sep 14, 2010 at 7:42 PM

Haha yea I went the painfull way and found a page on the web :

http://www.hscripts.com/tutorials/php/jsArrayToPHP.php

I managed to get my array from JS to php by converting it to a string and putting it in a hidden input of a form.

The form looks like this :

<form name="input" action="saveMap.php" method ="post" onSubmit=setValue()>				<input id="hiddenArray" name="tileData" type=hidden>				<input type=submit>			</form>

So when you hit the submit button it calls setValue function,set value converts the &#106avascript array which contains tile objects into arrays:

	function setValue()	{		var arrIds = []		var tiles = g_GameObjectManager.gameObjects;		for(var i=0; i<tiles.length; i++)		{			arrIds.push(tiles.id);						}		var array = arrIds.toString();			document.getElementById('hiddenArray').value = array;			}

Now the value of the hidden field contains 3200 ids in the form "3,2,1,5,6,6,...,23".

This gets to the php script as $_POST['tileData'] and the php script turns it into a php array with explode :

$tiles = $_POST['tileData'];//echo $tiles;$tok = explode(',',$tiles);print_r($tok);

I wonder how inefficient this is though, all this string converting is probably slow as hell.

Black Knight

Sep 14, 2010 at 11:13 PM

Is there anyway to update a column in a table with a single query ?
I have the arrays I need but I need to make a loop that goes through 3200 elements and call UPDATE which I think makes the save map functions slow.

Here is the code that runs when save map is clicked :

<?phpinclude "db_connect.php";$connection = dbConnect();//save the data from javascript array to map_tile_info table in mysql$tileIds = $_POST['tileIds'];$tileXpos = $_POST['tileXpos'];$tileYpos = $_POST['tileYpos'];$tileIdsArray = explode(',',$tileIds);$tileXposArray = explode(',',$tileXpos);$tileYposArray = explode(',',$tileYpos);// check if the table already has the rows$query = "SELECT * FROM map_tile_info";$result = mysql_query($query) or die(mysql_error());  $onlyModify = FALSE;// if there is something then we just need to modifyif( mysql_fetch_array( $result ))	$onlyModify = TRUE;		// now add each row again if($onlyModify)	for($i=0; $i<count($tileIdsArray); $i++)	{		//this id tell which type of tile this map cell is		$currentTileID = $tileIdsArray[$i];				//position of the map cell		$currentX = $tileXposArray[$i];		$currentY = $tileYposArray[$i];				//always 1 for now there is only one map		$mapID = 1;		//$insertQuery = "UPDATE map_tile_info SET `mapid`=$mapID,`x`=$currentX,`y`=$currentY,`tileid`=$currentTileID WHERE id=$i+1";		$insertQuery = "UPDATE map_tile_info SET `tileid`=$currentTileID WHERE id=$i+1";		mysql_query($insertQuery) or die(mysql_error());  	}else	for($i=0; $i<count($tileIdsArray); $i++)	{		//this id tell which type of tile this map cell is		$currentTileID = $tileIdsArray[$i];				//position of the map cell		$currentX = $tileXposArray[$i];		$currentY = $tileYposArray[$i];				//always 1 for now there is only one map		$mapID = 1;		$insertQuery = "INSERT INTO map_tile_info VALUES ('',$mapID,$currentX,$currentY,$currentTileID)";					mysql_query($insertQuery) or die(mysql_error());  	}mysql_close($connection);// return back to mapeditorHeader('Location: mapeditor.php');exit();?>

krez

Sep 14, 2010 at 11:33 PM

You can insert multiple rows of data with one INSERT. Build your query as one giant string, each row of values can be followed by a comma and the next row:

INSERT INTO table VALUES (a1, b1, c1), (a2, b2, c2), ... (a_n, b_n, c_n);

You can also throw DELAYED after the INSERT keyword under certain circumstances, and have the query return immediately which should speed things up from the user's perspective. It only works for certain configurations, won't show the rows immediately for other queries, and could potentially lose your data if you don't make sure you read the linked page carefully (but if you are using MySQL that probably isn't a concern).

Cygnus_X

Sep 15, 2010 at 7:58 AM

Your code is about as efficient as it gets. My only recommendations are...

Instead of:

$query = "SELECT * FROM map_tile_info";
$result = mysql_query($query) or die(mysql_error());

$onlyModify = FALSE;
// if there is something then we just need to modify
if( mysql_fetch_array( $result ))
$onlyModify = TRUE;

// now add each row again
if($onlyModify)

I'd recommend:

$query = ("Select * from mapt_tile_info where MapID = '$MapID');
$Result = mysql_fetch_array($query)
if(mysql_num_rows($Result) > )
{
... update only
}
else
{
....insert
}

Also, you 'may' be able to save the data in your hidden input as an array by setting the name to name="MyData[]" You can then loop through this in PHP with:

$Count = count($tileIdsArray);

while($count > 0)
{
$TileID = array_shift($tileIDsArray);
$X = array_shift($XArray);
$Y = array_shift($YArray);

$Count = count($tileIDsArray);

..do your updates, inserts here
}

May be a little bit faster.

Also, stacking your insert statements per Krez's comments will help some if your execution time is really bad.

Black Knight

Sep 15, 2010 at 11:41 AM

Can I use one giant query to update too?

UPDATE map_tile_info SET (mapid=$mapID,x=$currentX[0],y=$currentY[0],tileid=$currentTileID[0]),(mapid=$mapID,x=$currentX[1],y=$currentY[1],tileid=$currentTileID[1]),
...;

Cygnus_X

Sep 15, 2010 at 12:20 PM

The only way I would know to do this would be as follows:

update MyTable set TileType = '$TileType' where (x = 1 and y=1) or (x=2 and y=2) or (x=1 and y=2) and MapID = '$TheMapID')

Of course, you'd have to do a lot of coding to make this query.... probably just as efficient to do several update statements. Be sure to index your MapID, X and Y values. This should help in selecting a certain tile type as it will b-tree the values of those columns (making them easier to find for queries and updates, but at the cost of adding some overhead with each update).

Black Knight

Sep 15, 2010 at 12:35 PM

I managed the generate an update query like this :

UPDATE mytable SET title = CASE
WHEN id = 1 THEN tileid[0]
WHEN id = 2 THEN tileid[1]
...
END
WHERE id IN (1,2,...)

It requires a bit more code to generate the query but it is faster than running 3200 queries.

$query = "UPDATE map_tile_info SET `tileid`= CASE ";$queryInPart ="END WHERE id IN(";for($i=0; $i<count($tileIdsArray); $i++){	$query.= "WHEN id=".($i+1)." THEN ".$tileIdsArray[$i]." ";	if ($i ==count($tileIdsArray)-1)		$queryInPart.= ($i+1);	else		$queryInPart.= ($i+1).",";}$queryInPart.=")";$query = $query.$queryInPart;

Black Knight

Sep 15, 2010 at 12:55 PM

I also tried setting the array by chaning name to tileIds[] but I can't seem to set the data from &#106avascript.

var arrTileIds = [];

var tiles = g_GameObjectManager.gameObjects;
for(var i=0; i{
arrTileIds.push(tiles.id);
}

document.getElementById('tileIdsArray').value = arrTileIds;

This should set the value of the tileIdsArray to the &#106avascript array right? When I get it in php with $tileIds = $_POST['tileIds']; echo count($tileIds); It only prints 1 instead of 3200.

Cygnus_X

Sep 15, 2010 at 1:58 PM

I did a little reading, and I believe I was wrong on an earlier comment. If you have a series of checkboxes, you can name all of them as name="MyCheckboxVar[]", and it will store the values in an array that can get posted to PHP. However, it appears you cannot simply append the value of a hidden input with the value of a &#106avascript array. For this, you must serialize, like you are doing, then explode the text on the PHP end. This appears to be as efficient as your script will be. How long does it take to save a file?

Black Knight

Sep 15, 2010 at 3:14 PM

Right now it takes around 4-5 seconds to save 3200 tiles, it was around 15-20 seconds before with the for loop.

You can test it at :
map editor

Hit the green S symbol to save the map.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.