• Update the database of software:
    sudo apt-get update
• Upgrade installed packages on your computer:
    sudo apt-get upgrade
• Shutdown your Apache server:
    sudo /usr/local/apache2/bin/apachectl stop
• Install MySQL, set the root password the same as the Networks user password:
    sudo apt-get install mysql-client mysql-common mysql-server php5-mysql
• Start your Apache server and verify via phpinfo() that mysqli is enabled.

• We will create a simple test database and display results from it from a PHP page.
• Log into your MySQL server as root:
    mysql -u root -p
• Create a test database, make it the active database:
  create database test;
  use test;
• Create a table to hold name/value pairs:

  CREATE TABLE data 
  (
    id    INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(255) NOT NULL,
    value VARCHAR(255) 
  );

• Populate the table with some example name/value pairs, for example:
    INSERT INTO data (name, value) VALUES ("hello", "world");
• Test out a query against your new table:
    SELECT * from data;
• Create a PHP page that does the same query, displaying the results in an HTML table. Here is a skeleton page that makes all the MySQL specific calls, but doesn't generate any output:

  <?php
  	$mysqli = mysqli_connect("localhost", "root", "password", "test");
  	if (mysqli_connect_errno())
  	{
  		printf("Connect failed: %s\n", mysqli_connect_error());
  		exit();
  	}
  
  	$sql = "SELECT * FROM data";
  	$res = mysqli_query($mysqli, $sql);
  	if ($res)
  	{
  		while ($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC))
  		{
  			$name = $newArray['name'];
  			$val  = $newArray['val'];
  		}
  		mysqli_free_result($res);
  	}
  	mysqli_close($mysqli);
  ?>
  

• Every executed command should be logged in database. The database entry should contained the normalized command text, the IP address of the user, the session ID of the user, and a date/time stamp of the command.
• You should isolate all your database functionality to a single PHP file commandDB.php that is included from your other pages.
• Database details (e.g. user, password, database name) should be easy to change (i.e. they should appear only once in your PHP code).
• You should create a separate user account for use by the web site (i.e. don't use the root login!).
• You need to submit a text file command.sql containing the sequence of SQL command needed to setup your server's database from scratch. This setup includes creating the database, the web user account, and creating table(s). The script should provide a completely fresh start, dropping any existing copy of the database and user.
• From the MySQL console, you can execute commands in a file using source /home/myuser/command.sql;
• You can structure your PHP database support code however you see fit. But here are the signatures of the functions I had:

  function getTopMatches($prefix, $max = 0, $sessionID = "", $sessionMax = 0)
  function addCommand($command, $ip, $sessionID)
  function deleteCommandsAll()
  function deleteCommandsSession($session_id)
  function deleteCommand($command)
  function normCommand($command)
  

This page allows an administrator to see all past commands and their frequency, delete all commands, delete all commands for a given session ID, or deleted selected commands. For testing purposes, you may want to add functionality so you can add new commands to the database (this is optional and won't be graded).

• The page should be called admin.php.
• The page should have a link to the command.php page.
• Clicking the Delete all button causes all commands to be erased from the database.
• The session ID text field is pre-populated with the session ID of the current web user.
• Clicking the Delete by session ID button causes all commands for the session ID given by the text field to be deleted.
• All unique commands appear in a list showing the frequency of the command, the command text, and containing a delete checkbox.
• Clicking the Delete button causes all commands matching the text of the checked commands to be deleted.
• A message indicating the last admin action taken is shown at the top of the page.

The command page consists of a text box in which users can enter commands, a list of the top-10 most popular commands, and a place where the last command made by the current user is shown. The command page links to the admin page. You might want to check out this Ajax Suggest Tutorial.

• The page should be named command.php
• A command is executed if the user hits enter in the text field or if the Go button is clicked.
• Whenever the command page loads, focus should be on the input text field.
• The interface tracks users via a session ID cookie. This cookie should persist for one-year.
• All commands are normalized as follows:
  • Converted to lowercase.
  • Leading and trailing whitespace is trimmed.
  • Internal whitespace is collapsed to a single space.
  • Characters besides a-z, 0-9, and ' are stripped.
• As the user types a command, suggested commands are made are follows:
  • Suggested commands must have a prefix matching the normalized typed characters.
  • Up to 20 command suggestions are made.
  • The top-10 positions are reserved for the most frequent commands made by the current user.
  • The lower-10 positions are the most frequent commands made by all users.
  • If a command is both a most frequent user command and a frequent command from all users, it should only appear once in the top user command section.
  • If the user has made less than 10 unique commands, additional all user commands are used to fill in the extra slots.
  • The total number of suggestions and the proportion devoted to user commands should be easily configurable in your code.
• Users can select a suggested command using the mouse. As a command is selected, it is highlighted in some way. Clicking on a suggested command results in it being executed.
• If the mouse is not in the suggestion list, no item is highlighted and clicking has no effect.
• If the current normalized text in the field is not a prefix of any past command, no suggestions should appear.
• The most popular section should show the ten most frequent commands. It may show less than ten if less than ten total unique commands have been issued. The top commands are across all users.
• Clicking on any of the most popular commands results in that command being executed. This also results in the frequency of that command increasing. Thus clicking repeatedly on the same most popular command will result in it working its way to the top of the list.
• Whenever the user executes a command (either using the text field or the most popular command links), the last command area should show the executed command. The text shown in last command should reflect the normalized version of the command (lower case and stripped of invalid punctuation).
• There should be a link to the admin.php page.
• Only do this after everything else is working (including the admin interface). Make the cursor keys behave as follows:
• If focus is in the text field, the down arrow causes the first suggestion (if any) to be highlighted. Further down arrows moves the highlighted suggestion.
• If focus in the text field, the up arrow causes the last suggestion (if any) to be highlighted. Further up arrows moves to the previous suggestion.
• Using the up/down arrows cause the text field to change to the currently highlighted suggestion.
• Down arrowing past the final suggestion causes focus to change back to the text field with no suggestion highlighted. The text of the field should change back to the text as it was prior to selecting suggestions. Similarly, up arrowing to before the first suggestion should do the same.
• If the mouse has highlighted a suggestion, the up/down arrow key should be relative to the currently highlighted suggestion.
• Hitting the right arrow while a suggestion is highlight should have no effect.
• Hitting the left arrow should cause the cursor to move within the text field.

Page last updated: May 15, 2013