Step 1: Creating the Users Table - PHP

Start up MySQL and login as the root user by issuing this command from the shell prompt:

$ mysql -u root -p

You should see MySQL started:

Your MySQL connection id is 412 to server version: 3.22.30
Type 'help' for help.
mysql>

Now select the mymarket database:

mysql> USE mymarket;

Now you're in the mymarket database, let's create the user table:

mysql> CREATE TABLE users (
->   username     char(16) not null,
->   password     char(32) not null,
->   priv         char(5) not null,
->   firstname    varchar(64) not null,
->   lastname     varchar(64) not null,
->   email        varchar(128) not null,
->   phone        varchar(32) not null,
->   address      varchar(255) not null,
->   PRIMARY KEY (username),
->   UNIQUE email (email)
-> );

Notice the constraints we've put on the users table, the username is the primary key (which makes sense, you should be able to identify a user record based on the username). The email address has a unique constraint as well because we don't want duplicate email addresses.

Now let's add a record to create the root user with the password password:

mysql> INSERT INTO users VALUES (
->    'root',
->    '5f4dcc3b5aa765d61d8327deb882cf99',
->    'admin',
->    'System',
->    'Administrator',
->    'root@mymarket.com',
->    '555-5555',
->    '123 5 Avenue'
-> );

Notice the password looks a bit wierd, 5f4dcc3b5aa765d61d8327deb882cf99. This is the MD5 hash of the the word "password", I won't go into details here, but the important thing to note is that it's a one-way algorithm and it always produces a 32 character string.

That's it, we have a users table to track our users, and one administrative account so we can try logging in and out of the system.