Always check for error conditions. This generally means checking the return value of functions that might fail and consulting any error status functions (e.g. mysqli::error and mysqli::errno).
PHP scripts are limited to 30 seconds of execution time (though this can be adjusted). Without knowing whether or not the script is generating any errors, I'd guess this is your problem.
Running a separate query for each x and y coordinate is killing your script. Firstly, you should use prepared queries when running the same query repeatedly so that the server doesn't need to parse the query each time. The mysqli driver offers mysqli::prepare to create prepared statements, but I find PDO offers an interface that's simpler to use. Secondly, you don't need to run that many queries. You can simply fetch all locations within the bounds, specifying the order of the results, and fetch the next location from the result as needed. To make it more readable, the code should be refactored into functions, classes and methods.
LocalDB.php:
PHP Code:
// defined in some other script to isolate user credentials. Auto-closing connections (based on
//reference counting) left as an exercise for the reader
class LocalDB {
static private $dbs = array();
static function connect($db='dflt') {
if (! isset($dbs[$db])) {
$dbs[$db] = new PDO("mysql:host=localhost;dbname=$db", 'user', 'passwd');
$dbs[$db]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return $dbs[$db];
}
}
PHP Code:
<?php
function placeholder($x, $y) {
if ($x == 0) {
if ($y == 0) {
return '+';
} elseif(($y % 100)==0) {
return $y;
} else {
return '|';
}
} elseif ($y == 0) {
if (($x % 100) == 0) {
return $x;
} else {
return '-';
}
}
return '';
}
function printLocation($x, $y, $thing) {
$tile = placeholder($x, $y);
if ($loc['x'] == $x && $loc['y'] == $y) {
// the only legal children of a <tr> are <th> and <td>,
// so the other elements must go inside the table cell
echo "<td class='$loc_data[V_type]'><a href='http://w1.torpia.com/tile/tile/$x/$y' rel='$x|$y'>$tile</a>
<div class='balloonstyle' id='$x|$y'>$loc[village_name]- Owned by: $loc_data[Owner]($loc_data[V_type])</div></td>\n";
return True;
} else {
echo "<td>$tile</td>";
return False;
}
}
function nextLocation($locations) {
$loc = $locQuery->fetch();
if (False === $loc) {
$loc = array('x' => -1001, 'y' => 1001);
}
return $loc;
}
function printMap($locations, $bounds) {
?><table border="1"><?php
$loc = $locations->fetch();
for ($y=$bounds[':top']; $y >= $bounds[':bottom']; --$y) {
?><tr><?php
for ($x=$bounds[':left']; $x <= $bounds[':right']; ++$x) {
if (printLocation($x, $y, $loc)) {
$loc = nextLocation($locations);
}
}
?></tr><?php
}
?></table><?php
}
$bounds = array(':left' => -1000, ':right' => 1000, ':bottom' => -1000, ':top' => 1000);
try {
$db=LocalDB::connection();
$locQuery = $db->prepare('SELECT `V_type`,`Owner`,`village_name`, `Corrd_X` as x, `Coord_Y` as y
FROM locations
WHERE `Coord_X` BETWEEN :left AND :right
AND `Corrd_Y` BETWEEN :bottom AND :top
ORDER BY Corrd_Y DESC, Corrd_X');
$locQuery->setFetchMode(PDO::FETCH_ASSOC);
$locQuery->execute($bounds);
printMap($locQuery, $bounds);
} catch (PDOException $exc) {
echo "I had a problem with the database, so I couldn't get the map. The problem has been logged, and we'll look into it.";
error_log($exc);
...
}
Better still would be to create Map and Location classes that would hide the DB access. Display could be handled by those classes (with display methods), or you could have separate view classes to handle display. Set the fetch mode on the query to PDO::FETCH_CLASS and have the query return Location instances.
PHP Code:
class Location {
function isAt($x, $y);
function display();
}
class Map {
protected $bounds_;
// initialize a map with bounds given by $bounds
function __construct__($bounds);
// return all locations within the bounds
protected function locations() {
...
$locations->setFetchMode(PDO::FETCH_CLASS, 'Location', array());
...
}
function display();
}
2Likes
LinkBack URL
About LinkBacks
Reply With Quote
