But wait! We don't need to store the date and time information AND the corresponding timestamp in the database. All we really need to store is the act_timestamp_1 and act_timestamp_2 because these timestamps contain the indiviual date and time information. So that reduces the table fields to just these six.

act_IDact_titleact_descact_timestamp_1act_timestamp_2act_status

Now that we know what pieces of data or information that will be stored for each activity, we also need to tell MySQL what type of data is in each field. For example, is it a numeric value or a character string? What is the size of the data? What will be the default value or can there be no data (null). MySQL has a list of data types (definitions or descriptive words) that you can use for this purpose. Here is a list of the most frequently used and you can find a complete list at the MySQL website (http://dev.mysql.com/doc/mysql/en/column-types.html). However, in our example, we will not use all of these. There are three types of data fields in our example (integer, variable character and text).

MySQL Data Types for Activities Table

act_ID

INT(11)

Numeric integer and maximum size 11 integers long

UNSIGNED

Cannot be a negative number

NOT NULL

The field cannot be empty

AUTO_INCREMENT

MySQL will increase this value by 1 each time a new record is added to the table

act_title

VARCHAR(255)

Variable character string and maximum size 255 characters long

NOT NULL

act_desc

TEXT

Variable character string up to 64 kb long

NULL

The field can be empty

DEFAULT NULL

act_timestamp_1

INT (50)NOT NULLDEFAULT 0

act_timestamp_2

INT (50)NOT NULLDEFAULT 0

act_status

INT(1)NOT NULLDEFAULT 0

PRIMARY KEY act_ID

The act_ID field is the unique identifier for this record

So, what would this table look like with data stored in it? Well, the data for each activity will be stored altogether and this group of data is called a record. So two activities will result in two records in the table.

12

PHPBob's Party

Bella PHP article dueBob's birthday party

11140596001114075000

......

10

Here is what the sql code will look like. As you can see, we first connect to the server as you learned in a previous tutorial. The field names and descriptions are placed between the parenthesis, i.e. between activities(...). Each field name and its description ends with a comma. The entire code is placed between the quotation marks for the sql statement. $sql = "";