Author: genux

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.

<?php
	require("functions_start.php");
 
	if (isset($_REQUEST["username"]) && isset($_REQUEST["password"]))
	{
		$error = $theUser->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 "<div>Not able to login, please try again</div>";
	}
	if (isset($_REQUEST["logout"])) 
		echo "Thanks for using the site, you are logged out!";
?>
<!-- taken from http://www.html.it/articoli/nifty/index.html-->
<div id="login">
<b class="rtop">
  <b class="r1"></b> <b class="r2"></b> <b class="r3"></b> <b class="r4"></b>
</b>
<form method="post" action="<?php echo $_SERVER["PHP_SELF"]; ?>" onsubmit="">
Login
<p>
Username : <input type="text" name="username" id="username" value="<?php echo $_REQUEST["username"]; ?>"/>
</p>
<p>
Password : <input type="password" name="password" id="password"/>
</p>
<p>
<input type="submit"/>
</p>
<p id="right">
<a href="register.php">Register</a> here.
</p>
</form>
<b class="rbottom">
  <b class="r4"></b> <b class="r3"></b> <b class="r2"></b> <b class="r1"></b>
</b>
</div>
<?php
	HTMLFooter();
?>

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.

<?php
	require("functions_start.php");
 
	if (isset($_REQUEST["username"]) && isset($_REQUEST["password"]))
	{
		$error = $theUser->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 "<div>Please check your emails to validate your email address</div>";
		else if (isset($notValidPassword))
			echo "<div id=\"error\">Not a valid password!, please enter one that is 6 characters and has at least 1 aphla/numeric</div>";
		else if (isset($emailAddressAlreadyPresent))
			echo "<div id=\"error\">Please use the forgotten password recovery,  already email address registered</div>";
		else if (isset($notValidEmail))
			echo "<div id=\"error\">Not a valid email address</div>";
		else if (isset($userInvalid))
			echo "<div id=\"error\">Does not validate correctly, please contact the system admin</div>";
	}
?>
<!-- taken from http://www.html.it/articoli/nifty/index.html-->
<div id="login">
<b class="rtop">
  <b class="r1"></b> <b class="r2"></b> <b class="r3"></b> <b class="r4"></b>
</b>
<form method="post" action="<?php echo $_SERVER["PHP_SELF"]; ?>" onsubmit="return CheckRegister()">
Register <p>
Username : <input type="text" name="username" id="username" value="<?php echo $_REQUEST["username"]; ?>"/>
(valid email address)</p>
<p>
Password : <input type="password" name="password" id="password"/>
(Has to be at least 6 characters and with 1 numeric and aplha character</p>
<p>
<input type="submit"/>
</p>
</form>
<b class="rbottom">
  <b class="r4"></b> <b class="r3"></b> <b class="r2"></b> <b class="r1"></b>
</b>
</div>
<?php
	HTMLFooter();
?>

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.

<?php
	require("functions_start.php");
 
	if (isset($_REQUEST["username"]) )
	{
		$error = $theUser->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 "<div>Please check your emails to validate your email address</div>";
		else if (isset($notValidEmail))
			echo "<div id=\"error\">Cannot find that email address</div>";
	}
?>
<!-- taken from http://www.html.it/articoli/nifty/index.html-->
<div id="login">
<b class="rtop">
  <b class="r1"></b> <b class="r2"></b> <b class="r3"></b> <b class="r4"></b>
</b>
<form method="post" action="<?php echo $_SERVER["PHP_SELF"]; ?>" onsubmit="return CheckRegister(true)">
Forgotten password, please enter your username <p>
Username : <input type="text" name="username" id="username" value="<?php echo $_REQUEST["username"]; ?>"/>
(valid email address)</p>
<input type="submit"/>
</p>
</form>
<b class="rbottom">
  <b class="r4"></b> <b class="r3"></b> <b class="r2"></b> <b class="r1"></b>
</b>
</div>
<?php
	HTMLFooter();
?>

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
	require("database.php");
	require("getstocks.php");
	require("user.php");
 
	session_start();
 
	function HTMLHeader($titleTag,$loggedIn = true)
	{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> 
<html>
  <head>
    <title><?php echo $titleTag;?></title>
	<link href="style.css" rel="stylesheet" type="text/css"/>
	<script src="ass1.js" language="javascript"></script>
  </head>
 <body>
<div id="mainpage"> 
	<ul id="menuoptions">
	<?php if ($loggedIn) { ?>
	<li><a href="viewdetails.php">View your stock</a></li>
	<li><a href="buystock.php">Buy stock</a></li>
	<li><a href="index.php?logout=true">Logout</a></li>
	<li><a href="changepassword.php">Change password</a></li>
	<?php } else { ?>
	<li><a href="index.php">Login</a></li>
	<li><a href="register.php">Register</a></li>
	<li><a href="forgotten.php">Forgotten password</a></li>
	<?php } ?>
	</ul>
<div id ="container">
<?php
	}
 
	function HTMLFooter()
	{
?>
</div>
</div>
</body>
</html>
<?php
	}
 
	$db= new mysqlConnect("localhost", "username","password", "cs75_project1");
	$theStock = new StocksDetails();
	$theUser = new User();
?>

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,

<?php
// could have setup the contrustor to have a session ID of the user.
// I went for this of passing in the users ID in the parameters, encase I wanted to pass in a different user within a admin screen!.
 
class User {
	// LoginCheck will check against the database the users creditials, please note that the string is send in clear text over the port/IP address from the php server to mysql database.
	public	function LoginCheck($varuser, $varpassword, $checkPassword = false)
	{
		global $db;
		if ($checkPassword == false)
			$sqlquery = sprintf("select uid from users where username = '%s' and pass =  AES_ENCRYPT('%s', '%s%s') and guid = 0", $varuser, $varuser, $varuser, $varpassword);
		else 
			$sqlquery = sprintf("select uid from users where username = '%s' and guid = 0", $varuser);
		$result = $db->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 .= "<a href=\"http://{$_SERVER["SERVER_NAME"]}{$_SERVER["PHP_SELF"]}?uid=$varuser&validid=$guid\">http://{$_SERVER["SERVER_NAME"]}{$_SERVER["PHP_SELF"]}?uid=$varuser&validid=$guid</a>";
				$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 .= "<a href=\"http://{$_SERVER["SERVER_NAME"]}{$_SERVER["PHP_SELF"]}?uid=$varuser&validid={$output["guid"]}\">http://{$_SERVER["SERVER_NAME"]}{$_SERVER["PHP_SELF"]}?uid=$varuser&validid={$output["guid"]}</a>";
			$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.

<?php
	class StocksDetails {
 
		public function GetStocksFromYahoo($varsearch)
		{
			$returnvalue = 0;
			$handle = fopen("http://download.finance.yahoo.com/d/quotes.csv?s=$varsearch&f=sl1d1t1c1ohgv&e=.csv", "r");
			while ($row = fgetcsv($handle))
			{
				if ($row[0] == $varsearch)
					$returnvalue = $row[1];
			}
			fclose($handle);
			return $returnvalue;
		}

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.

<?php
	class mysqlConnect
	{
		public function __construct($connectionHost, $connectionUser, $connectionPW, $connectionDB)
		{
			if (($this->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.

CDATA, With, Match and Regular Expression

CDATA

Sometimes when you get errors within your HTML validation can sometimes just get bogged down with javascript code that has not been “told” to be ignored by the parser, for example the javascript code

if (a < b)

is fine within that context, but within HTML the < could be a start of a tag and thus would fall over with some old parsers and also the validator, so best to wrap the javascript code within the CDATA 

<script type="text/javascript">
<![CDATA[
   if (a < b) 
...
]]>
</script>

WITH

The with syntax will allow you to work on a object of a HTML form elements within having to constantly referencing the object fully, for example if you had a HTML as

<form name="theform">
<input id="theinput"/> 
<input id="secondinput"/>
</form>

and so within the javascript you could do something like this without using the with statement,

var theinput = document.form.theform.theinput;

well if you wanted to you could use the “with” syntax and thus if you wanted to access all of the elements within that HTML object then

with (document.form.theform)
{
    var first = theinput;
    var second = secondinput;
}

just allot more nicer to use the objects :).

Match / Regular expression

Since a string within the javascript is capable of having regular expression actions applied to it, you can use the inbuilt method that is associated with the string class as such, which allows you to do regular expression, but for some reason you do not need to put in the ” ” around the regular expression.

var username = document.getElementById("username");
if (
    username.match(/.+@.+\.com$)
   ) {
....

will match with a regular expression so that the username has to end with .com for it to be valid!!.. not really a good test, but there you go!.

XML XSLT XPath DOCDocument

This is one of the ways that I create dynamic web pages on the fly with using XML/XSLT with a PHP back-end to update the XML on the fly with data obtained (either from MySQL etc, but in this example it will be hard coded).

To start with I am using the PHP DOMDocument object, which is a very good XML reader and updater that uses the XPath query to obtain nodes(lists) from within the XML document itself. So to start with I first load up the XML file that has the basic outputs defined, which means that if you did not want to insert any data on the fly, you could use this basic one.

<?php 
    $xmlDoc = new DOMDocument();
    $xmlDoc->load("ass1baseoutput.xml");

and here is the XML file, what it basically is is just a symbols that I have created on the fly to mean something to me, like “title”,”form” so that I know what I kinder should be doing with the data located within them, so as you can probably tell I am about to generate an form filled with data.

<root>
	<title>XML/XSLT test</title>
	<form>
		<table>
			<row>
				<td>value1</td>
				<td>value2</td>
			</row>
			<row>
				<td>value3</td>
				<td>value4</td>
			</row>
		</table>
	</form>
</root>

The next this to do, is now that we have read in the XML file, we can use the DOCDocument to either createElement, which we can appendChild to that element of any data that we want to insert on the file, I am using a XPath to find the place where I want to insert the new data, in this case within the root/form/table element of the XML document. Since within the above document we have a row tag which is followed by a td tag, I have to create the same to insert, which is why I am creating a element first of a “row” and then appending a child (the 2 td elements) to that node, which at the end I insert the created row into the document via the xpath result.

// now that you have a XML document loaded, if you wanted to add more to areas, then you are 
// able to, thus changing the output on the fly as such.
 
	$xpath = new DOMXpath($xmlDoc);
	$nodelist = $xpath->query('/root/form/table');
	$row = $xmlDoc->createElement("row","");
	$row->appendChild($xmlDoc->createElement("td","Created on the fly"));
	$row->appendChild($xmlDoc->createElement("td","Right hand side"));
	$nodelist->item(0)->appendChild($row);
 
    $xslDoc = new DOMDocument();
    $xslDoc->load("sample.xsl");

Here is my XSLT file, how it kinder works is within the manor of matching elements from the XML document into the stylesheet (XSLT) so for example means to match from the base node (in this case the node called ROOT), then create some textural output (which is html code) and then pull back from data from the /root/title element from within the XML document with using the , to apply other templates that may match other parts from within the XML document, you use the adaptly named apply-templates 🙂 as 

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="html" encoding="iso-8859-1" indent="no"/>
<xsl:template match="/">
  <html>
  <title><xsl:value-of select="/root/title"/></title>
  <body>
	<xsl:apply-templates select="/root/form"/>
</body>
</html>
</xsl:template>
 
<xsl:template match="/root/form">
	<form action="" method="">
		<xsl:apply-templates select="/root/form/table"/>
		<input type="submit" value="submit"/>
	</form>
</xsl:template>
 
<xsl:template match="/root/form/table">
	<table>
		<xsl:for-each select="row">
		<tr>
				<xsl:for-each select="td">
					<td><xsl:value-of select="."/></td>
				</xsl:for-each>
		</tr>
		</xsl:for-each>
	</table>
</xsl:template>
 
</xsl:stylesheet>

and now all we need to do is to process the XML/XSLT to produce the output, with using the XSLTProcessor class

    $proc = new XSLTProcessor();
 
    $proc->importStylesheet($xslDoc);
    echo $proc->transformToXML($xmlDoc);
?>

The output would be, which has the inserted data “Created on the fly”, it may seem like a allot of hassle to get the output like below, but if you was using a far bigger XML file and XSLT with also creating allot of different pages with the same data, but just inserting different parts into the correct XML area, you can start to see that it will save allot of time and also be a nicely validated document.

<html><title>XML/XSLT test</title><body><form action="" method=""><table>
<tr><td>value1</td><td>value2</td></tr>
<tr><td>value3</td><td>value4</td></tr>
<tr><td>Created on the fly</td><td>Right hand side</td></tr>
</table><input type="submit" value="submit"></form></body></html>

Unique fileds Foreign keys duplicate key and Joins

I have been asked about some SQL questions, so I thought that I would post on here encase it helps others as well. The questions was based around, what is the unique key compared to a primary key, foreign keys (and how to link) and also the joins within a relationship database(outer, inner, left, right).

Unique key

An unique key is kinder similar to a primary key, where the primary key is the main index into the table itself (kinder like a index at the back of the book, where is item 5, arrh there in the book) well the unique key basically means that this column within the table cannot have a similar value within this column compared to any other values stored in other rows in the table. So for example,

Index UniqueKey
1 genux
2 bob

now if I tried to insert a name into the uniquekey field name of “genux” it would complain because there is already a field name of that.

Foreign key

A foreign key is a value that is linked to another table that if that other table ( which normally is the main table of lets say customers) deletes a row from it, then you can setup in the second table (where the foreign key is) to also delete data from its table where they are linked with the foreign key (this can also happen on updates as well instead of deleting data).

So as taken from this page on the mysql website.

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

The foreign key is the parent_id field within the child table where it links to the parent tables id field and with the “ON DELETE CASCADE” it will delete any rows within the childs table if the parent table deletes its linking row.

Duplicate keys

Duplicate keys are nice, when you want to insert some data into a table and the index key is already present then you can update the value within the already present row in the table and thus get around the testing for any duplicate keys, you can also use the REPLACE syntax as well (but this will not allow you to increment the present value within the field, but update it to a new value).

Joins, left,right, inner, outer

Joins are basically in mathematical terms linking sets of data together, so a inner join

SELECT * FROM table1 INNER JOIN table2 ON table1.linkId = table2.Id

This will bring back only the data from both tables that have the linking column values from table1.linkId and table2.Id, in contrast to outer join

SELECT * FROM table1 OUTER JOIN table2 ON table1.linkId = table2.Id

where all of the values from within the two tables are outputted in the results but values of NULL are inserted into the unmatched result fields.

There is also, left and right join where it is similar to the outer join as in

SELECT * FROM table1 LEFT JOIN table2 ON table1.linkId = table2.Id

But only all of the values from the left table are still present in the results output and NULLs inserted into the fields where there is no link with the right had side table, of course the values from the right table are inserted where there is a link with the left table, and the right join is similar to the left join apart from all rows are in the results from the right table instead of the left. As a side note, the left table is the table on the left hand side of the join syntax so table1 in this instance.