Lab 07 - PHP + MySQLi

   Ways to let down people nicely from being asked out:

        "I'd love to go out with you, but...
         - I have to floss my cat."
         - I have to stay home and see if I snore."
         - I never go out on days that end in `Y.'"
         - I'm attending the opening of my garage door."
         - the man on television told me to stay tuned."
         - I'm having all my plants neutered."
         - there are important world issues that need worrying about."

Overview

You will be creating a web page that will display information pulled from a database. This web page will exist under the secure '/internal' directory you created in lab06. The information in this database will contain username/IP address/timestamp combinations that are the dates and machines from which a user has connected to this machine. This information will be fake for the time being, but we will be populating the database in a future lab.

You will be using the mysqli package in PHP to do the connecting and querying of the database. You will be writing some simple HTML to display the contents of the database. You will specifically highlight new username/IP address combinations within the last week in Red text.

All of the information in this lab was built from the lecture slides in class, as well as the following websites:
http://devzone.zend.com/node/view/id/686 - MySQLi tutorial
http://us.php.net/mysqli - MySQLi reference
http://us.php.net/manual/en/ - General PHP reference

Tasks and Grading

This lab is due at 9:30am, Tuesday April 8th, 2008. - Be sure to do each of these tasks in order and make sure that they work before moving onto the next. The more you jump around and try to do things piecemeal, the more complex your mistakes become. You must complete all the following tasks for this lab:

Part 1 - Creating the database, user, and table

  1. Install the 'mysql-server' and 'php5-mysqli' packages.
  2. Restart your apache server so it will load the new mysqli library.
  3. Start the MySQL command line client with the following command (and hit enter for a blank password): 
    chris@coolname:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62 to server version: 5.0.24a-Debian_9ubuntu2.2-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
  4. Create a database called 'login_monitor' and set it to be the current database with the 'use login_monitor' command.
  5. Grant all privileges on all tables on this database to the user 'monitor' that can login from 'localhost' and uses the password 'wearewatchingyou'.
  6. Do not set a password for root. If you have, set the password to be empty.
  7. Run the 'FLUSH PRIVILEGES' command.
  8. Create a table called 'logins' with the following columns:
    • id - INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
    • username - VARCHAR(20)
    • ip - VARCHAR(15)
    • time - TIMESTAMP DEFAULT
  9. Now insert a row and select that row to see if it works properly: 
    mysql> INSERT INTO logins (username, ip, time) VALUES ('testuser', '1.2.3.4', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from logins;
+----+----------+---------+---------------------+
| id | username | ip      | time                |
+----+----------+---------+---------------------+
|  1 | testuser | 1.2.3.4 | 2008-04-01 00:42:34 | 
+----+----------+---------+---------------------+
1 row in set (0.01 sec)

mysql>

Part 2 - Loading data from a file

  1. Now that you have a database up and running with a user, quit the MySQL client by typing 'exit'.
  2. Download the file http://www.cs.colorado.edu/~schenkc/tabledump.txt using wget.
  3. Load the data from the file using the mysql command line client: 
    chris@coolname:~$ mysql -u root -p login_monitor < tabledump.txt
Enter password:
  4. Check that the data loaded properly: 
    chris@coolname:~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 84 to server version: 5.0.24a-Debian_9ubuntu2.2-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use login_monitor
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from logins;
+----+----------+-----------------+---------------------+
| id | username | ip              | time                |
+----+----------+-----------------+---------------------+
|  1 | chris    | 128.138.242.249 | 2008-03-18 15:53:26 | 
|  2 | chris    | 128.138.202.8   | 2008-03-18 15:57:25 | 
|  3 | chris    | 128.138.243.151 | 2008-03-18 15:57:37 | 
|  4 | bob      | 128.138.242.249 | 2008-03-18 15:57:53 | 
|  5 | chris    | 128.138.242.249 | 2008-03-18 15:57:59 | 
|  6 | joe      | 128.138.243.151 | 2008-04-01 07:30:00 | 
|  7 | chris    | 128.138.242.249 | 2008-04-03 17:47:31 | 
|  8 | joe      | 128.138.243.151 | 2008-04-08 03:31:07 | 
+----+----------+-----------------+---------------------+
8 rows in set (0.00 sec)

mysql>

Part 3 - Create a web page to display the data

This is the more interesting part of the lab. You now get to connect to a database using PHP and display information on a web page! I'm not testing your ability to do HTML, but I am testing your ability to write a little bit of code to pull information from the database and display it in a way that would be useful to me as a system administrator of a system.

Use the resources given to you! You have working PHP scripts from the last lab that you can use to build your PHP script for this lab.

  1. Create a file called 'logins.php' and put it in your /var/www/default/internal directory.
  2. Read through the tutorial link at the top of the lab and connect to your 'login_monitor' database using the mysqli library and user 'monitor' created in part 2.
  3. Using prepared statements and bind results, display all of the latest, unique username/ip address combinations in a table on your web page without duplicates. This means if the user 'chris' has logged in from the same IP address multiple times, only display the most recent login from that IP.
  4. Display all of the new username/ip address combinations within the last week in red text.

Part 4 - Testing

As always, you gotta test to see if your web page is working properly. The beauty of our particular database is that we can insert arbitrary data, including timestamp values. We also have a powerful language to perform queries, and we're barely even scratching the surface of what we can do with them. When the lab is graded, new data will be added to the database to test whether or not your web page is displaying as per the requirements above.

  1. If you want to see if you are successfully only printing new unique logins from the last week in red text, simply add in a login for a user with an old timestamp. The way to do this is as follows:

    mysql> INSERT INTO logins (username, ip, time) VALUES ('chris', '5.5.5.5', '2008-02-15 12:12:12');
Query OK, 1 row affected (0.01 sec)

mysql>

    Now you have an entry for user 'chris' from February 15th at 12:12pm. This entry should not show up in red text. Also, MySQL timestamps can be entered in two different ways:

    2008-02-15 12:12:12
20080215121212

    Those two entries are functionally equivalent and are accepted by MySQL. If you make a mistake in your syntax, the timestamp will be the default, which is time 0000-00-00 00:00:00. Bad juju!

  2. A lot of people think they need to do timestamp comparisons in PHP, when in fact you can simply do two different queries to the database. You can use the 'WHERE' clause in SQL to select entries with a certain timestamp, so you can simply do one query for entries within the last week, as well as entries before the last week.

Clarifications

Useful commands in the MySQL client

  • show databases; - Lists all of the databases your user has access to. Since you're logging in as root, you can see all databases.
  • use <dbname>; - Sets the current database to modify.
  • show tables; - Lists the tables in this database.
  • desc <tablename>; - Shows the properties of a table.
  • delete from <tablename> where <colname>='<value>'; - Delete an entry or entries from a table.

How to select only the most recent login from the logins table

This is pretty easy to do with the right SQL syntax. Basically you tell MySQL to give you the entries with the maximum timestamp. A maximum timestamp (interpreted as an integer value) will always be the most recent value as represented in the TIMESTAMP type.

mysql> SELECT username,ip,MAX(time) FROM logins GROUP BY username,ip;
+----------+-----------------+---------------------+
| username | ip              | MAX(time)           |
+----------+-----------------+---------------------+
| bob      | 128.138.242.249 | 2008-03-18 15:57:53 | 
| chris    | 128.138.202.8   | 2008-03-18 15:57:25 | 
| chris    | 128.138.242.249 | 2008-04-03 17:47:31 | 
| chris    | 128.138.243.151 | 2008-03-18 15:57:37 | 
| joe      | 128.138.243.151 | 2008-04-08 03:31:07 | 
| testuser | 123.123.123.123 | 2008-04-01 06:42:34 | 
+----------+-----------------+---------------------+
6 rows in set (0.00 sec)

mysql>

Options for figuring out when a login has occurred in the last 7 days

You can do this in a few different ways:

  1. You can change your SQL query to give you logins that match a timestamp that is older or newer than 7 days ago
  2. You can use PHP to do comparisons of timestamps returned to you in your query using the 'substr' function in php as well as 'mktime' for testing time differences

I'm sure there are other ways, but it's up to you to figure it out and get it working.