From d7b0c04459a6ec42a37255c38d67cea14fb60e61 Mon Sep 17 00:00:00 2001 From: BlueRaja Date: Sat, 26 Jan 2013 03:30:45 -0600 Subject: Moved usersData table to users table, added a few more columns to users (currently unused) --- api/app.php | 8 +++----- db updates.sql | 20 ++++++++++++++++++-- includes/chats.php | 8 +++----- includes/datas.php | 33 +++++++++------------------------ index.php | 8 ++++---- pages/achievements.php | 30 +++++++++--------------------- pages/challenge.php | 8 ++++---- pages/leaderboard.php | 16 ++++++---------- pages/tutorial.php | 8 ++++---- 9 files changed, 60 insertions(+), 79 deletions(-) diff --git a/api/app.php b/api/app.php index 806e4ae..959d960 100644 --- a/api/app.php +++ b/api/app.php @@ -145,16 +145,14 @@ function getScoresB($mapID, $pageNumber = 1, $pageDivide = 10) { users.displayName as display, solutions.moves as moves, users.ID as ID, - IfNull(userData.displayColor, '#cccccc') as displayColor, - IfNull(userData.wallColor, '#666666') as wallColor, - IfNull(userData.wallEmblem, 'blank.png') as wallEmblem, + users.displayColor, + users.wallColor, + users.wallEmblem, solutions.dateModified as cdate FROM `users` JOIN `solutions` ON users.ID = solutions.userID - LEFT JOIN `userData` - ON users.ID = userData.userID WHERE solutions.mapID = '$mapID' ORDER BY solutions.moves DESC, solutions.dateModified ASC, solutions.ID DESC "; diff --git a/db updates.sql b/db updates.sql index bb3bcb3..3f2c593 100644 --- a/db updates.sql +++ b/db updates.sql @@ -36,8 +36,24 @@ ALTER TABLE `mapOfTheDay` ADD INDEX ( `mapExpireTime` , `mapType` ); -- The new code relies on these being unique, so we'll enforce that through indices. Make sure you -- verify these ARE unique in the DB before adding these indices (they weren't in my test DB) -- Should probably run these two commands by themselves, one at a time -ALTER TABLE `maps` ADD UNIQUE ( `challengeTier` , `challengeSuborder`); +ALTER TABLE `challengeMaps` ADD UNIQUE ( `challengeTier` , `challengeSuborder`); ALTER TABLE `challenges` DROP INDEX `mapID` , -ADD UNIQUE `mapID` ( `mapID` , `ordering` ); \ No newline at end of file +ADD UNIQUE `mapID` ( `mapID` , `ordering` ); + +-- Move shit from userData table to users +ALTER TABLE `users` ADD `displayColor` VARCHAR( 7 ) NOT NULL DEFAULT '#cccccc' , +ADD `wallColor` VARCHAR( 7 ) NOT NULL DEFAULT '#666666' , +ADD `wallEmblem` VARCHAR( 64 ) NOT NULL DEFAULT 'blank.png'; + +UPDATE users, userData +SET users.displayColor = userData.displayColor, + users.wallColor = userData.wallColor, + users.wallEmblem = userData.wallEmblem +WHERE users.ID = userData.userID; + +DROP TABLE `userData`; + +ALTER TABLE `users` ADD `dateChatGaggedUntil` DATETIME NULL , +ADD `isOptedOutOfEmails` BOOLEAN NOT NULL DEFAULT '0'; \ No newline at end of file diff --git a/includes/chats.php b/includes/chats.php index 56c88d1..4ecf6ce 100644 --- a/includes/chats.php +++ b/includes/chats.php @@ -37,12 +37,10 @@ function getChat($startID = 0) { SELECT chat.ID, chat.userID, chat.message, chat.dateSent, users.displayName, - userData.displayColor, - userData.wallColor, - userData.wallEmblem + users.displayColor, + users.wallColor, + users.wallEmblem FROM `chat` - LEFT JOIN `userData` - ON chat.userID = userData.userID LEFT JOIN `users` ON chat.userID = users.ID WHERE chat.ID > '$startID' diff --git a/includes/datas.php b/includes/datas.php index e8e24a3..c6a0788 100644 --- a/includes/datas.php +++ b/includes/datas.php @@ -17,16 +17,14 @@ function topScores($mapid, $top = 5, $bottom = 0) { users.displayName as display, solutions.moves as m, users.ID as ID, - userData.displayColor, - userData.wallColor, - userData.wallEmblem, + users.displayColor, + users.wallColor, + users.wallEmblem, solutions.dateModified as cdate FROM `users` JOIN `solutions` ON users.ID = solutions.userID - LEFT JOIN `userData` - ON users.ID = userData.userID WHERE solutions.mapID = '$mapid' ORDER BY solutions.moves DESC, solutions.dateModified ASC LIMIT $bottom, $top @@ -43,16 +41,6 @@ function topScores($mapid, $top = 5, $bottom = 0) { $output .= ""; while (list($diff, $display, $moves, $userID, $displayColor, $wallColor, $wallEmblem, $cdate) = mysql_fetch_row($result)) { $i++; - if (!isset($displayColor)) { - $sql = "INSERT INTO `userData` (`userID`, `displayColor`, `wallColor`) - VALUES ('$userID', '#cccccc', '#666666')"; - mysql_query($sql); - $displayColor = '#cccccc'; - $wallColor = '#666666'; - $wallEmblem = 'blank.png'; - } - if ($wallEmblem == '') - $wallEmblem = 'blank.png'; //$cdate = date("g:i A (T)", strtotime($cdate)); $cdate = relativeTime(strtotime($cdate)); @@ -791,13 +779,12 @@ function getMembers($pageNumber = 1, $pageDivide = 50, $order = 'DESC', $orderBy users.totalMazes as totalMazes, users.totalWins as totalWins, users.totalTies as totalTies, - IfNull(userData.wallColor, '#666666') as wallColor, - IfNull(userData.displayColor, '#cccccc') as displayColor, - userData.wallEmblem as wallEmblem, + users.wallColor, + users.displayColor, + users.wallEmblem, users.datejoined as dateJoined, users.dateLogin as dateLogin FROM `users` - LEFT JOIN `userData` ON users.ID = userData.userID $sqlOrder LIMIT $limitTop, $pageDivide "; @@ -928,16 +915,14 @@ function getScores($mapID, $pageNumber = 1, $pageDivide = 10) { users.displayName as display, solutions.moves as moves, users.ID as ID, - IfNull(userData.displayColor, '#cccccc') as displayColor, - IfNull(userData.wallColor, '#666666') as wallColor, - IfNull(userData.wallEmblem, 'blank.png') as wallEmblem, + users.displayColor, + users.wallColor, + users.wallEmblem, solutions.dateModified as cdate FROM `users` JOIN `solutions` ON users.ID = solutions.userID - LEFT JOIN `userData` - ON users.ID = userData.userID WHERE solutions.mapID = '$mapID' ORDER BY solutions.moves DESC, solutions.dateModified ASC, solutions.ID DESC "; diff --git a/index.php b/index.php index 562b59b..7561071 100644 --- a/index.php +++ b/index.php @@ -30,10 +30,10 @@ if ($accepted) { include_once ('./includes/sqlEmbedded.php'); $sql = " SELECT - userData.wallColor, - userData.wallEmblem - FROM `userData` - WHERE userID = '$userID' "; + users.wallColor, + users.wallEmblem + FROM `users` + WHERE ID = '$userID' "; $result = mysql_query($sql); if ($result) list($wallColor, $wallEmblem) = mysql_fetch_row($result); diff --git a/pages/achievements.php b/pages/achievements.php index e3f9e8d..f2c5aba 100644 --- a/pages/achievements.php +++ b/pages/achievements.php @@ -225,16 +225,6 @@ if ($result) { //Validate selection and then update. if (isset($_POST['doupdate']) AND !$viewer) { - // Ensure the user is located in userData. - $sql = "SELECT * FROM `userData` - WHERE `userID` = '$userID'"; - $result = mysql_query($sql); - if (mysql_num_rows($result) == 0) { - $sql = "INSERT INTO `userData` (`userID`, `displayColor`, `wallColor`) - VALUES ('$userID', '#cccccc', '#666666')"; - mysql_query($sql); - } - //print_R($unlocks); $unlockIDC = $_POST['selectColorID']; $unlockColor = $unlocks[1][$unlockIDC]['value']; @@ -253,9 +243,9 @@ if (isset($_POST['doupdate']) AND !$viewer) { $unlockNameColor = '#cccccc'; if ($unlockColor == $_POST['selectColor']) { - $sql = "UPDATE `userData` + $sql = "UPDATE `users` SET `wallColor` = '$unlockColor' - WHERE `userID` = '$userID'"; + WHERE `ID` = '$userID'"; $result = mysql_query($sql); if (!$result) { echo "Error updating wall color"; @@ -267,9 +257,9 @@ if (isset($_POST['doupdate']) AND !$viewer) { } } if ($unlockEmblem == $_POST['selectEmblem']) { - $sql = "UPDATE `userData` + $sql = "UPDATE `users` SET `wallEmblem` = '$unlockEmblem' - WHERE `userID` = '$userID'"; + WHERE `ID` = '$userID'"; $result = mysql_query($sql); if (!$result) { echo "Error updating wall color"; @@ -282,9 +272,9 @@ if (isset($_POST['doupdate']) AND !$viewer) { } if ($unlockNameColor == $_POST['selectNameColor']) { - $sql = "UPDATE `userData` + $sql = "UPDATE `users` SET `displayColor` = '$unlockNameColor' - WHERE `userID` = '$userID'"; + WHERE `ID` = '$userID'"; $result = mysql_query($sql); if (!$result) { echo "Error updating wall color"; @@ -330,14 +320,12 @@ SELECT users.isAdmin, users.totalMoves, users.totalMazes AS totalSolutions, - IfNull(userData.wallColor, '#666666'), - IfNull(userData.displayColor, '#cccccc'), - userData.wallEmblem + users.wallColor, + users.displayColor, + users.wallEmblem FROM `users` LEFT JOIN `solutions` ON users.ID = solutions.userID -LEFT JOIN `userData` - ON users.ID = userData.userID WHERE users.ID = '$userID' GROUP BY solutions.userID "; diff --git a/pages/challenge.php b/pages/challenge.php index 84b401b..02c6f04 100644 --- a/pages/challenge.php +++ b/pages/challenge.php @@ -18,10 +18,10 @@ if ($accepted) { $sql = " SELECT - userData.wallColor, - userData.wallEmblem - FROM `userData` - WHERE userID = '$userID' "; + users.wallColor, + users.wallEmblem + FROM `users` + WHERE ID = '$userID' "; $result = mysql_query($sql); if ($result) list($wallColor, $wallEmblem) = mysql_fetch_row($result); diff --git a/pages/leaderboard.php b/pages/leaderboard.php index b5e3a76..cb9a029 100644 --- a/pages/leaderboard.php +++ b/pages/leaderboard.php @@ -106,9 +106,9 @@ function getStat ($type, $daysAgo = 1) { users.displayName as Name, SUM(solutions.moves) as Moves, timediff(MAX(dateModified), maps.dateCreated) as Timetaken, - userData.wallColor, - userData.wallEmblem, - userData.displayColor, + users.wallColor, + users.wallEmblem, + users.displayColor, users.ID as userID, SUM(CASE WHEN solutions.dateModified < CURDATE() THEN solutions.championPointsWorth @@ -118,8 +118,6 @@ function getStat ($type, $daysAgo = 1) { ON maps.ID = solutions.mapID JOIN `users` ON solutions.userID = users.ID - LEFT JOIN `userData` - ON users.ID = userData.userID WHERE $where GROUP BY solutions.userID ORDER BY Moves DESC, MAX(dateModified) ASC @@ -198,9 +196,9 @@ function displayPastMaze($mapType, $name, $daysAgo = 1) { users.displayName as Name, SUM(solutions.moves) as Moves, timediff(solutions.dateModified, maps.dateCreated) as Timetaken, - userData.wallColor, - userData.wallEmblem, - userData.displayColor, + users.wallColor, + users.wallEmblem, + users.displayColor, users.ID as userID, solutions.championPointsWorth FROM `maps` @@ -210,8 +208,6 @@ function displayPastMaze($mapType, $name, $daysAgo = 1) { ON solutions.userID = users.ID JOIN `mapOfTheDay` ON maps.ID = mapOfTheDay.mapID - LEFT JOIN `userData` - ON users.ID = userData.userID WHERE maps.ID = '$mapID' GROUP BY solutions.userID diff --git a/pages/tutorial.php b/pages/tutorial.php index 5208a57..88095da 100644 --- a/pages/tutorial.php +++ b/pages/tutorial.php @@ -18,10 +18,10 @@ if ($accepted) { $sql = " SELECT - userData.wallColor, - userData.wallEmblem - FROM `userData` - WHERE userID = '$userID' "; + users.wallColor, + users.wallEmblem + FROM `users` + WHERE ID = '$userID' "; $result = mysql_query($sql); if ($result) list($wallColor, $wallEmblem) = mysql_fetch_row($result); -- cgit v1.2.3