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

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 (https://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_1INT (50)
NOT NULL
DEFAULT 0
act_timestamp_2INT (50)
NOT NULL
DEFAULT 0
act_statusINT(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









This site needs an editor - click to learn more!



RSS
Related Articles
Editor's Picks Articles
Top Ten Articles
Previous Features
Site Map





Content copyright © 2023 by Diane Cipollo. All rights reserved.
This content was written by Diane Cipollo. If you wish to use this content in any manner, you need written permission. Contact BellaOnline Administration for details.