useful programming resources
W3 Schools programming resources
Learning Ruby, an interactive Guide
An HTML validation tool, Very Important
Lists and CSS styles elegantly explained
Step by step lessons in PHP programming
Useful tools
Text Wrangler - General Purpose text editor
Transmit Text Edit and FTP client
The Total Validator validation tool
One of the great evil temptations in any computer project these days is to code first, plan later. This is a very bad temptation, and can cause immense headaches and heartaches down the the road. In Ye Olden Days when computer time was expensive and hard to get, programs were done entirely on paper before they were committed to electronics. Steve Wozniak reports in his book that he designed the Apple I operating system which was to run off of ROM chips, he designed the whole thing on paper first, and then took into the company that was to burn the chips, which were very expensive. It is a tribute to his genius that the chips worked the very first time. It is also a tribute to his hard work and meticulous planning. It is true we learn from our mistakes, and even more from the mistakes of others. But these life lessons are painful, and is probably better to do without them.
The employee list needs an unique identifier for each employee, a name, (which should be broken down into first, last and middle), an address, a phone number, a job title, and the employee identifier to whom this employee reports. (Names can be long, but the longest name I have seen is about 25 characters. But we are seeing more and more hyphenated names. A bit of padding here won't hurt.) And this is very much what the grammar of the create table statement will look like. NB, one very good best practice which will be a good mnemonic as you work is to make all table names plural and all column names singular.
When defining a field for your data base, you give the type of data and for many of the text, character, and number types you give the lengths as well. Here is a list of the data types:
mysql> create table staffers (emp_id smallint(5) unsigned not null auto_increment,
-> first_name varchar(22) not null,
-> middle_name varchar(17),
-> family_name varchar(30) not null,
-> area_code char(3),
-> exchange char(3),
-> pnumber char(4),
-> job_title varchar(15) not null,
-> reports_to smallint(5),
-> hire_date datetime not null,
-> primary key(emp_id));
Query OK, 0 rows affected (0.03 sec)
To get an idea of what our table looks like, we do a show columns command Most of the fields can't have null values. Reports_to will have to be null, because there will be one person to whom everyone reports.
mysql> show columns from staffers; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | emp_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(22) | NO | | NULL | | | middle_name | varchar(17) | YES | | NULL | | | family_name | varchar(30) | NO | | NULL | | | area_code | char(3) | YES | | NULL | | | exchange | char(3) | YES | | NULL | | | pnumber | char(4) | YES | | NULL | | | job_title | varchar(15) | NO | | NULL | | | reports_to | smallint(5) | YES | | NULL | | | hire_date | datetime | NO | | NULL | | +-------------+----------------------+------+-----+---------+----------------+
now to hire the staff. Two nice things about MySQL is that you can do multiple inserts at once, and that you don't have to define every value if the value is automatically filled in.
To add information to the table we use the INSERT command which has the syntax "Insert into table(fielda, fieldb, fieldn)values('data1','data2','datan'); like so
mysql> insert into staffers(
-> first_name,
-> middle_name,
-> family_name,
-> area_code,
-> exchange,
-> pnumber,
-> job_title) values (
-> 'Thomas'
-> ,'Leech',
-> 'Barnicle',
-> '503','986','8623',
-> 'Executive Director');
Query OK, 1 row affected, 2 warnings (0.24 sec)
and now to display the insert. I am using aliases in my select because otherwise the text wont fit on the page
mysql> select emp_id, concat(first_name,' ',middle_name,' ',family_name) as name,
-> concat('(',area_code,') ',exchange,'-',pnumber) as phone, job_title,
-> reports_to,hire_date from staffers;
+--------+-----------------------+----------------+-----------------+------------+---------------------+
| emp_id | name | phone | job_title | reports_to | hire_date |
+--------+-----------------------+----------------+-----------------+------------+---------------------+
| 1 | Thomas Leech Barnicle | (503) 986-8623 | Executive Direc | NULL | 0000-00-00 00:00:00 |
+--------+-----------------------+----------------+-----------------+------------+---------------------+
1 row in set (0.00 sec)
You remember me saying that planning is good, and reduces errors and frustration? Well that is great theory, but actual practice is somewhat different. If you want a job in the industry, adherence to the theory is a very good plan. But none of us walk on water, so there are ways to fix things that break. For example, the job title field. It seems it is too short. People like long titles, so in this case 15 characters is too short. The way some folks behave, 400 characters is too short, but I think 40 can handle most egos for the short term. So we have the Alter command. It has the syntax alter table tablename clause For example:
mysql> alter table staffers change job_title job_title varchar(42); Query OK, 1 row affected (0.11 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table staffers change hire_date hire_date timestamp not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0
The reason planning is good is because it reduces lots of work later. Now we need to fix his job title too
mysql> update staffers set job_title='Executive Director' where emp_id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select emp_id, concat(first_name,' ',middle_name,' ',family_name) as name,
-> concat('(',area_code,') ',exchange,'-',pnumber) as phone, job_title,
-> reports_to,hire_date from staffers;
+--------+-----------------------+----------------+--------------------+------------+---------------------+
| emp_id | name | phone | job_title | reports_to | hire_date |
+--------+-----------------------+----------------+--------------------+------------+---------------------+
| 1 | Thomas Leech Barnicle | (503) 986-8623 | Executive Director | NULL | 2009-01-05 13:18:58 |
+--------+-----------------------+----------------+--------------------+------------+---------------------+
1 row in set (0.05 sec)
That was cool. Now for some more staff. One thing to note is that the phone numbers are of type char. This makes formatting them easier, and it takes care of leading '0' issues. Character and text values have to have quotes around them when they are entered. Numbers don't. This can lead to frustration when entering values into a data base manually. Anyway, as you work with this, breathe deeply, take it easy, Rome wasn't built in a day. As you work with this, I recommend frequent breaks.
mysql> insert into staffers(first_name,middle_name,family_name,
-> area_code,exchange,pnumber,
-> job_title,reports_to) values
-> ('John','Jacob','Jingleheimer-Smith',
-> '971','228','3124',
-> 'Operations Administrator',1),
-> ('Percival','Astroglazer','Weasley',
-> '971','321','8686',
-> 'Executive Assistant',1),
-> ('Jayne', 'Michelle','Curley',
-> '503','555','1212',
-> 'Community Action Liason',1),
-> ('Ava','T','Moneypenny',
-> '971','322','2358',
-> 'Secretary/Reception',3),
-> ('Marian','M','Hill',
-> '503','221','0121','Librarian',2);
Query OK, 5 rows affected (0.54 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select emp_id, concat(first_name,' ',middle_name,' ',family_name) as name,
-> concat('(',area_code,') ',exchange,'-',pnumber) as phone, job_title,
-> reports_to,hire_date from staffers;
+--------+-------------------------------+----------------+--------------------------+------------+---------------------+
| emp_id | name | phone | job_title | reports_to | hire_date |
+--------+-------------------------------+----------------+--------------------------+------------+---------------------+
| 1 | Thomas Leech Barnicle | (503) 986-8623 | Executive Director | NULL | 2009-01-05 13:18:58 |
| 2 | John Jacob Jingleheimer-Smith | (971) 228-3124 | Operations Administrator | 1 | 2009-01-05 15:37:11 |
| 3 | Percival Astroglazer Weasley | (971) 321-8686 | Executive Assistant | 1 | 2009-01-05 15:37:11 |
| 4 | Jayne Michelle Curley | (503) 555-1212 | Community Action Liason | 1 | 2009-01-05 15:37:11 |
| 5 | Ava T Moneypenny | (971) 322-2358 | Secretary/Reception | 3 | 2009-01-05 15:37:11 |
| 6 | Marian M Hill | (503) 221-0121 | Librarian | 2 | 2009-01-05 15:37:11 |
+--------+-------------------------------+----------------+--------------------------+------------+---------------------+
6 rows in set (0.19 sec)
Things change. Folks quit, new folks are hired, the hierarchy shifts, so there needs to be ways of making changes. The DELETE is very similar to the INSERT command. It is also very good practice to make sure about your deletions before you do them.
mysql> select emp_id, concat(first_name,' ',middle_name,' ',family_name) as name
-> from staffers where family_name='Weasley';
+--------+------------------------------+
| emp_id | name |
+--------+------------------------------+
| 3 | Percival Astroglazer Weasley |
+--------+------------------------------+
1 row in set (0.05 sec)
mysql> delete from staffers where emp_id=3;
Query OK, 1 row affected (0.25 sec)
mysql> insert into staffers(first_name,middle_name,family_name,
-> area_code,exchange,pnumber,
-> job_title,reports_to) values
-> ('Uriah','T','Heep',
-> '503','345','6789','Executive Assistant',1),
-> ('Bunny','A','Fibbonacci',
-> '503','987','1597','Programmer',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select emp_id, concat(first_name,' ',middle_name,' ',family_name) as name,
-> job_title,reports_to from staffers order by reports_to, family_name;
+--------+-------------------------------+--------------------------+------------+
| emp_id | name | job_title | reports_to |
+--------+-------------------------------+--------------------------+------------+
| 1 | Thomas Leech Barnicle | Executive Director | NULL |
| 4 | Jayne Michelle Curley | Community Action Liason | 1 |
| 7 | Uriah T Heep | Executive Assistant | 1 |
| 2 | John Jacob Jingleheimer-Smith | Operations Administrator | 1 |
| 8 | Bunny A Fibbonacci | Programmer | 2 |
| 6 | Marian M Hill | Librarian | 2 |
| 5 | Ava T Moneypenny | Secretary/Reception | 7 |
+--------+-------------------------------+--------------------------+------------+
7 rows in set (0.05 sec)
Individual tables are all well and good, but by themselves they are only small pieces of what we need to know. We need to combine them, and that is the next step: Joins.