{"id":1143,"date":"2010-08-11T15:57:53","date_gmt":"2010-08-11T14:57:53","guid":{"rendered":"http:\/\/www.codingfriends.com\/?p=1143"},"modified":"2010-08-11T15:59:47","modified_gmt":"2010-08-11T14:59:47","slug":"cs75-ass1-finance","status":"publish","type":"post","link":"https:\/\/www.codingfriends.com\/index.php\/2010\/08\/11\/cs75-ass1-finance\/","title":{"rendered":"CS75 – Ass1 – Finance"},"content":{"rendered":"

<\/a><\/span>I am doing the Harvards<\/a> building dynamic websites called CS-75<\/a> (also could be called E-75), because someone told me about it and I just thought might as well, it is all learning \ud83d\ude42 even if allot of it you may already know.<\/p>\n

I have done the previous project here Three aces<\/a>, where it was to develop a menu system using simpleXML.<\/p>\n

This project is to communicate with the Yahoo finance<\/a> 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.<\/p>\n

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<\/a>, here is what phpmyadmin export created, I did not include the trigger on the users table that was creating a UUID<\/a>, because some versions of MySQL does not support that syntax<\/p>\n

\r\n--\r\n-- Table structure for table `stocks`\r\n--\r\n\r\nDROP TABLE IF EXISTS `stocks`;\r\nCREATE TABLE IF NOT EXISTS `stocks` (\r\n  `UID` int(30) unsigned NOT NULL DEFAULT '0' COMMENT 'Link to users table',\r\n  `SYMBOL` varchar(20) DEFAULT NULL,\r\n  `Quantity` int(20) DEFAULT NULL,\r\n  KEY `UID` (`UID`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n\r\n--\r\n-- Dumping data for table `stocks`\r\n--\r\n\r\n-- --------------------------------------------------------\r\n\r\n--\r\n-- Table structure for table `users`\r\n--\r\n\r\nDROP TABLE IF EXISTS `users`;\r\nCREATE TABLE IF NOT EXISTS `users` (\r\n  `UID` int(30) unsigned NOT NULL AUTO_INCREMENT,\r\n  `username` varchar(255) NOT NULL,\r\n  `pass` blob NOT NULL,\r\n  `cash` decimal(10,2) NOT NULL DEFAULT '10000.00' COMMENT 'the default value is the free gift',\r\n  `GUID` char(36) NOT NULL COMMENT 'Use this string to validate the user, if the value is 0, then validated',\r\n  PRIMARY KEY (`UID`),\r\n  UNIQUE KEY `username` (`username`)\r\n) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;\r\n\r\n--\r\n-- Constraints for table `stocks`\r\n--\r\nALTER TABLE `stocks`\r\n  ADD CONSTRAINT `stocks_ibfk_1` FOREIGN KEY (`UID`) REFERENCES `users` (`UID`) ON DELETE CASCADE;\r\n<\/pre>\n
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!.<\/p>\n
\r\n-- Triggers `users`\r\n--\r\nDROP TRIGGER IF EXISTS `users_insert`;\r\nDELIMITER \/\/\r\nCREATE TRIGGER `users_insert` BEFORE INSERT ON `users`\r\n FOR EACH ROW begin\r\nset NEW.guid = uuid();\r\nend\r\n\/\/\r\nDELIMITER ;\r\n<\/pre>\n
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.<\/p>\n
\r\ntheConnection = \r\n\t\t\t\tmysql_connect($connectionHost, $connectionUser, $connectionPW)) === FALSE)\r\n\t\t\t\tdie(\"Problems - connection to the database, please check\");\r\n\t\t\tif (mysql_select_db($connectionDB, $this->theConnection) === FALSE)\r\n\t\t\t\tdie(\"Problems - connected to database engine, but not the database\");\r\n\t\t}\r\n\t\t\r\n\t\tpublic function __destruct()\r\n\t\t{\r\n\t\t\tif ($this->theConnection)\r\n\t\t\t\tmysql_close($this->theConnection);\r\n\t\t}\r\n\t\t\r\n\t\tpublic function query($stringQuery)\r\n\t\t{\r\n\t\t\t$q = mysql_real_escape_string($stringQuery);\r\n\t\t\treturn mysql_query($stringQuery);\r\n\t\t}\r\n\t\t\r\n\t\t\/\/ if the database supports the result of returning the number of rows effected with the last call.\r\n\t\tpublic function rowsNumber($connection)\r\n\t\t{\r\n\t\t\treturn mysql_num_rows($connection);\r\n\t\t}\r\n\t\t\r\n\t\tpublic function rowsAffected()\r\n\t\t{\r\n\t\t\treturn mysql_affected_rows();\r\n\t\t}\r\n\t\t\r\n\t\tpublic function arrayResults($connection)\r\n\t\t{\r\n\t\t\treturn mysql_fetch_assoc($connection);\r\n\t\t}\r\n\t\t\r\n\t\tpublic function freeResult($connection)\r\n\t\t{\r\n\t\t\tmysql_free_result($connection);\r\n\t\t}\r\n\t\t\t\t\r\n\t\tpublic function startTransaction()\r\n\t\t{\r\n\t\t\t$this->query(\"START TRANSACTION\");\r\n\t\t}\r\n\t\t\r\n\t\tpublic function commitTransaction()\r\n\t\t{\r\n\t\t\t$this->query(\"COMMIT\");\r\n\t\t}\r\n\t\t\r\n\t\tpublic function rollbackTransaction()\r\n\t\t{\r\n\t\t\t$this->query(\"ROLLBACK\");\r\n\t\t}\r\n\t\t\r\n\t\tprivate $theConnection;\r\n\t};\r\n?><\/pre>\n
Shall does the classes next of the project and then the actual php files that do the user interaction.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \ud83d\ude42 even if allot of it you may already know. I have done the previous project here Three aces, where it was to … Continue reading CS75 – Ass1 – Finance<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,21],"tags":[246,249,271],"class_list":["post-1143","post","type-post","status-publish","format-standard","hentry","category-php","category-sql","tag-assignment-1","tag-cs75","tag-finance"],"_links":{"self":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/1143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/comments?post=1143"}],"version-history":[{"count":3,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/1143\/revisions"}],"predecessor-version":[{"id":1145,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/posts\/1143\/revisions\/1145"}],"wp:attachment":[{"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/media?parent=1143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/categories?post=1143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codingfriends.com\/index.php\/wp-json\/wp\/v2\/tags?post=1143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}