diff options
| author | Patrick Davison <snapwilliam@gmail.com> | 2013-02-14 00:42:29 -0800 |
|---|---|---|
| committer | Patrick Davison <snapwilliam@gmail.com> | 2013-02-14 00:42:29 -0800 |
| commit | f65cf9406397ac688ec4060ba070e172af0cd90b (patch) | |
| tree | 391b668b47b1237711b5424722d0be07fb4f9509 /ajax/scores.ajax.php | |
| parent | a5d4ffe96373a24c398a47165e7079e30735467f (diff) | |
| download | pathery-f65cf9406397ac688ec4060ba070e172af0cd90b.tar.xz | |
Lots'n Lots of work on the scoreboard...
Diffstat (limited to 'ajax/scores.ajax.php')
| -rw-r--r-- | ajax/scores.ajax.php | 108 |
1 files changed, 98 insertions, 10 deletions
diff --git a/ajax/scores.ajax.php b/ajax/scores.ajax.php index b95d22e..7ba25d0 100644 --- a/ajax/scores.ajax.php +++ b/ajax/scores.ajax.php @@ -8,6 +8,7 @@ $userID = $_SESSION['userID']; require('../includes/maps.php');
require('../includes/mapclass.php');
require('../includes/datas.php');
+include_once('../includes/sqli.php');
if ($_GET['getmap'] == 'true') {
$mapID = $_GET['mapID'];
@@ -28,17 +29,105 @@ if ($_GET['getmaplist'] == 'true') { echo getMapNavigation($todaysScoreMaps);
}
+if (isset($_GET['getmapidsbydate'])) {
+ //TODO: SANATIZE
+ $requestDate = $_GET['getmapidsbydate'];
+ echo json_encode(getMapIDsByDate($requestDate));
+}
+
+// Here is that SQL for you
+// SELECT correctMapIds.mapType, maps.ID, maps.code
+// FROM maps
+// INNER JOIN
+// (
+ // SELECT MIN(mapOfTheDay.mapId) AS mapId, mapOfTheDay.mapType
+ // FROM `mapOfTheDay`
+ // INNER JOIN
+ // (
+ // SELECT mapType, MIN(mapExpireTime) AS mapExpireTime
+ // FROM mapOfTheDay
+ // WHERE mapExpireTime > DATE_ADD(CURDATE(), INTERVAL -1 DAY)
+ // GROUP BY mapType
+ // ) AS expireTimes ON mapOfTheDay.mapType = expireTimes.mapType
+ // AND mapOfTheDay.mapExpireTime = expireTimes.mapExpireTime
+ // GROUP BY mapOfTheDay.mapType
+// ) AS correctMapIds ON maps.ID = correctMapIds.mapId
+
+//TODO: SANITIZE.
+function getMapIDsByDate($date) {
+ global $mysqli;
+$sql = "SELECT MIN(mapOfTheDay.mapId) AS mapId
+FROM `mapOfTheDay`
+INNER JOIN
+(
+ SELECT mapType, MIN(mapExpireTime) AS mapExpireTime
+ FROM mapOfTheDay
+ WHERE mapExpireTime > ?
+ GROUP BY mapType
+) AS expireTimes ON mapOfTheDay.mapType = expireTimes.mapType
+ AND mapOfTheDay.mapExpireTime = expireTimes.mapExpireTime
+GROUP BY mapOfTheDay.mapType";
+
+$stmt = $mysqli->prepare($sql);
+$stmt->bind_param("s", $date);
+$stmt->execute();
+
+ $stmt->bind_result($mapID);
+ /* fetch values */
+ while ($stmt->fetch()) {
+ $ids[] = $mapID;
+ }
+
+ return $ids;
+
+var_dump($ids);
+
+
+while ($myrow[] = $result->fetch_assoc()) {}
+
+ $result = mysql_query($sql) or die(mysql_error());
+ if (mysql_num_rows($result) == 0) return -1;
+ global $mapNamesByType;
+ $r = array();
+ while($data[] = mysql_fetch_array($result)) {
+ $mapID = $data['mapID'];
+ $mapType = $data['mapType'];
+ $map = new map($code);
+ if ($map->name == '')
+ $map->name = $mapNamesByType[$mapType];
+ $r[$mapID] = $map;
+ }
+ return $myrow;
+}
function getMapsPlayed($daysAgo) {
- $sql = "
- SELECT `mapID`, `code`, `mapType`
- FROM `mapOfTheDay`
- INNER JOIN `maps` ON `mapID` = maps.ID
- WHERE DATE_ADD(CURDATE(), INTERVAL -$daysAgo DAY) =
- mapDate AND
- `mapType` IN (1, 2, 3, 4)
- ";
+ // $sql = "
+ // SELECT `mapID`, `code`, `mapType`
+ // FROM `mapOfTheDay`
+ // INNER JOIN `maps` ON `mapID` = maps.ID
+ // WHERE DATE_ADD(CURDATE(), INTERVAL -$daysAgo DAY) =
+ // mapDate AND
+ // `mapType` IN (1, 2, 3, 4, 5)
+ // ";
+
+ $sql = "SELECT correctMapIds.mapType, maps.ID as mapID, maps.code
+FROM maps
+INNER JOIN
+(
+ SELECT MIN(mapOfTheDay.mapId) AS mapId, mapOfTheDay.mapType
+ FROM `mapOfTheDay`
+ INNER JOIN
+ (
+ SELECT mapType, MIN(mapExpireTime) AS mapExpireTime
+ FROM mapOfTheDay
+ WHERE mapExpireTime > DATE_ADD(CURDATE(), INTERVAL -$daysAgo DAY)
+ GROUP BY mapType
+ ) AS expireTimes ON mapOfTheDay.mapType = expireTimes.mapType
+ AND mapOfTheDay.mapExpireTime = expireTimes.mapExpireTime
+ GROUP BY mapOfTheDay.mapType
+) AS correctMapIds ON maps.ID = correctMapIds.mapId";
+
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) == 0)
return -1;
@@ -49,8 +138,7 @@ function getMapsPlayed($daysAgo) { $mapID = $data['mapID'];
$mapType = $data['mapType'];
$map = new map($code);
- if ($map->name == '')
- $map->name = $mapNamesByType[$mapType];
+ if ($map->name == '') $map->name = $mapNamesByType[$mapType];
$r[$mapID] = $map;
}
|