Personal Activities Calendar Program - What Goes Into MySQL Database Tables - 2

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_ID
act_title
act_desc
act_timestamp_1
act_timestamp_2
act_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 NULL DEFAULT 0
act_timestamp_2	INT (50) NOT NULL DEFAULT 0
act_status	INT(1) NOT NULL DEFAULT 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.

1
2

PHP
Bob's Party

Bella PHP article due
Bob's birthday party

1114059600
1114075000

...
...

1
0

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 = "";

← Back

	Editor Wanted BellaOnline's PHP Editor