Moved usersData table to users table, added a few more columns to users (currently unused)

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 .= "</tr>";
 	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);