MySQL storage engines

In MySQL, there is a couple of different storage engines that can be used for different tables within a database. There is myiasm, innodb, cvs, memory, merge, BDB (BerkeleyDB) example, federated, archive and blackhole.

The main two are that are used are

The main differences between them both are speed, the myiasm is more designed for speed to access the database tables but does not support the rollback where you are able stop the current set of instructions and rollback the updates/inserts to where it was at a previous time, because of this the programmer is the person to make sure that what they want to do is and will happen, otherwise the programmer will have to sort out the tables that could out of stink. But compared to the innodb, where it supports the rollback function, it does have to take a hit somewhere and that is the speed.

The main database that I use is the myiasm, purely because of the speed of access, and also it appears that most web/applications appear to use that. But if you running some circuital data then of course I would use innodb database type purely for data.

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.

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.

Web page inputs and insert into database – Part 2

As from here, where I outlined the javascript, html part of the exercised here is the php and mysql parts of the problem.

Here is the table that I created within MySQL

CREATE TABLE `User` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(50) DEFAULT NULL,
  `lastName` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(50) DEFAULT NULL,
  `phoneNum` VARCHAR(20) DEFAULT NULL,
  `guid` VARCHAR(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

As from my other post about MySQL triggers, so to find out more information about why please check out that page, and here is the trigger that I created for the above table.

CREATE TRIGGER User_insert 
BEFORE INSERT ON USER 
FOR EACH ROW 
BEGIN 
        SET NEW.guid = uuid(); 
END

The reason why I created this for was because within the exercise they asked “would forward planning, i.e. adding a unique code to the user details that could be used to validate their email address”, which by using a uuid, you can use that as a unique code.

so the only part left is the PHP that will connect to the database and also sanitize the inputs within sql insert. So created a class that has a constructor that will open a database connection to a database

      private $dbLink;
      /* connect to the database*/
      function __construct($host = "localhost", $user = "user", $password = "password", $database = "database")
      {
	$this->dbLink = mysql_connect($host, $user, $password);
	if (!$this->dbLink) die("no database present");
	if (!mysql_select_db($database, $this->dbLink))
	  die("no database within the database");
      }

Here I try to sanitize the insert string so that it will use the mysql_real_escape_string, this will use a php function that helps with SQL injections, also I am using the stripslashes and then trim the string to make sure that there is no white spaces (or any valid text left).

      /* santize the input for a mysql database */
      public function santizeInput($theStr)
      {
	return trim(stripslashes(mysql_real_escape_string($theStr)));
      }

and then to insert the data, just use the mysql_query and the sanitize function above to insert data into the database.

      /* insert the data into the database */
      public function insertData($sqlData)
      {
	mysql_query($this->santizeInput($sqlData), $this->dbLink) or die("Error inserting data");
      }

And here is where I get the data from the form post from the web page and then double sanitize the data and then create a database link, which in-turn use to insert the data.

    // could use foreach loop $_POST inputs, but I personally prefer to pick them up.
    $firstname = $_POST['firstname'];
    $secondname =$_POST['secondname'];
    $email = $_POST['email'];
    $phonenumber = $_POST['phonenumber'];
 
    /* could do additional checks on input incase it is sent via backend POST and not via the webpage,  could do with regular expression as well ? */
    $db = new databaseAccess("localhost", "username", "password", "database");
    /* can santize the inputs to make sure that there is some data to "play" with */
    $firstname = $db->santizeInput($firstname);
    $secondname =$db->santizeInput($secondname);
    $email =$db->santizeInput($email);
    $phonenumber = $db->santizeInput($phonenumber);
 
    if (checkLength($firstname) && checkLength($secondname))
    {	
	$sql = "insert into User (firstname, lastname, email, phoneNum) values (\"$firstname\",\"$secondname\",\"$email\", \"$phonenumber\")";
	$db->insertData($sql);
	echo "Data inserted";
    }

I did write within the exercise that since someone may try and post the data to the server within using the webpage (naughty people that they are!!) you could also check the inputs again for there data validity.

Here is the full code for the web page in total.

<?php
    class databaseAccess
    {
      private $dbLink;
      /* connect to the database*/
      function __construct($host = "localhost", $user = "user", $password = "password", $database = "database")
      {
	$this->dbLink = mysql_connect($host, $user, $password);
	if (!$this->dbLink) die("no database present");
	if (!mysql_select_db($database, $this->dbLink))
	  die("no database within the database");
      }
 
      /* disconnect */
      function __destruct()
      {
	if (!$this->dbLink) mysql_close($this->dbLink);
      }
 
      /* santize the input for a mysql database */
      public function santizeInput($theStr)
      {
	return trim(stripslashes(mysql_real_escape_string($theStr)));
      }
 
      /* insert the data into the database */
      public function insertData($sqlData)
      {
	mysql_query($this->santizeInput($sqlData), $this->dbLink) or die("Error inserting data");
      }
    }
 
    function checkLength($theStr)
    {
      if (strlen($theStr) > 0) 
	return true; 
      else 
	return false;
    }
 
    // could use foreach loop $_POST inputs, but I personally prefer to pick them up.
    $firstname = $_POST['firstname'];
    $secondname =$_POST['secondname'];
    $email = $_POST['email'];
    $phonenumber = $_POST['phonenumber'];
 
    /* could do additional checks on input incase it is sent via backend POST and not via the webpage, not sure if SOAP are looking for that as well ? 
      could do with regular expression as well ? */
    $db = new databaseAccess("localhost", "User", "PW", "Test");
    /* can santize the inputs to make sure that there is some data to "play" with */
    $firstname = $db->santizeInput($firstname);
    $secondname =$db->santizeInput($secondname);
    $email =$db->santizeInput($email);
    $phonenumber = $db->santizeInput($phonenumber);
 
    if (checkLength($firstname) && checkLength($secondname))
    {	
	$sql = "insert into User (firstname, lastname, email, phoneNum) values (\"$firstname\",\"$secondname\",\"$email\", \"$phonenumber\")";
	$db->insertData($sql);
	echo "Data inserted";
    }
 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<script language="javascript">
  /* check the length of the element, focus is none present */
  function lengthCheck(elem, thename)
  {
    if (elem.value.length> 0) 
      return true;
    else
    {
      alert("Please insert the " + thename);
      elem.focus();
    }
  }
 
  /* check a email address, using regular expression */
  function emailChecker(elem)
  {
    var reg = /^[\w\-\.\+]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid email address");
      elem.focus();
      return false;
    }
  }
 
  /* check against a phone number. a number being between 11-15 numbers*/
  function checkPhone(elem)
  {
    var reg = /^[0-9]{11,15}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid phone number");
      elem.focus();
      return false;
    }
  }
 
  function checkInputs()
  {
    // obtain inputs
    var firstname = document.getElementsByName("firstname").item(0);
    var secondname = document.getElementsByName("secondname").item(0);
    var email= document.getElementsByName("email").item(0);
    var phonenum = document.getElementsByName("phonenumber").item(0);
    /* check the inputs */
    if (lengthCheck(firstname, "first name")) 
      if (lengthCheck(secondname, "second name"))
	if (emailChecker(email))
	  if (checkPhone(phonenum))
	    return true;
    return false;
  }
</script>
</head>
<body>
<form name="input" action="insertData.php" method="post" onSubmit="return checkInputs()">
First Name :
<input type="text" name="firstname"/>
 
Second Name : 
<input type="text" name="secondname"/>
 
Email : 
<input type="text" name="email"/>
 
Phone number : 
<input type="text" name="phonenumber"/>
 
<input type="submit" value="Submit"/>
</form>
</body>
</html>

If you save that as insertData.php then open up within your web-server. You will be able to insert data into a database with some javascript / php checks.

Web page inputs and insert into database

Also from the other posts, Join files together and simple calculator , I was also asked to do

“Accept form input for a user registration form, and store results in a MySQL table, using PHP. Applicant should demonstrate a knowledge of input validation, and using server-side code to avoid sql injection exploits. The user data should include first name, last name, email, an phone number.

Usage of Javascript pre-validation would be a plus, as would forward planning, i.e. adding a unique code to the user details that could be used to validate their email address. Suitable MySQL table schema should be demonstrated.”

To start with, I started at the data insert within a web page

<body>
<form name="input" action="insertData.php" method="post" onSubmit="return checkInputs()">
First Name :
<input type="text" name="firstname"/>
Second Name : 
<input type="text" name="secondname"/>
Email : 
<input type="text" name="email"/>
Phone number : 
<input type="text" name="phonenumber"/>
<input type="submit" value="Submit"/>
</form>
</body>

Which takes in the required input’s and also within the form HTML tag, but before sending to the back end PHP part of the exercise, there was a requirement to do some Javascript checking on the inputs.

So here is the javascript that will check the inputs, within the form onsubmit action I call this function checkInputs and the return value (true/false) is returned which gives the form either a action to post back to the server (true return) or to wait for the user correct there inputs (false return).

    var firstname = document.getElementsByName("firstname").item(0);

I get the data from the webpage, getElementsByName (which since it is a name there could be x amount of elements with that name, so I want the first one (.item(0))

  function checkInputs()
  {
    // obtain inputs
    var firstname = document.getElementsByName("firstname").item(0);
    var secondname = document.getElementsByName("secondname").item(0);
    var email= document.getElementsByName("email").item(0);
    var phonenum = document.getElementsByName("phonenumber").item(0);
    /* check the inputs */
    if (lengthCheck(firstname, "first name")) 
      if (lengthCheck(secondname, "second name"))
	if (emailChecker(email))
	  if (checkPhone(phonenum))
	    return true;
    return false;
  }

and then after getting the elements, I then call different additional functions that I did write within javascript to check the inputs gained. Here I check the length of a element passed within the one of the parameters within the function parameter list, with using objects you can access the objects value.length (javascript object of a element) and also use the focus function with the element which will focus the element on the webpage for the user to know where to check there input (also with a alert window to say why, e.g. “please insert some data”.)

  function lengthCheck(elem, thename)
  {
    if (elem.value.length> 0) 
      return true;
    else
    {
      alert("Please insert the " + thename);
      elem.focus();
    }
  }

Here is a way of using regular expression to check email inputs, basically it first checks to make sure there is a name before the “@” and also a at between 2 and 4 names with a “.” better them.

  /* check a email address, using regular expression */
  function emailChecker(elem)
  {
    var reg = /^[\w\-\.\+]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid email address");
      elem.focus();
      return false;
    }
  }

Here is a similar way as above for checking a phone number input between 11-15 numbers

  /* check against a phone number. a number being between 11-15 numbers*/
  function checkPhone(elem)
  {
    var reg = /^[0-9]{11,15}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid phone number");
      elem.focus();
      return false;
    }
  }

This is the web page part, and here is the second part where I insert the data into database with php.

But here is the full web page part of the first part.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<script language="javascript">
  /* check the length of the element, focus is none present */
  function lengthCheck(elem, thename)
  {
    if (elem.value.length> 0) 
      return true;
    else
    {
      alert("Please insert the " + thename);
      elem.focus();
    }
  }
 
  /* check a email address, using regular expression */
  function emailChecker(elem)
  {
    var reg = /^[\w\-\.\+]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid email address");
      elem.focus();
      return false;
    }
  }
 
  /* check against a phone number. a number being between 11-15 numbers*/
  function checkPhone(elem)
  {
    var reg = /^[0-9]{11,15}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid phone number");
      elem.focus();
      return false;
    }
  }
 
  function checkInputs()
  {
    // obtain inputs
    var firstname = document.getElementsByName("firstname").item(0);
    var secondname = document.getElementsByName("secondname").item(0);
    var email= document.getElementsByName("email").item(0);
    var phonenum = document.getElementsByName("phonenumber").item(0);
    /* check the inputs */
    if (lengthCheck(firstname, "first name")) 
      if (lengthCheck(secondname, "second name"))
	if (emailChecker(email))
	  if (checkPhone(phonenum))
	    return true;
    return false;
  }
</script>
</head>
<body>
<form name="input" action="insertData.php" method="post" onSubmit="return checkInputs()">
First Name :
<input type="text" name="firstname"/>
 
Second Name : 
<input type="text" name="secondname"/>
 
Email : 
<input type="text" name="email"/>
 
Phone number : 
<input type="text" name="phonenumber"/>
 
<input type="submit" value="Submit"/>
</form>
</body>
</html>

MySQL – triggers

In MySQL version 5 onwards you can use Triggers, triggers are a nice was of checking values within a insert/update/delete process. A trigger happens either just before the insert actually happens into the database, or just after

Lets say that you create a table as below

CREATE TABLE testTable 
(id INT NOT NULL AUTO_INCREMENT, 
  guid VARCHAR(36), 
  name VARCHAR(100), 
PRIMARY KEY (id));

and then if you want to insert a name into the name column you could do

INSERT INTO testTable (name) VALUES ("thename");

but you are not inserting the anything into the guid column because you can force a value into that column with a trigger (the id is already creating a value with the primary key, auto_increment).

To create a trigger you need to set the delimiter to “//” because within sql you need to use the “;” which is the standard delimiter (when the MySQL will try and execute the query)

delimiter //
CREATE TRIGGER testTable_insert 
BEFORE INSERT ON 'testTable' 
FOR EACH ROW 
BEGIN 
    SET NEW.guid = uuid(); 
END;
delimiter ;

“CREATE TRIGGER” is just like creating a table, apart from you are creating a trigger, so the next value is the trigger name, normally it is the table name with what you are doing, e.g. inserting

“BEFORE INSERT ON” means before you actually insert the data into the database (it is in a hold area as such). the next value is the actual table that you are linking this trigger to.

“FOR EACH ROW” means each row of the insert, since you can insert x amount of lines into a table.

the sql code is between the “BEGIN” and “END”, and all is what is being set is the guid column within the “NEW” (this is the new row to be inserted) and setting that value to uuid() which is a mysql function.

so before you would have inserted into a table and the table would have looked like

ID GUID Name
1 thename

but once the trigger is in place and you did the same process again of inserting a new name

INSERT INTO testTable (name) VALUES ("a new name");
ID GUID Name
1 thename
1 3ace82c-2cf1-11df-b1c3-00a0d1a1240a a new name

Blob to store data in mysql database

To store data within a blob in a database can be a good thing at times because then you can just copy the database from one place to another and use the access rights on the database to restrict access to the “files” within the database.

There could be a few reasons why you want to store the data within a blob in the database, but here how the basics would work.

To start with you have to create a database and a table to store the data/file within the blob, of course if you have already created the database and/or the tables then alter as you think, but here is the basics.

  CREATE DATABASE phptestplace;
  CREATE TABLE storingData (id INT NOT NULL auto_incremenet, lblob BLOB, PRIMARY KEY (id));

And then within a php file you can access the database and a file.

  $link = mysql_connect('localhost', 'username', 'userpassword');
  if (!$link) {
      die('Could not connect: ' . mysql_error());
  }
// alter to your database name
  mysql_select_db("phptestplace", $link);

and now access the file and read in file

  $filename = "filetoload.txt";
  $handle = fopen($filename, "r");
  $contents = fread($handle, filesize($filename));
  fclose($handle);
 
// to insert into the database you need to add in the slashes for characters like / \ etc.
  $contents = addslashes($contents);

to insert into the blob you just, change the table and table name to what may have called it.

  $sqlquery = "insert into storingData(largeblob) values ('$contents')";
  mysql_query($sqlquery) or die("ERROR");*/

to get the data back (I am calling back the last inserted value into the table)

// get the data into a result variable
  $return = mysql_query ("select lblob from storingData where id = (select max(id) from storingData)") or die("LLL");
// get the contents of the blob from the return variable (it returns a array of data) and the list takes out the data from a array each part at time.
  list($newcontents) = mysql_fetch_array($return);

and then store the data from the database pull into a file, I have called it newfile.txt, but it is up to you.

  $fp = fopen('NEWFILE.txt', 'w');
  fwrite($fp, $newcontents);
  fclose($fp);

Of course can do it via a web page, using a HTML FORM enctype=”multipart/form-data” within the form tag otherwise it may not work.