Building a cool website using HTML, PHP and MySQL

Normalization, begining MySQL and security

I am doing my MySQL examples on my home computer (A Mac running system X, which comes with my MySQL installed) and this computer is not serving or accepting requests from the outside world. The actual web pages are hosted by a very nice company who offers the latest stable releases of MySQL and PHP. For actual work that goes out to the great wide world, this is the best solution. For quick MySQL demonstrations, my home computer works fine. For actual real world work, I use the hosting co

One of the very real problems out there in the world is malicous types who like to do various horrible things to other people's web spaces. And one of the nastier things they can do is something called SQL injection. This means they can put SQL code into your forms or your http requests that cause damage to your web page, and worse, can cause damage to your visitor's computer.

The hosting companies usually offer different user accounts for your MySQL work. Your application will sign on as a user on the host computer. It is best practice to give that user the minimum level of authority to do their work, and no more. This is called "Least Privilege" and it prevents some of the worst damage that can happen to your site. I will discuss this more as we go along, but I want that you should always think that when dealing with anything coming into your site, you should assume the user is either malicious or stupid. And dangerous. Of course, most of them are not, but in the computer world, like the regular world, we need to protect ourselves from the .0000018% who are.

Starting MySQL

My SQL has a slightly strange grammar, which starts with the verb, works with the object of the verb, and finishes with the modifiers to the verb. MySQL commands have the form similar to "Eat your vegetables quickly." of course, they can get extremely verbose, but that is the basis for the command. My SQL commands can get very long, so the practice is to run them over several lines. No matter how long the command, it terminates with a semicolon.

The most familiar of the MySQL commands is the SELECT command.

mysql> Select * from authors;
+-----------+-----------+-----------+
| writer_id | efname    | elname    |
+-----------+-----------+-----------+
|         1 | Heron     | Carvic    | 
|         2 | Adam      | Smith     | 
|         3 | Yuna      | Kagesaki  | 
|         4 | Tom       | Clancy    | 
|         5 | Paul      | De Kruif  | 
|         6 | Richard   | Powell    | 
|         7 | Connie    | Willis    | 
|         8 | Manning   | Coles     | 
|         9 | Hisaya    | Nakajo    | 
|        10 | Eric      | Flint     | 
|        11 | Larry     | Ullmahn   | 
|        12 | Donald    | Westlake  | 
|        13 | Henry     | Mencken   | 
|        14 | Milton    | Friedman  | 
|        15 | Rose      | Friedman  | 
|        16 | Robert    | Ardrey    | 
|        17 | Charles   | Darwin    | 
|        18 | Joseph    | Telushkin | 
|        19 | Edmund    | Love      | 
|        20 | Lee       | Babin     | 
|        21 | Frederick | Hayek     | 
|        22 | Rudyard   | Kipling   | 
|        23 | Anthony   | Hope      | 
|        24 | JK        | Rowlling  | 
|        25 | PG        | Wodehouse | 
|        26 | Damon     | Runyon    | 
|        27 | Konrad    | Lorenz    | 
|        28 | Mark      | Twain     | 
|        29 | Ambrose   | Bierce    | 
|        30 | George    | Frazier   | 
|        31 | Ullysses  | Grant     | 
|        32 | Tecumsah  | Sherman   | 
|        33 | MC        | Beaton    | 
+-----------+-----------+-----------+
33 rows in set (0.01 sec)

This command essentially tells the computer that you want to know all about all the authors. You can get more specific and ask for particular information. In this case, you can use the WHERE clause.

mysql> select * from authors where elname="Friedman";
+-----------+--------+----------+
| writer_id | efname | elname   |
+-----------+--------+----------+
|        14 | Milton | Friedman | 
|        15 | Rose   | Friedman | 
+-----------+--------+----------+
2 rows in set (0.08 sec)

and sometimes a dump of the whole list is just too much. You can limit the list by specifying a particular start point, and how many records you want. So if you want five records at a time, for example, you use the LIMIT clause to work through your list in easily digestible chunks.

mysql> select * from authors limit 5,5;
+-----------+---------+--------+
| writer_id | efname  | elname |
+-----------+---------+--------+
|         6 | Richard | Powell | 
|         7 | Connie  | Willis | 
|         8 | Manning | Coles  | 
|         9 | Hisaya  | Nakajo | 
|        10 | Eric    | Flint  | 
+-----------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from authors limit 10,5;
+-----------+--------+----------+
| writer_id | efname | elname   |
+-----------+--------+----------+
|        11 | Larry  | Ullmahn  | 
|        12 | Donald | Westlake | 
|        13 | Henry  | Mencken  | 
|        14 | Milton | Friedman | 
|        15 | Rose   | Friedman | 
+-----------+--------+----------+
5 rows in set (0.03 sec)

And there are other cool functions as well. The text functions, like "CONCAT" are fussy about punctuation. There can be no space between the command and the parenthesis. There are also helpful tools such as the alias. In this case, you use the alias to specify a name for a CONCATed field. And you can also tell the computer to sort the list on a particular field, or set of fields. Empty spaces are also fields. In the next example we tell the system to combine two fields into one, with a space in the middle. We aslo ask it to sort on the last name useing the ORDER BY clause.

mysql> select concat(efname,' ',elname)as name
     -> from authors order by elname limit 20,9;
+------------------+
| name             |
+------------------+
| Henry Mencken    | 
| Hisaya Nakajo    | 
| Richard Powell   | 
| JK Rowlling      | 
| Damon Runyon     | 
| Tecumsah Sherman | 
| Adam Smith       | 
| Joseph Telushkin | 
| Mark Twain       | 
+------------------+
9 rows in set (0.00 sec)

Say you notice an error in your database. No one walks on water, and mistakes happen. Which is one of the advantages of using a normalized database. A mistake happens only once, and needs to be corrected only once, using the UPDATE command.

in this case, it is good practice to check to make sure you have the right information before making a change. So, I do a selection making sure I have the correct writer id before making the correction. The UPDATE function works similarly to the SELECT function.

mysql> select concat(efname,' ',elname) as name,
     -> writer_id
     -> from authors
     -> where writer_id=24;
+-------------+-----------+
| name        | writer_id |
+-------------+-----------+
| JK Rowlling |        24 | 
+-------------+-----------+
1 row in set (0.01 sec)

mysql> update authors set efname="Joanne  K",                                   
     -> elname="Rowling"
     -> where writer_id=24;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select concat(efname,' ',elname) as name,
     -> writer_id
     -> from authors
     -> limit 20,8;
+-------------------+-----------+
| name              | writer_id |
+-------------------+-----------+
| Frederick Hayek   |        21 | 
| Rudyard Kipling   |        22 | 
| Anthony Hope      |        23 | 
| Joanne  K Rowling |        24 | 
| PG Wodehouse      |        25 | 
| Damon Runyon      |        26 | 
| Konrad Lorenz     |        27 | 
| Mark Twain        |        28 | 
+-------------------+-----------+
8 rows in set (0.00 sec)

Resources

Building a cool website using HTML, PHP and MySQL

Normalization, begining MySQL and security

Starting MySQL