WPII -PHP - Intro

8 - PHP:MySQL - Create a table

We're in mysql now. Let's see what databases we have available to use.

Type

mysql> show databases; (NOTICE! a mysql statement must ALWAYS end with a semi-colon. If you hit Enter without typing a semi-colon you can just type the semi-colon and hit Enter again. You can type mult-line commands, and sometimes that makes it easier for you to keep track of what your typing. So you can make a command as many lines long as you need. When you have it all entered, add the semi-colon, hit Enter and the command will execute.)

This will show us all the databases available to your user name. To choose a database to work with, type

mysql>use myDatabase;

Now every command we enter will act on the chosen database. So now, let's look at what tables are available within this database. Type

mysql>show tables;

This will return something like this:

   +---------------------+
   | Tables in employees |
   +---------------------+
   | employee_data |
   +---------------------+
   1 row in set (0.00 sec)

Create a table

The CREATE statement is used to create a table in MySQL. The general syntax to create a table in MySQL is:

CREATE TABLE tableName
(
fieldName1 dataType(size) [NULL | NOT NULL]
fieldName2 dataType(size) [NULL | NOT NULL]
);

If NULL is specified, the field is allowed to be left empty. If NOT NULL is specified, the field must be given a value. In the absence of either a NULL or NOT NULL, NULL is assumed.

Data Types

Data Type Example Description

CHAR(size) fieldName CHAR(10)
Stores up to 255 characters. If the content is smaller than the field size, the content will have trailing spaces appended.

VARCHAR(size) fieldName VARCHAR(100)
Stores up to 255 characters, and a minimum of 4 characters. No trailing spaces are appended to the end of this datatype. MySQL keeps track of a delimiter to keep track of the end of the field.

TINYTEXT fieldName TINYTEXT
Stores up to 255 characters. Equivalent to VARCHAR(255).

TEXT fieldName TEXT
Stores up to 65,535 characters. An Index can be created on the first 255 characters of a field with this data type.

MEDIUMTEXT fieldName MEDIUMTEXT
Stores up to 16,777,215 characters. An Index can be created on the first 255 characters of a field with this data type.

LONGTEXT fieldName LONGTEXT
Stores up to 4,294,967,295 characters. An Index can be created on the first 255 characters of a field with this data type.

Note: The maximum size of a string in MySQL is currently 16 million bytes, so this data types is not useful at the moment.

ENUM
fieldName ENUM('Yes', 'No')

Stores up to 65,535 enumerated types. The DEFAULT modifier may be used to specify the default value for this field.

INT fieldName INT
Stores a signed or unsigned integer number. Unsigned integers have a range of 0 to 4,294,967,295, and signed integers have a range of -2,147,438,648 to 2,147,438,647. By default, the INT data type is signed. To create an unsigned integer, use the UNSIGNED attribute.

fieldName INT UNSIGNED

The ZEROFILL attribute may be used to left-pad any of the integer with zero's.

fieldName INT ZEROFILL

The AUTO_INCREMENT attribute may be used with any of the Integer data types. The following example could be used to create a primary key using the AUTO_INCREMEMNT attribute.

fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

TINYINT fieldName TINYINT
Stores a signed or unsigned byte. Unsigned bytes have a range of 0 to 255, and signed bytes have a range of -128 to 127. By default, the TINYINT data type is signed.

MEDIUMINT fieldName MEDIUMINT
Stores a signed or unsigned medium sized integer. Unsigned fields of this type have a range of 0 to 1,677,215, and signed fields of this type have a range of -8,388,608 to 8,388,607. By default, the MEDIUMINT data type is signed.

BIGINT fieldName BIGINT
Stores a signed or unsigned big integer. Unsigned fields of this type have a range of 0 to 18,446,744,073,709,551,615, and signed fields of this type have a range of -9,223,372,036,854,775,808 to 9,223,327,036,854,775,807. By default, the BIGINT data type is signed.

FLOAT fieldName FLOAT
Used for single precision floating point numbers.

DOUBLE fieldName DOUBLE
Used for double precision floating point numbers.

DATE fieldName DATE
Stores dates in the format YYYY-MM-DD.

DATETIME fieldName DATETIME
Stores dates and times in the format YYYY-MM-DD HH:MM:SS.

TIMESTAMP(size) fieldName TIMESTAMP(14)
Automatically keeps track of the time the record was last ammended. The following table shows the formats depending on the size of TIMESTAMP.

TIMESTAMP Formats

Size Format

2 YY

4 YYMM

6 YYMMDD

8 YYYYMMDD

10 YYYYMMDDHH

12 YYYYMMDDHHMM

14 YYYYMMDDHHMMSS

TIME fieldName TIME
Stores times in the format HH:MM:SS.

YEAR(size) fieldName YEAR(4)
Stores the year as either a 2 digit number, or a 4 digit number, depending on the size provided.

Primary Keys

A primary key is a field in a table that uniquely identifies a record. The PRIMARY KEY attribute may be used when defining the field name to create a primary key, as in the following example.

fieldName INT AUTO_INCREMENT PRIMARY KEY

Alternatively, the PRIMARY KEY attribute may be used to specify the primary key after the defining the fields, as in the following example:

CREATE TABLE product
(
prodID INT UNSIGNED AUTO_INCREMENT,
description VARCHAR(100),
PRIMARY KEY(prodID)
);

Providing Default Values

Default values may be provided for fields using the DEFAULT attribute. The following example uses a default value of index.html, should a Page name not be provided for the search table.

CREATE TABLE search
(
Category VARCHAR(100) NOT NULL,
Page VARCHAR(20) DEFAULT 'index.html',
Directory VARCHAR(255) NOT NULL,
LinkName VARCHAR(255) NOT NULL,
Keywords MEDIUMTEXT NOT NULL,
Desription VARCHAR(255) NOT NULL,
PRIMARY KEY(Page, Directory)
);

Examining Tables

You can examine the structure of a table using the DESCRIBE statement. This is useful when you need to remember how a table is set up, what data types are used, field sizes, etc. The following displays the structure of the search table.

mysql> DESCRIBE myTable;

Let's build our table now

For this exercise, we'll build a table named "userInfo" that will have 3 fields:

id (This will be the primary key, the field the database will use to keep track of this record. It must be unique, so we will set it to auto-increment. The database takes care of it's value for us that way.)
firstName (Notice there are no spaces in the field name. There never are.)
lastName (And notice I'm using camel-hump notation. You don't have to do that, but it is very common and pretty easy to work with.)

The SQL INSERT Statement

The INSERT statement is used to insert records into a table. The keywords used in an INSERT statement are:

INSERT- Inserts data into a table
INTO - Specifies the name of the table to insert the data
VALUES - The data for the fields in the table

So let's start populating our table.

INSERT INTO userInfo (firstName,
      				lastName)
   		VALUES ('George',
   				'Harrison');

Do this four times, so you have four records in your table. Use John Lennon, Paul McCartney and Ringo Starr to finish up the table.

Characters that require Escaping

The following characters have a special meaning to MySQL, and will need to be escaped by putting a backslash before them in order that they are interpreted correctly.

'     Single quote
"     Double quote
\     Blackslash
%   Percent
_    Underscore

The following example shows how you would escape an underscore in the string, "last_modified".

'last\_modified'

You can escape single quotes by placing two single quotes together, as in the following example.

'Don''t care!'

If you have to deal with quotes in text strings, they can be tricky, so be very careful and very aware of where all your double and single quotes are.

(from juicystudio)

Select statement

Table of Contents