CS71 – Ass1 – Finance – Part 5 – View users details – buy stock – change password

I am doing the Harvards building dynamic websites called CS-75 (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning 🙂 even if allot of it you may already know.

As from the previous post, most of this page is in similar in nature it is just displaying data to the user that was built up from the classes files.

So to buy some stock, what this page will do is wait for the user to type in a stock symbol and then use the stock class to get the data back from that symbol and then have another input that will allow the user to buy some of that stock, also if the user clicks on the link it will display at the bottom of the page new items about the stock itself.


BuyStock($_SESSION["username"], $stockID, $stockAmount);
	}
	
	if (isset($_REQUEST["searchSymbol"]))
	{
		if (strlen($_REQUEST["searchSymbol"]) >=3)
			$result = $theStock->GetStocksFromYahoo(strtoupper($_REQUEST["searchSymbol"]));
	}

	HTMLHeader("Buy some stock, search and buy");

	if (isset ($stockPurchased)) {
		echo "

"; if ($stockPurchased >0 ) echo "Stock was purchased ($stockID amount of $StockAmount total price of $stockPurchased each price was ". number_format($stockPurchased / $stockAmount,2).")"; else echo "Problem with purchasing the stock, please email the error above to the support team"; echo "

"; } ?>
" method="post" onsubmit="return CheckBuy()"/> 0) { echo "

{$_REQUEST["searchSymbol"]}

"; echo "The price of {$_REQUEST["searchSymbol"]} is $result, do you want to buy ?
Please enter how many and tick the box.
"; echo ""; echo ""; echo ""; echo ""; echo "

Or search for another stock

"; } else echo "

Not stock of that symbol - {$_REQUEST["searchSymbol"]}

"; } ?>
Search Symbol :

Your present cash flow is

GetCash($_SESSION["username"]),2); HTMLFooter(); ?>

To view the stock details of the user, I just display the stock that the user has from data once again from the stock class, which also includes the current price of the stock. There is a checkbox that will allow the user to sell some of there stock (if they have any!!) and once again use the stock class to sell the stock and update the tables in the database.

 $value)
	{
		if (strpos($key, "remove_") === 0)
			$theStock->SellStock($_SESSION["username"], $value);
		if ($key=="stock")
			$getStock = $value;
	}
	HTMLHeader("The details of your account");
?>

The stocks that the user has

" method="post" onsubmit="return checkSell()"> ReturnAllStocks($_SESSION["username"]); foreach ($usersStock as $theStocks) { echo ""; $totalValue += $theStocks[2] * $theStocks[1]; } echo ""; ?>
Stock NameQuantityPrice of StockValueSell
{$theStocks[0]}{$theStocks[1]}{$theStocks[2]}". number_format($theStocks[2] * $theStocks[1],2)."
Total value".number_format($totalValue,2)."

Your present cash flow is

GetCash($_SESSION["username"]),2); echo "

Your cash + investments

" . number_format($theUser->GetCash($_SESSION["username"]) + $totalValue,2); if (isset($getStock)) { echo "

$getStock more information

"; $stockDetailsArray = $theStock->ArrayOfStockDetails($getStock); for ($i = 0; $i < sizeof($stockDetailsArray); $i++) { echo "
DateTitle/Link
{$stockDetailsArray[$i]["Date"]}{$stockDetailsArray[$i]["Title"]}"; } echo "
"; } HTMLFooter(); ?>

The last part is the change of the password, which just will use the user class to update the users password to the requested input from the change password page.

ChangePassword($_SESSION["username"], $_REQUEST["password1"]);
		else
			$notSamePassword = true;
	}

	if (!isset($_SESSION["authenticated"]))
	{
		header("Location: /index.php");
		exit;
	}

	HTMLHeader("Change password",true);

	if (isset($error))
	{
		if ($error)
			echo "
Password updated
"; else echo "
Cannot update the password, please contact the system admin
"; } if (isset($notSamePassword)) echo "
Passwords are not the same
"; ?>
" onsubmit="return CheckChangePasswords()"> Change your password

New Password :

Retype New Password :

The actual pages that are displayed to the user are all very basic as them selves, but the actual logic and formula are all within the classes that is the gold dust as such, since it is one point of checking and also one file to update if there is a update to the yahoo etc stocks details, because would have to update the different pages that display the stocks and that is not good for testing and also updating.

CS71 – Ass1 – Finance – Part 4 – Login Register Forgotten password

I am doing the Harvards building dynamic websites called CS-75 (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning 🙂 even if allot of it you may already know.

Since most of the work is done in the class files, from the previous post then we just use the classes to only have basic php pages for the user. So the login page is just, which also has a check for if the user is already logged in and goto the viewdetails page, or if the user requests to logout then logout the user, the main page is just logging the user in.

LoginCheck($_REQUEST["username"], $_REQUEST["password"]);
	}

	if (isset($_REQUEST["logout"]))
		$theUser->Logout();

	if (isset($_SESSION["authenticated"]) && $_SESSION["authenticated"]== true)
	{
		header("Location: /viewdetails.php");
		exit;
	}
	
	HTMLHeader("Login to the stocks!",false);

	if (isset($error))
	{
		if ($error==false)
			echo "
Not able to login, please try again
"; } if (isset($_REQUEST["logout"])) echo "Thanks for using the site, you are logged out!"; ?>
" onsubmit=""> Login

Username : "/>

Password :

Here is the register page, where I send the request to the users class and utilize the return value to display either a message of check your emails (or in this case it will be a link on the top of the page) or display a error to the user depending on what could happen, e.g. not a valid valid/password(if the user turned off javascript checking) and also if there is already that email address present, the next check checks for the valid ID value that is sent to the user to validate there email.

RegisterUser($_REQUEST["username"], $_REQUEST["password"]);
		if ($error == -2)
			$notValidPassword = true;
		else if ($error == -1)
			$notValidEmail = true;
		else if ($error == 0)
			$emailAddressAlreadyPresent = true;
		else if ($error == 1)
			$checkEmails = true;
	}

	if (isset($_REQUEST["validid"]) && isset($_REQUEST["uid"]))
	{
		$error = $theUser->CheckUserGUID($_REQUEST["uid"], $_REQUEST["validid"]);
		if ($error == false)
			$userInvalid = true;
		else if ($error == true)
		{			
			$theUser->LoginCheck($_REQUEST["uid"],"",true);
			header("Location: /viewdetails.php");
			exit;
		}
	}
	
	if (isset($_SESSION["authenticated"]) && $_SESSION["authenticated"]== true)
	{
		header("Location: /viewdetails.php");
		exit;
	}
	
	HTMLHeader("Register",false);

	if (isset($error))
	{
		if (isset($checkEmails))
			echo "
Please check your emails to validate your email address
"; else if (isset($notValidPassword)) echo "
Not a valid password!, please enter one that is 6 characters and has at least 1 aphla/numeric
"; else if (isset($emailAddressAlreadyPresent)) echo "
Please use the forgotten password recovery, already email address registered
"; else if (isset($notValidEmail)) echo "
Not a valid email address
"; else if (isset($userInvalid)) echo "
Does not validate correctly, please contact the system admin
"; } ?>
" onsubmit="return CheckRegister()"> Register

Username : "/> (valid email address)

Password : (Has to be at least 6 characters and with 1 numeric and aplha character

Here is the last part, that will allow the user to enter there email to be able to get a link to enable them to change there password.

ForgottenUser($_REQUEST["username"]);
		if ($error)
			$checkEmails = true;
		else 
			$notValidEmail = true;
	}

	if (isset($_REQUEST["validid"]) && isset($_REQUEST["uid"]))
	{
		$error = $theUser->CheckUserGUID($_REQUEST["uid"], $_REQUEST["validid"]);
		if ($error == false)
			$userInvalid = true;
		else if ($error == true)
		{			
			$theUser->LoginCheck($_REQUEST["uid"],"",true);
			header("Location: /changepassword.php");
			exit;
		}
	}
	
	if (isset($_SESSION["authenticated"]) && $_SESSION["authenticated"]== true)
	{
		header("Location: /viewdetails.php");
		exit;
	}
	
	HTMLHeader("Forgotten password",false);

	if (isset($error))
	{
		if (isset($checkEmails))
			echo "
Please check your emails to validate your email address
"; else if (isset($notValidEmail)) echo "
Cannot find that email address
"; } ?>
" onsubmit="return CheckRegister(true)"> Forgotten password, please enter your username

Username : "/> (valid email address)

As you can see the actual pages are really simple because most of the work is done in the class files

CS71 – Ass1 – Finance – Part 3 – javascript and common php page

I am doing the Harvards building dynamic websites called CS-75 (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning 🙂 even if allot of it you may already know.

Before any of the actual pages start, they will all call this php file so that it will build up the classes and connect to the database, so to start with need to pull in there classes and also start the session. The two functions are the HTML header and footer parts of each page, so that do not have to alter every page to just add in another style as such, the last part is when we are creating the objects for the database/users/stocks.


 

  
    <?php echo $titleTag;?>
	
	
  
 



Because in the HTMLHeader function I am linking to the ass1.js file, which is the javascript code that will allow for a better user experience because we can do some input tests before sending to the server.

This is the function that checks the users input when they are trying to sell some of the stock that they have

function checkSell()
{
	var formElem = document.forms[0];
	var elemsEmpty =0;
	for (var i = 0; i < formElem.length; i++)
	{
		var elem = formElem[i];
		if (elem.type == "checkbox" && elem.checked)
			elemsEmpty++;
	}
	if (elemsEmpty ==0)
	{
		alert("Please select a item to sell");
		return false;
	}
	return true;
}

When the user is on the buying stock page, it would be good to make sure that the value is a integer value before the user actually sends the request to the server, it will also check to make sure that there is a symbol to search for, saves on just clicking on the submit without any data.

function CheckBuy()
{
	var symbol = document.getElementById("searchSymbol");
	var amount = document.getElementById("AMOUNT");
	
	if (symbol.value.length < 3 && amount == null)
	{
		alert("Please insert a search symbol");
		symbol.focus();
		return false;
	}
	
	if (amount != null && amount.length >0)
	{
		if (isNaN(amount) && amount.value > 0)
		{
			if (!document.getElementById("BUYME").checked)
			{
				alert("Please select the tick box next to the amount");
				return false;
			}	
		}
		else 
		{
			alert("Please enter a valid amount to buy");
			document.getElementById("AMOUNT").focus();
			return false;
		}
	}
	return true;
}

This function actually checks the users input on the amount of stock to buy and making sure that it is only numeric values.

// only allow integer values within the box
function CheckKey(boxToCheck)
{
	var boxValue="";
	for (i = 0; i < boxToCheck.value.length; i++)
	{
		if (!isNaN(boxToCheck.value[i]))
			boxValue += boxToCheck.value[i];
	}
	boxToCheck.value = boxValue;
}

This function, checks to make sure that the registration screen has a valid email address (does another check on the server if the user turns off javascript), and also checks the password for the correct format.

function CheckRegister(justUsername)
{
	var username = document.getElementById("username");
	var password = document.getElementById("password");
	if (username.value.match(/^([a-zA-Z0-9_.-])+@([a-zA-Z0-9_.-])+\.([a-zA-Z])+([a-zA-Z])+/)) {
		if (justUsername)
			return true;
		return CheckPassword(password);
    }else{   
		alert("Incorrect email format"); 
    }
	return false;
}

This the actual function that will check the password is in the correct format, e.g. 1+ numeric/alpha characters. and more than 6 in length.

function CheckPassword(password)
{
	if (password.value.length >=6) {
		var addLet = 0;
		for (var i =0; i < password.value.length; i++) {
			if (isNaN(password.value[i]))
				addLet++;
		}
		if (addLet == password.value.length)
		{
			alert("Please enter a password that contains at least 1 aplha/numeric character");
		} else
			return true;
	} else
		alert("Please enter a password that is at least 6 characters long");
	return false;
}

Here on the change password, I am making sure that the first password conforms to the above function check and also that the two passwords are the same in value.

function CheckChangePasswords()
{
	if (CheckPassword(document.getElementById("password1"))) 
	{
		if (document.getElementById("password1").value == document.getElementById("password2").value)
			return true;
		else
			alert("Passwords do not match");
	}
	return false;
}

Next is the actual php pages that the user will interact with.

CS71 – Ass1 – Finance – Part 2 – Classes

I am doing the Harvards building dynamic websites called CS-75 (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning 🙂 even if allot of it you may already know.

Here is the classes part of the project, I have only done two, one for the users which allow for registration/forgotten password/login etc and another for the stock function like buying and selling stock.

To start with here is the users.php file, this file will allow the user to login and setup the SESSION information,

query($sqlquery);
		if ($db->rowsNumber($result) === 1)
		{
			$output = $db->arrayResults($result);
			$_SESSION["authenticated"] = true;
			$_SESSION["username"] = $output["uid"];
			$db->freeResult($result);
			return true;
		}
		return false;
	}
	

here is the registration method, that will return values that are useful for the error reporting part of the web site

	// RegisterUser 
	// paramters : $varuser = email address
	//						$varpassword = password
	// return values
	// 					-2 : check password
	// 					-1 : not a valid email address
	// 					0 : already registered
	// 					1 : email check
	public	function RegisterUser($varuser, $varpassword)
	{
		global $db;
		
		if (!$this->CheckPassword($varpassword))
			return -2;
		// first lets check for a valid email address
		preg_match("/(\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,6})/", $varuser, $matches);
		if (isset($matches[0]))
		{
			// valid email address
			$sqlquery = sprintf("select uid from users where username = '%s'", $varuser);
			$result = $db->query($sqlquery);
			if ($db->rowsNumber($result) ===1)
			{
				$output  = $db->arrayResults($result);
				$db->freeResult($result);
				if ($output["uid"] > 0) return 0;
			}
			// so there is no user with that email address,
			// if there is not a trigger on the database
//			$sqlquery = sprintf("insert into users (username, pass) values ('%s',AES_ENCRYPT('%s','%s%s'))", $varuser, $varuser, $varuser, $varpassword);
			$sqlquery = sprintf("insert into users (username, pass,guid) values ('%s',AES_ENCRYPT('%s','%s%s'), uuid())", $varuser, $varuser, $varuser, $varpassword);
			$db->query($sqlquery);
			$sqlquery = sprintf("select guid from users where username = '%s'", $varuser);
			$result = $db->query($sqlquery);
			if ($db->rowsNumber($result) ===1)
			{
				$output = $db->arrayResults($result); 
				$guid = $output["guid"];
				$db->freeResult($result);
				// email the client a 
				$subject = 'Registration required for stocks program';
				$message = 'Please click on the link to register with the site';
				$message .= "http://{$_SERVER["SERVER_NAME"]}{$_SERVER["PHP_SELF"]}?uid=$varuser&validid=$guid";
				$headers = 'From: noresponse@codingfriends.com' . "\r\n" . 'X-Mailer: PHP/' . phpversion();

				//mail($varuser, "Register with the stocks site", $message, $headers);
				// debugging, just output the message
				echo $message;
				return 1;
			}
		}
		else
			return -1;
	}

encase the user has javascript turned off and the javascript is not checking to make sure that the user is passing in a password that is at least 6 characters long and also has 1+ numeric/aplha characters.

	// checks password to be a minimum of 6 letters and also contains at least 
	// 1+ aplha/numeric
	private function CheckPassword($varpassword)
	{
		$varpassword = trim($varpassword);
		if (strlen($varpassword) >=6)
		{
			// if not all aplha or digit characters then return true else false
			if (!ctype_alpha($varpassword))
				if (!ctype_digit($varpassword))
					return true;
			return false;
		}	
		else
			return false;	
	}

Here the user will be emailed, if you uncomment the code, a link that will allow to change the password

	public function ForgottenUser($varuser)
	{
		global $db;
		
		$sqlquery = sprintf("update users set guid = uuid() where username = '%s'", $varuser);
		$result = $db->query($sqlquery);
		if ($db->rowsAffected() === 1)
		{
			$db->freeResult($result);
			$sqlquery = sprintf("select guid from users where username = '%s'", $varuser);
			$result = $db->query($sqlquery);
			$output = $db->arrayResults($result);
			$db->freeResult($result);
			
			$subject = 'Forgotten password';
			$message = 'Please click on the link to get back your password with the site';
			$message .= "http://{$_SERVER["SERVER_NAME"]}{$_SERVER["PHP_SELF"]}?uid=$varuser&validid={$output["guid"]}";
			$headers = 'From: noresponse@codingfriends.com' . "\r\n" . 'X-Mailer: PHP/' . phpversion();

			//mail($varuser, "Register with the stocks site", $message, $headers);
			// debugging, just output the message
			echo $message;
			return true;
		}
		$db->freeResult($result);
		return false;
	}

here this is checking to make sure that the value that was passed to the user to either registrar/forgotten password that is it the same as what is in the database and thus change that value to 0 to denote that the user is valid.

	
	public function CheckUserGUID($varuser, $guid)
	{
		global $db;
		
		$retValue = false;
		$sqlquery = sprintf("select guid from users where username = '%s'", $varuser);
		$result = $db->query($sqlquery);
		if ($db->rowsNumber($result) === 1)
		{
			$output = $db->arrayResults($result);
			if ($guid == $output["guid"])
				$retValue = true;
			$sqlquery = sprintf("update users set guid = 0 where username = '%s'", $varuser);
			$db->query($sqlquery);
		}
		$db->freeResult($result);
		return $retValue;
	}

If the user wants to update there password, here we alter the database values to for there password and using the AES_ENCRYPT function within MySQL to encrypted the password.

	public function ChangePassword($varuserID, $varpassword)
	{
		global $db;
		
		if (!$this->CheckPassword($varpassword))
			return false;

		$sqlquery = sprintf("select username from users where uid = %s", $varuserID);
		$result = $db->query($sqlquery);
		if ($db->rowsNumber($result) === 1)
		{
			$output = $db->arrayResults($result);
			$varuser = $output["username"];
			$db->freeResult($result);
			
			$sqlquery = sprintf("update users set pass = AES_ENCRYPT('%s', '%s%s') where username = '%s' and guid = 0", $varuser, $varuser, $varpassword, $varuser);
			$result = $db->query($sqlquery);
			return true;
			// if the password is the same, then the line is not updated and thus may come across as a weird message.
//			if ($db->rowsAffected() ===1)
//				return true;
		}
		$db->freeResult($result);
		return false;
	}

this is the method that will return the value of the users cash that they have left

	
	public function GetCash($varuser)
	{
		global $db;
		$returnValue = 0;
		$sqlquery = sprintf("select cash from users where uid = %d", $varuser);
		$result = $db->query($sqlquery);
		if ($db->rowsNumber($result) ===1)
		{
			$results = $db->arrayResults($result);
			$returnValue = $results["cash"];
		}
		$db->freeResult($result);
		return $returnValue;
	}

last but not the least, here is destroying the session data, so that the user is logout.

	// logout the user!
	public 	function Logout()
	{
		session_destroy();
	}
};
?>

The next class is the stock details, I called it getstocks.php, to start with I am getting the stock details from the yahoo site, with using the fopen of the yahoo.


This method will build up a array of the users stock details and also the present value of the stock symbol.

		// display all of the users stock details, 
		public function ReturnAllStocks($varusernameID)
		{
			global $db;
			
			$sqlquery = sprintf("select symbol, quantity from stocks where uid = '%s'", $varusernameID);
			$result = $db->query($sqlquery);
			$insI = 0;
			while ($row = $db->arrayResults($result))
			{
				$returnArr[$insI++] = array($row["symbol"], $row["quantity"],$this->GetStocksFromYahoo($row["symbol"]));
			}
			$db->freeResult($result);
			return $returnArr;
		}

This method will sell the stock that the user has, to start with need to update the users cash flow for the sale of the stock, and then delete the actual stock from stocks table in the database that is linked to the user.

		public function SellStock($varusernameID, $stockID)
		{
			global $db;
			
			$db->startTransaction();
			try {
//	need to pull back users stock quantity and then delete it from the list and update the cash within the users table.
				$sqlquery = sprintf("update users,stocks set users.cash = users.cash + (%f * stocks.quantity) where users.uid = stocks.uid and users.uid = %d and stocks.symbol = \"%s\"",$this->GetStocksFromYahoo($stockID), $varusernameID, $stockID);
				$result = $db->query($sqlquery);
				if ($db->rowsAffected() == 1)
				{
					$sqlquery = sprintf("delete from stocks where uid = %d and symbol = \"%s\"", $varusernameID, $stockID);
					$result2 = $db->query($sqlquery);
					if ($db->rowsAffected() != 1)
						throw new Exception("Error updating the stock details");
				}
				else
					throw new Exception("Error updating users cash");
					
				$db->commitTransaction();
			} catch (Exception $e)
			{
				echo "Possible error : {$e->getMessage()}";
				$db->rollbackTransaction();
			}
		}

this method is the opposite of the above, where we are buying stock, we have to check the users balance/cash to make sure that they are able to, and then go though the process of updating the users table and the stocks, I am using the START TRANSACTION from within MySQL database InnoDB so that if any of process errors I can rollback the updates to the database tables.

		// buy the stock into the users 
		public function BuyStock($varusernameID, $stockID, $stockQuantity)
		{
			global $db;
			
			$valueOfStock = $this->GetStocksFromYahoo($stockID) * $stockQuantity;
			if ($valueOfStock > 0)
			{
				try {
					$db->startTransaction();
					// update the users cash, whilst making sure that there is enought !!.
					$sqlquery = sprintf("update users set cash = cash - (%f) where uid = %d and cash > %f", $valueOfStock, $varusernameID, $valueOfStock);
					$db->query($sqlquery);
					// if there was enought money, place the update into the stocks table now!.
					if ($db->rowsAffected() ==1)
					{
						// now update the stock database table, could have used on duplicate key here.. 
						$sqlquery = sprintf("update stocks set quantity = quantity + %d where uid = %d and symbol = '%s'",$stockQuantity, $varusernameID, $stockID);
						$db->query($sqlquery);
						// there was no stock of that type already, then just insert
						if ($db->rowsAffected() == 0)
						{
							$sqlquery = sprintf("insert into stocks values (%d,\"%s\", %d)", $varusernameID, $stockID, $stockQuantity);
							$db->query($sqlquery);
						}
					}
					else 
						throw new Exception("Not enought money!");
					$db->commitTransaction();
				} catch (Exception $e)
				{
					echo "Possible error : {$e->getMessage()}";
					$db->rollbackTransaction();
					return 0;
				}
			}
			else
			{
				echo "Possible error : Getting values from Yahoo stock";
				return 0;
			}
			return $valueOfStock;
		}

Here, I am getting the news from of the symbol stock from the yahoo rss links.

		
		public function ArrayOfStockDetails($stockID)
		{
			$xmlDoc =  simplexml_load_file("http://finance.yahoo.com/rss/headline?s=$stockID");
			$xpath = $xmlDoc->xpath("//channel/item");
			$insI = 0;
			foreach ($xpath as $key)
			{
				$arrayRet[$insI++] = array("Date" => (string)$key->pubDate,
															  "Link" => (string)$key->link,
															"Title" =>  (string)$key->title);
			}
			return $arrayRet;
		}
	};
?>

Next going to do the basic php file to load up the class files and connect to the database, with also the javascript code.

CS75 – Ass1 – Finance

I am doing the Harvards building dynamic websites called CS-75 (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning 🙂 even if allot of it you may already know.

I have done the previous project here Three aces, where it was to develop a menu system using simpleXML.

This project is to communicate with the Yahoo finance website, and bring back details of stock prices for different companies, and also to keep track of the users purchases and also allow them to sell the stock that they have purchased, I have attached the PDF of the assignment if you want more information about the assignment.

Since it is a bit bigger than the previous project I am going to split each part into a different post, so this one is going to be about the database setup. So to start with here is the details of the database tables, I have included the full source code and sql file to create the database within MySQL, here is what phpmyadmin export created, I did not include the trigger on the users table that was creating a UUID, because some versions of MySQL does not support that syntax

--
-- Table structure for table `stocks`
--

DROP TABLE IF EXISTS `stocks`;
CREATE TABLE IF NOT EXISTS `stocks` (
  `UID` int(30) unsigned NOT NULL DEFAULT '0' COMMENT 'Link to users table',
  `SYMBOL` varchar(20) DEFAULT NULL,
  `Quantity` int(20) DEFAULT NULL,
  KEY `UID` (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `stocks`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `UID` int(30) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `pass` blob NOT NULL,
  `cash` decimal(10,2) NOT NULL DEFAULT '10000.00' COMMENT 'the default value is the free gift',
  `GUID` char(36) NOT NULL COMMENT 'Use this string to validate the user, if the value is 0, then validated',
  PRIMARY KEY (`UID`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Constraints for table `stocks`
--
ALTER TABLE `stocks`
  ADD CONSTRAINT `stocks_ibfk_1` FOREIGN KEY (`UID`) REFERENCES `users` (`UID`) ON DELETE CASCADE;

If you are running the latest version of MySQL then you can have the trigger on the database table :), and just need to take out the uncomment code within the users.php file!.

-- Triggers `users`
--
DROP TRIGGER IF EXISTS `users_insert`;
DELIMITER //
CREATE TRIGGER `users_insert` BEFORE INSERT ON `users`
 FOR EACH ROW begin
set NEW.guid = uuid();
end
//
DELIMITER ;

Here is my database.php file where I connect to the database and also perform some of the required actions, like queries etc and also since I am using the InnoDB MySQL engine, then I can use the START TRANSACTION which will allow the ROLLBACK function within mysql so that if any of the SQL between them do not work, then I can roll back to where I was before I started that transaction, or commit the sql to the database.

theConnection = 
				mysql_connect($connectionHost, $connectionUser, $connectionPW)) === FALSE)
				die("Problems - connection to the database, please check");
			if (mysql_select_db($connectionDB, $this->theConnection) === FALSE)
				die("Problems - connected to database engine, but not the database");
		}
		
		public function __destruct()
		{
			if ($this->theConnection)
				mysql_close($this->theConnection);
		}
		
		public function query($stringQuery)
		{
			$q = mysql_real_escape_string($stringQuery);
			return mysql_query($stringQuery);
		}
		
		// if the database supports the result of returning the number of rows effected with the last call.
		public function rowsNumber($connection)
		{
			return mysql_num_rows($connection);
		}
		
		public function rowsAffected()
		{
			return mysql_affected_rows();
		}
		
		public function arrayResults($connection)
		{
			return mysql_fetch_assoc($connection);
		}
		
		public function freeResult($connection)
		{
			mysql_free_result($connection);
		}
				
		public function startTransaction()
		{
			$this->query("START TRANSACTION");
		}
		
		public function commitTransaction()
		{
			$this->query("COMMIT");
		}
		
		public function rollbackTransaction()
		{
			$this->query("ROLLBACK");
		}
		
		private $theConnection;
	};
?>

Shall does the classes next of the project and then the actual php files that do the user interaction.

CS75 – Assignment 0 – Three Aces Menu

I am doing the Harvards building dynamic websites called CS-75 (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning 🙂 even if allot of it you may already know.

Anyway, to setup the CS75 on my personal laptop ( I am running Ubuntu 10.4) I did a couple of alternations for me so that the local apache instance looks like I am doing it under the top level domain of CS75 :).. so for this assignment Three Aces menu I am calling the website http://threeaces.cs75 on my local host, so to setup that I altered the /etc/hosts file and added

127.0.0.1       threeaces.cs75

so that local IP will also have threeaces.cs75 🙂 and now need to alter apache to allow me to run that as a virtualhost on my laptop, I added the file within

sudo vim /etc/apache2/sites-available/cs75_threeaces

with adding in


       ServerAdmin emailaddress@here.com
       ServerName  threeaces.cs75
       DocumentRoot /var/www/cs75/ass0
       
               Options Indexes FollowSymLinks MultiViews
               AllowOverride None
               Order allow,deny
               allow from all
       

       ErrorLog /var/log/apache2/error.log
       LogLevel warn
       CustomLog /var/log/apache2/access.log combined

Which points to the /var/www/cs75/ass0 as the base directory for this assignment :).

I have included the assignments 0 PDF for more information, but basically this assignment is about playing with simpleXML to load in a menu (that you have created from the menu list of the restaurant Three Aces) which is in XML format, you get to pick the format, but here is mine (it has the extras link within the menu->type extras attribute which link to the extras at the bottom of the menu format)



	
		
			5.50
			9.75
		
		
			6.85
			10.85
		
	
	
		
			3.50
			4.50
		
		
			4.50
			5.50
		
	
	
		
			4.50
			4.95
		
		
			4.50
			4.95
		
	
	
		
			7.25
		
		
			7.25
		
	
	
		1.25
		1.85
	

You can implement it anyway that you want to, but to not have two same names of the food for different sizes. So that is why I have a the item name with the price list(s) underneath. It is only part of the menu that I implemented above.

The next part is to only have part of the menu on the main home page, so I am just displaying the main types of food options, I have included the source file for all of the files in the zip file above.

type as $types) { echo "attributes()->name . "\"/>".$types->attributes()->name . "
" ; } ?>

Once the user has selected the type of food that they want, you next to have display the items of food within that area, so I am passing in the $_GET string from the index page the type of food, so within the types.php I pick up the type and then display all of the food items within that area. I did do some javascript that will make sure that there is a valid integer value within the, also at the start of the code I am pulling any extra values from the menu to populate the extras option.

"; echo ""; $result = $xml->xpath("//type[@name='$type']"); if (sizeof($result[0]) == 0) echo "

Please go back to home, because cannot find that type

"; else { $extras = $result[0]->attributes()->extras; if (strlen($extras) > 0) { $resultExtras = $xml->xpath("//extras[@name='$extras']"); if (sizeof($resultExtras[0]) > 0) { $extraName = $resultExtras[0]->attributes()->name; for ($i=0; $i < sizeof($resultExtras[0]); $i++) { $thePrice = $resultExtras[0]->price[$i]; $theType = ($resultExtras[0]->price[$i]->attributes()->type ? $resultExtras[0]->price[$i]->attributes()->type : "Normal"); $theExtras[$i] = array((string)$thePrice, (string)$theType); } } } foreach ($result[0] as $items) { echo "".$items->attributes()->name."PriceQuantity"; if (strlen($extras)) echo "$extras"; echo ""; for ($i=0; $i < sizeof($items->children()); $i++) { echo "". $items->price[$i]->attributes()->type . "" . $items->price[$i]. "attributes()->name)."_".$i."\"/>"; if (strlen($extras)) { foreach ($theExtras as $typeCheck) { if ($typeCheck[1] == $items->price[$i]->attributes()->type) { echo "attributes()->name)."_".$i."\"/>"; break; } } } echo ""; } } }?>

Because there is a basket of items that the customer will like to buy, I am also storing the basket details within the $_SESSION within a multi array, the array looks like something like

Array
(
    [Pizzas] => Array
        (
            [Tomato  Cheese] => Array
                (
                    [Small] => Array
                        (
                            [price] => 5.5
                            [quantity] => 6
                        )
                )
        )
)

So in the next page, checkout.php I need to update the basket details and also then display what the customer has already picked (with giving the options to update the quantity of goods and also to remove them if they did not want it), the first part loads the new item(s) into the basket and the second part of the code below will display the basket to the user, and call the update.php file if any quantities/remove of items need to be done.

xpath("//type[@name='$type']"); if (sizeof($result[0]) == 0) { if (strlen($type) > 0) echo "

Please go back to home, because cannot find that type

"; } else { $extras = $result[0]->attributes()->extras; if (strlen($extras) > 0) { $resultExtras = $xml->xpath("//extras[@name='$extras']"); if (sizeof($resultExtras[0]) > 0) { $extraName = $resultExtras[0]->attributes()->name; for ($i=0; $i < sizeof($resultExtras[0]); $i++) { $newname = str_replace(" ", "_" , $extras) . "_" . $i; $$newname = $_GET[$newname]; if (strlen($$newname) > 0) { $thePrice = $resultExtras[0]->price[$i]; $theType = ($resultExtras[0]->price[$i]->attributes()->type ? $resultExtras[0]->price[$i]->attributes()->type : "Normal"); $theExtras[$i] = array($$newname, (string)$thePrice, (string)$theType); } } } } foreach ($result[0] as $items) { for ($i=0; $i < sizeof($items->children()); $i++) { $newname = $items->attributes()->name."_".$i; $newname = str_replace(" ", "@", $newname); $$newname = $_GET[$newname]; if ($$newname > 0) { $priceType = $items->price[$i]->attributes()->type; if (strlen($priceType) <=1) $priceType = "Normal"; $extraPrice = 0; foreach ($theExtras as $extraAdd) { if ($extraAdd[0] == $newname) $extraPrice = $extraAdd[1]; } $basket[(string)$type][(string)$items->attributes()->name][(string)$priceType . ($extraPrice > 0 ? (string)" (".$extras.")" : "")] = array("price" =>(float) ($items->price[$i]) + (float)$extraPrice, "quantity" => (int)($$newname + $basket[(string)$type][(string)$items->attributes()->name][(string)$priceType]["quantity"]) ); // going with the theory of adding to the previous quantity echo "Have added " . $type . " (". $items->attributes()->name . " " . $priceType . ")
"; } } } $_SESSION["basket"] = $basket; } // lets print out the basket in a form of the user to confirm and also remove / alter the quantity $totalPrice = 0; echo "
"; foreach ($basket as $type => $typevalue) { echo ""; foreach ($typevalue as $item => $itemvalue) { echo ""; foreach ($itemvalue as $size => $sizevalue) { $namevalue = $type."_".$item."_".$size; $namevalue = str_replace(" ","@",$namevalue); echo ""; echo ""; $totalPrice +=$sizevalue["price"] * $sizevalue["quantity"]; } } echo ""; } echo "
TypeItemSizePriceQuantityTotal PriceRemove
$type
$item
$size£".number_format($sizevalue["price"],2) . "£".number_format($sizevalue["price"] * $sizevalue["quantity"],2) ."
"; echo "The total price is £".number_format($totalPrice,2)."
"; ?>

here is the update.php, this will update the basket from the requested action from the above php file.

 $value)
{
	$key = str_replace("@", " ", $key);
	list($type, $item, $size,$command) =  split("_", $key);
	if ($command== "quantity" && $value > 0)
	{
		$basket[$type][$item][$size]["quantity"] = $value;
	}
	elseif ($command =="remove")
	{
		unset($basket[$type][$item][$size]);
		// clean out the type -> item if none more left
		if (sizeof($basket[$type][$item])==0)
			unset($basket[$type][$item]);
		// clean out the type if none left
		if (sizeof($basket[$type])==0)
			unset($basket[$type]);
	}
}
$_SESSION["basket"] = $basket;
// redirect back to checkout.php
header('Location: checkout.php');
?>

and redirects back to checkout.php.

The last page displays the order in total and also says thanks very much for the order :).. and deletes the basket details :).

";
foreach ($basket as $type => $typevalue)
{
	echo "";
	foreach ($typevalue as $item => $itemvalue)
	{
			echo "";
			foreach ($itemvalue as $size => $sizevalue)
			{
				echo "";
				echo "";
				$totalPrice +=$sizevalue["price"] * $sizevalue["quantity"];
			}
	}
	echo "";
}
echo "
TypeItemSizePriceQuantityTotal Price
$type
$item
$size£".number_format($sizevalue["price"],2) . "" . $sizevalue["quantity"] . "£".number_format($sizevalue["price"] * $sizevalue["quantity"],2) ."
"; echo "The total price is £".number_format($totalPrice,2)."
"; session_destroy(); ?>

The file above does include all of the php files from the above comments and the PDF of the assignment. You can also view the assignment live on my CS – 75 assignment 0 test area.