How to achieve this? DB structure & php

[yandoo]

Hiya,

 

I am trying to make a game using php/mysql. This a version 2 with many improvements but a complete redesign and change in rules.

 

I've been contemplating in my tiny brain and limited knowledge exactly how to solve a specific problem. I will try and explain what I am trying to achieve (in order to try and keep it simple I am explaining in terms of houses on a grid but in fact its planets, see attachment.)

 

I have a database with a table (only mentioning relevant one) that contains details of a house.

 

I have a grid map divided evenly into 8 squares by 15 squares.

Within many of these squares (not all) is a house.

From any starting house I wish display a path to any other house.

A condition of making this path is that a house must be 1 square away from another. (so moving across the map would require you to hop over each house 1 square away and so on. You can't hop more than 1 square and if there isn't a house next to the house your hopping from you cant move there)

 

For example as long as every hop to another house along the path is is owned by you, you can anywhere.

 

Displaying a path (which is css and html) is easy. But working out which each allowed hop is where the mysql and php come in. 

 

I have tried various ways to achieve this but don't think any of them is very good. The latest method is find each house that is 1 square away from the departure house. Then find those houses that are 1 square away from those houses and so on. So I thought nesting while loops could achieve this and creating a new table to store information of a house and its neighbour house.

 

For example table called neighbour and inserting a record for each house that is 1 square away from another house.

ID

House1 (departure house)

House2 (neighbour house)

 

Creating a simple query to establish where the 1 square away houses are from the departure house

Next I create a nested do while loop to echo out all the houses within 1 square away from each other:

$house = 'Frist House';
mysql_select_db($database_swb, $swb);
$test = "SELECT House2 FROM neighbour WHERE House1='$house' AND ";
$test = mysql_query($test, $swb) or die(mysql_error());
$row_test = mysql_fetch_assoc($test);
$totalRows_test = mysql_num_rows($test);


do{
echo $row_test['Name2']; echo'<br/>';

$nexthouse = $row_test['Name2'];
mysql_select_db($database_swb, $swb);
$test1 = "SELECT Name2 FROM neighbour WHERE Name1='$nexthouse'";
$test1 = mysql_query($test1, $swb) or die(mysql_error());
$row_test1 = mysql_fetch_assoc($test1);
$totalRows_test1 = mysql_num_rows($test1);

do{
echo $row_test1['Name2'];echo'<br/>';
}
while ($row_test1 = mysql_fetch_assoc($test1));
}
while ($row_test = mysql_fetch_assoc($test));

This method starts with the first house , searches for other houses 1 square away. With the houses it finds it then searches for other houses that are 1 square away from that.

 

As you can see this probably is a very poor method of trying to achieve this.

 

If I haven't explained very well please see the attachment image of the map and houses (planets) so you can see what I'm trying to achieve.

 

Do you know of anyother better way I can do this please??

 

Thank you for listening.

[mac_gyver]

your table should not have column names (nor should your code have variables) with 1,2,3,4.... in them. that's a sign you are treating the table like it is a spreadsheet and that results in more complicated code to manage the data. your table should have one row for each piece of data. that one row contains the information about the piece of data it holds. for your map/neighbour table, you would have columns for id, x-coordinate, y-coordinate, and owner_id.

 

you should never run queries inside of loops. for this map example, you would just retrieve all the rows using one query, joining this table to any other table(s) to get the related name... information, then store the rows into a two-dimensional array using the x and y coordinates as the array indexes. then as you are iterating over the grid when displaying the page, you can check if there is an entry in the array for any x,y coordinate pair and access the information in the array for that grid position.

[yandoo]

Thank you very much for your reply, Yes I totally see what you mean now with database structure.

 

I'm just a little unsure about retrieving that data. By joining both House and map/neighbour table I would know the house details and its coordinates. But how would I know which house is next to the first house? Could you elaborate a little please?

 

Thank you

[ignace]

It's not complete, and it needs a few more reviews but this might work:

 

class Grid {
  private $rows;
  private $cols;
  
  public function getHouseAt(Position $pos) {}
}

class House {
  private $pos;
  private $owner;
  
  public function updatePlayerPosition($player) {
    if (!$this->owner->equals($player)) {
      throw new InvalidArgumentException('Out, Out I tell you!');
    }
    $player->setPosition($this->pos);
  }
}

class Travel {
  private $distance;
  private $direction;
  
  public function getNewPosition($from) {}
}

class Position {
  private $x;
  private $y;
}

interface PositionAwareInterface {
  public function setPosition(Position $pos);
  public function getPosition();
}

class Player implements PositionAwareInterface {
  private $pos;
  
  public function goInside($house) {
    $house->updatePlayerPosition($this);
  }
}

class PlayerTravelService {
  public function move(Player $player, Travel $path) {
     switch ($path->getDistance()) {
       case 0:
         return; // no-op
       case 1:
         $this->doMove($player, $path);
         break;
       default:
         throw new UnexpectedValueException('Player can only travel along neighbouring squares.');
     }
  }
  
  private function doMove(Player $player, Travel $path) {
    $pos = $path->getNewPosition($player);
    if (!($house = $this->grid->getHouseAt($pos))) {
      throw new UnexpectedValueException('Travel aborted. Nothing there!');
    }
    
    $player->goInside($house);
  }
}

It tries to model your domain from a code point of view. I suppose CQRS would work really well here. You basically would have a TravelCommand with the coordinates and the TravelCommandHandler would handle the actual travel as it would update your model. Edited February 16, 2014 by ignace

[Barand]

Your neighbour table would be redundant as it can be derived by query

 

Given this data

mysql> SELECT * FROM house;
+---------+-------+-------+
| idhouse | gridx | gridy |
+---------+-------+-------+
|       1 |     1 | 0     |
|       2 |     3 | 0     |
|       3 |     2 | 1     |
|       4 |     4 | 1     |
|       5 |     2 | 2     |
|       6 |     3 | 2     |
|       7 |     5 | 2     |
+---------+-------+-------+

Then this query will produce the neighbours of each house

SELECT h1.idhouse as house
, h2.idhouse as neighbour
FROM house h1
    INNER JOIN house h2
        ON h1.idhouse <> h2.idhouse
        AND h2.gridx BETWEEN h1.gridx-1 AND h1.gridx+1
        AND h2.gridy BETWEEN h1.gridy-1 AND h1.gridy+1

+-------+-----------+
| house | neighbour |
+-------+-----------+
|     1 |         3 |
|     2 |         3 |
|     2 |         4 |
|     3 |         1 |
|     3 |         2 |
|     3 |         5 |
|     3 |         6 |
|     4 |         2 |
|     4 |         6 |
|     4 |         7 |
|     5 |         3 |
|     5 |         6 |
|     6 |         3 |
|     6 |         4 |
|     6 |         5 |
|     7 |         4 |
+-------+-----------+