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.

3 thoughts on “CS75 – Ass1 – Finance”

  1. Encase this helps anyone, to reset the auto increment index value back to 1 you do

    ALTER TABLE <TABLE NAME> AUTO_INCREMENT=1

  2. Hi,

    First and foremost, I would like to say that I believe you did an awesome job with all of the detail you provided in creating your websites for the said projects. For a novice like myself, I am quite impressed! I have learned so much from your examples. However, I am now stumped! I am following your CS75

  3. Hi Esther

    Sounds like you need to give the user “genux” on the database access to the database that you just created.

    GRANT ALL privileges ON <yourdatabasename>.* TO 'genux'@'localhost' IDENTIFIED BY '<yourpassword?'

    Hope that helps.

Leave a Reply

Your email address will not be published. Required fields are marked *