table without primary or unique key?

[madmenyo]

Hey guys!

 

I am wondering if it's necessary to give this table a primary or unique key. It's for storing tile map arrays in the DB, here is the table i need to create:

 

Table maps:

systemID - refers to the system the player is in

 

orderID - This is for ordering the tiles so i can fetch an array in the correct order. I could make this one the primary but as i have a map for each system i would like to have this start at 1 for each system.

 

xpos - horizontal position

 

ypos - vertical position

 

tileID - refers to the tile that needs to be shown

 

with these fields every row is unique, i would never call up a row with a unique or primary key but with the systemID and fetch it in order by orderID. If i make orderID primary and i have 2< maps i can't add to the first map so i would need another field with a unique/primary ID, is this required? What are the possibilities here?

 

tx.

 

[Mchl]

Since all rows in your table are distinct, a primary key for this table is a multi-column index created on all columns. It is not necessary to create such an index, but having it can speed up some opertaions.

[madmenyo]

How do i create a multi column index? Because without a unique key my DB gives a error like no key defined.

 

Also, what kinda operation would be faster? Like i said, i won't ever look up a query in this table by a primary key but by it's location (systemID), then see how they are ordered with (orderID) and select the tileID for display. So it looks like a unique key for each tile would be useless. Offcourse i want the most efficient method so if adding a unique key to every single tile in the game would speed up things i add that in, but i can't see how that would speed up things atm.

 

Thanks!

[madmenyo]

I created the multiple field key so no need to explain that.

 

Shame i can't edit my posts.....

[Mchl]

You have 10 minutes to edit your posts

 

Perhaps if you pasted an example of query, we could advise someting.

[madmenyo]

Mind, i'm still trying to get a global idea to HOW i'm about to get my game map to work for me. First i want to get my map to rebuild each time i move a tile. As there are no real tutorial on how to do this with mysql, PHP, javascript, HTML and AJAX i started up a little test environment.

 

I'm still fiddling around with how to build my query but it should look something like this:

 

$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 )

 

I have build my table "world" like this:

systemID -> not really used yet as there is just 1 system.

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).

 

 

[Mchl]

ORDER BY ypos, xpos

  should do the ordering thing.

An index on (xpos,ypos) could possibly do some good here. Check with EXPLAIN

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

[madmenyo]

I was planning on figuring that out later on, first i need to get more then one value into $mapgen.... i would not not to order my query if it only returns one value right?

[madmenyo]

I figured the query probably overwrites mapgen[tileID] therefor it just returns 1 value. If i do a num_rows it actually returns 9 so thats good.

 

Is there a mysql function that avoids rows being overwritten? or do i have to make a function that puts the data into a array?

[Mchl]

Didn't notice that

 

$mapgen[] = mysql_fetch_assoc($mapquery);