Page 244 - Open Soource Technologies 304.indd

Web Technologies-I

Notes “NOT NULL” requires a little explanation. When a field has never had any data posted into it,
it is considered NULL. This is not the same as containing “0” since “0” is a character. A field
that is created as “NOT NULL” must have data in it. Lastly, “auto_increment” means that the
number in subsequent records will be generated by adding “1” to the previous record.

The next two fields “guest_name” and “guest_email” are defined as “varchar(50)”. This type
of field is a variable length character string. This means the name can be anywhere from 0 to
50 characters long.
The “guest_time” data type is “timestamp(14)”. The timestamp datatype is automatically updated
by the database when the record is updated. The number of characters defines the format of the
data. 14 characters includes a 4 digit year and a 2 digit month, day, hour, minute and second.
The “guest_message” type is “text”. This is a non-indexed data type that allows for 65,535
characters to be stored in it. By non-indexed it means that the data is not readily searchable by
the database.
The last line defines the “guest_id” as the primary key. Every table should have a “PRIMARY
KEY” defined. This is the unique identifier for each record in the table.
The command to look at all the tables in a database is:
SHOW TABLES;

You can look at the properties of the fields in a table by issuing this statement:
DESCRIBE TABLES;
There is also a number of ways to alter table and field properties using the “ALTER TABLE”
function.
Deleting a table is as simple as issuing the following command.
DROP TABLE guests;

Adding Data
Now that we have the table created we will have to add data to it. To put a record indirectly
to test the sql statement, you will use in the program. Here is the SQL query to add a record
to the database:
INSERT INTO guests ( guest_id, guest_name, guest_email, guest_time, guest_message) values(

0000,’Tony’,’tony@awtrey.com’,NULL,’This is how it works!’);
The statement starts by telling the database we want to insert data into the “guests” table. Next
we list the fields we are going to update and finally, the data we want inserted in each field.
You have to match the order of the listed fields and the data.
The “guest_id” field is autoincrementing, so it does not really matter what you insert into it.
we put 0000 just because we wanted to. Strings like the ones for “guest_name”, “guest_email”
and “guest_message” must be quoted. To put a quote character in the field you must put a “”
(backslash) in front of it. The “guest_time” field is set automatically, but weinsert the special
NULL character.
If that is added to the database correctly you will see:
Query OK, 1 row affected (0.00 sec)

Searching the Table
Getting the data back from the database is even easier. This statement will return all the data
in the “guests” table:

238 LOVELY PROFESSIONAL UNIVERSITY