More about the id column

The tgborder table used in the TGB example in the slides has an id column. That id column is never used in TGB but an id column will be handy in quotes.php.

The create table in $a8/quotes.sql includes an id column: 

	create table quote
	(
	 id   bigint not null auto_increment primary key,
	 ...
	);
The keyword auto_increment provides a great convenience: If you add a row with insert but don't mention the id column, a value for id will be included, and its value will be largest-existing-id + 1.

The id column essentially serves as a serial number, a unique identifier for the row. If you take CSC 460 you'll learn all about keys—primary keys, foreign keys, synthetic keys, and more. A simplistic definition of primary key is that it's a value that uniquely identifies a row. (That's why we also have primary key in the SQL above.)

To see id at work, do this:

  1. Login to lectura, run mysql and select all the rows from the tgborder table in whm_cs337f13: 
    % mysql -h mysql -p -u cs337f13 whm_cs337f13
Enter password: (tednelson)

mysql> select * from tgborder;
+----+---------------------+----------+----------+
| id | time                | customer | servings |
+----+---------------------+----------+----------+
|  1 | 2013-11-16 12:15:35 | Dr. Hart |      100 |
+----+---------------------+----------+----------+
1 row in set (0.00 sec)
  2. Hit http://cgi.cs.arizona.edu/classes/cs337/fall13/tgbox.php and create an order for yourself. I'll do that now.

  3. Do the select again: 
    mysql> select * from tgborder;
+----+---------------------+----------+----------+
| id | time                | customer | servings |
+----+---------------------+----------+----------+
|  1 | 2013-11-16 12:15:35 | Dr. Hart |      100 |
|  2 | 2013-11-16 12:30:03 | whm      |        2 |
+----+---------------------+----------+----------+
2 rows in set (0.01 sec)

    Note that the newly added row, for whm at 12:30:03 has id=2 even though the following line, from rdbms slide 43, doesn't mention id at all: 

        $stmt = $conn->prepare("insert into tgborder(time, customer, servings) values(now(), :cust, :serv)");

    Note also that the current date/time is supplied using SQL's now() function. Use that in quotes.php, too!

Note that anybody that tries the example above will be adding to the same database, and you'll see their orders, too. (No offensive customer names, please!)

If you want to delete your order, go ahead! I'll do this, to delete mine: 

mysql> delete from tgborder where id=2;
Query OK, 1 row affected (0.04 sec)

mysql> select * from tgborder;
+----+---------------------+----------+----------+
| id | time                | customer | servings |
+----+---------------------+----------+----------+
|  1 | 2013-11-16 12:15:35 | Dr. Hart |      100 |
+----+---------------------+----------+----------+
1 row in set (0.00 sec)

If I'd said just delete from tgborder, it would have deleted all the rows. Feel free to try that, too!. We'll consider this database to be a free-fire zone but, again, no offensive data, please!

Extra credit

Earn a Bug Bounty point on assignment 8 by answering the following question via email to whm:

Above I say, ...a value for id will be included, and its value will be largest-existing-id + 1. That's a simplification. Rewrite it to make it precise.
(Deadline: The assignment's deadline. All are eligible, not just the first answer.)

SPOILER WARNING: BIG Hint for quotes.php (it has color:white—use select-all to view!)

Consider using a hidden field in a per-quote <form> that specifies the quote's id value. Then use $_POST["quote_id"] in a SQL update statement to up/down vote, or flag.