Users search for a quote by typing in a search query. Users can also select from amoung the most popular or most recent searches in the application. The system then searches its database and returns matching quotes: The application supports automatic suggestions as users type their search (similar to Google suggest). The suggestions will present the top searches made by the current user (based on their session ID). Below the suggestions based on a user's past searches, popular searches made by all users will appear. Users can rate each quote between 1 and 5 stars. Each quote displays the community average rating for that particular quote: The application also supports a simple administrator interface that allows past searches and ratings to be deleted either globally or only for a specific session ID:

• sudo /etc/init.d/mysql stop
• sudo apt-get install mysql-server-5.6
• sudo /etc/init.d/mysql start
• mysql --version

• A table for keeping track of every search made. Each row should contain the normalized search text, the IP address of the user, the session ID of the user, and a date/time stamp of the search. While the IP address and date/time stamp aren't currently needed by the user interface, in the future you anticipate using these fields for reporting, banning spammers, etc.
• A table for holding the set of quotes searched up by the application. This table tracks the actual text of the quote, the author of the quote, and the category of the quote. You need to populate this database with the 76K quotes contained in the tab delimited file quotes_parsed.zip.
• A table for keeping track of the star rating made by any user (identified by session ID) on any of the quotes in the system.

• You need to submit a text file quotes.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 the tables. The script should provide a completely fresh start, dropping any existing copy of the database and user. It should also populate the table containing the actual quotes.
• From the MySQL console, you can execute commands in a file using source /home/myuser/command.sql;
• You can load the quotes into your database using the LOAD DATA LOCAL INFILE command. For example: LOAD DATA INFILE 'quotes_parsed.txt' INTO TABLE ... . MySQL only seems happy if you place the quotes_parsed.txt file in the directory for the database. My database was called quotes, and so I had to put the file in /var/lib/mysql/quotes.
• Searching for quotes makes use of MySQL's full-text search feature.
• Search should be over the text of the quote, author, and category.
• Search should be a logical AND of all the normalized words in the query. This can be achieved using + before the words and IN BOOLEAN MODE. For example: WHERE MATCH (quote, author, category) AGAINST ('+charlie +rose ' IN BOOLEAN MODE)
• By default, MySQL full-text search ignores stop words and short words. You should disable these features by adding the following to your MySQL config file under the [mysqld] section:

  ft_stopword_file = ""
  ft_min_word_len = 1
  innodb_ft_min_token_size = 1
  innodb_ft_enable_stopword = 0
  

  On my VM, the config file is located at /etc/mysql/mf.cnf. Note: you will need to restart MySQL and also reload the table containing the full-text index for this change to take effect.

• Isolate all your database functionality to a PHP file named quotesDB.php. This file can be included from other PHP pages as needed.
• Database details (e.g. user, password, database name) should be easy to change (i.e. they should appear only once in your PHP code).
• For security, create a separate user account for use by the quote web application (i.e. don't use the root login!). This user should only have read/write access to the quotes related database tables.
• You can structure your PHP database support code however you see fit. But here are the functions I had:

  // Return an array of quotes based on the given set of search word(s), limit to max total quotations
  function getQuotes($search, $max)
  
  // Return an array of the most frequent commands given the current prefix, limit to at most max results.
  // If sessionID is set, include up to sessionMax results that are personalized based on the sessionID.
  function getTopMatches($prefix, $max, $sessionID = "", $sessionMax = 0)
  
  // Return an array of the newest searches, limit to at most max results
  function getTopRecentMatches($max)
  
  // Add a new search to the database from a user with the given ip and sessionID
  function addSearch($search, $ip, $sessionID)
  
  // Add/change rating for the quote identified by quoteID for user identified by sessionID
  // score is the star rating: 1, 2, 3, 4 or 5.
  function rateQuote($sessionID, $quoteID, $score)
  
  // Return the average score of this quote, "" if no score for the quote yet.
  // If sessionID not "", we return current score set by this user.
  function getQuoteScore($quoteID, $sessionID = "")
  
  // Deletes searches from the database, if sessionID is set only delete entries for that sessionID
  function deleteSearches($sessionID = "")
  
  // Deletes ratings from the database, if sessionID is set only delete entries for that sessionID
  function deleteRatings($sessionID = "")
  
  

• The page should be named quotes.php
• A search is executed if the user hits enter in the text field or if the Go button is clicked.
• Whenever the search 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 searches 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.
• The specifics of normalization are likely to change and should be isolated to one place in your code.
• At the bottom of the page, there should be a link to the admin.php page.

• Suggested commands must have a prefix matching the normalized characters typed thus far.
• Up to 20 query suggestions are made.
• The top-10 positions are reserved for the most frequent commands made by the current user (based on the user's session ID).
• The bottom-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, fill the extra slots using searches by all users.
• The total number of suggestions and the number devoted to the user's past searches should be easily configurable in your code.
• Users can select a suggested search using the mouse. As a command is selected, it is highlighted in some way. Clicking on a suggested search results in that search 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 appear.
• You might want to check out this Ajax Suggest Tutorial.

• The most popular search section and the recent search sections each show at most N things.
• N should be easily configurable by changing no more then one line of code at the top of quotes.php.
• These section may show less than N searches if fewer then N total unique searches have been issued.
• The most popular search section never contains identical searches.
• The most recent search section may contain identical searches. This may occur if repeated identical searches have happened in the last N searches.
• The text shown in both sections should reflect the normalized version of the command (lower case and stripped of invalid punctuation).
• Clicking on anything in the most popular or recent searches result in that search being executed. This also results in the frequency of that search increasing. Thus clicking repeatedly on the same search will result in it working its way to the top of the most popular list.
• Whenever the user executes a search (either using the text field or the most popular or recent links), this command should appear at the top of the most recent search list.

• The text, author and category of each quote should be displayed
• The search returns at most M quotes.
• M should be easily configurable by changing no more then one line of code at the top of quotes.php.
• The order are in whatever order the quotes were returned by MySQL's full-text search.
• Results should contain all the words in the query in the quote, author, or category.
• The search is case insensitive.
• Searches should be possible with words containing an apostrophe (e.g. contractions like "didn't").
• Quote star ratings are in {1, 2, 3, 4, 5}.
• The average community star rating (to within 1/2 a star) should be displayed for each quote.
• The current star rating assigned by the current user (identified by session ID) should be displayed for each quote.
• A user can add or change the rating assigned to a quote to a new value in {1, 2, 3, 4, 5}.
• A user only gets to have one rating for any particular quote (the user's rating only counts once in the community average rating calculation).
• Adding/changing a rating should immediately update the visual feedback showing the average community and user rating.
• Adding/changing a rating should not cause the existing quotes to disappear or reorder themselves.
• There should be some way to distinguish quotes that have not yet been rated by either the community or by the current user.

• The page should be called admin.php.
• The session ID text field(s) are pre-populated with the session ID of the current web user.
• Clicking the Delete searches button causes all past searches for the session ID given by the text field to be deleted. If the session ID field is blank, then it should delete all past searches.
• Clicking the Delete ratings button causes all past ratings for the session ID given by the text field to be deleted. If the session ID field is blank, then it should delete all past ratings.
• At the bottom of the page, there should be a link to the quotes.php page.

Page last updated: April 01, 2015