Foreach(Array Expression As $Value) Statement s1

Server Side Programming with PHP CPAN 542

Lecture 12-1: MySQL Tutorial

In order to use MySQL on the HAL server, you must first logon to your account on the server using telnet. From the Start Menu of Windows, click Run and then telnet to HAL as follows:

At the prompt of sparcy, login using your Humber email (for example abcd0000) and your 8-digit student number. After you login, you should see the following screen:

In order to use mysql database, type mysql at the prompt line and press enter. You will receive a welcome message, and you will be prompted with mysql line command.

You can use the show databases command to see what databases are available:

The database that you can use is test, and you must use the use command. You will receive a message stating that database changed.

If you have privilege, you can create your own database using the command:

mysql> create database db_name;

You can view the available table using the show tables command:

If there are no tables, you will receive the message empty set.

At any time you can quit the database using the command:

mysql> quit;

To find out which database is currently selected, use the database() function:

mysql> select database();

If you decide you don't want to execute a command that you are in the process of entering, cancel it by typing \c:

To create a table use the create table command. You should be aware of the following data type categories supported by mysql: numeric types, data and time types and string types.

Following are some of the data types supported by Mysql , where:

M indicates the maximum display size. The maximum legal display size is 255.

D applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M minus 2. Square brackets, [ and ], indicate a part of type specifies that are optional. Note that if you specify ZEROFILL for a column, MySQL will automatically add the UNSIGNED attribute to the column.

· INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

· DOUBLE[(M,D)] [ZEROFILL]

A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to1.7976931348623157E+308. The M is the display width and D is the number of decimals

· FLOAT[(M,D)] [ZEROFILL]

A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number.

· DATE A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.

· DATETIME A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.

CHAR(M)

A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of a M is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set.

· VARCHAR(M)

A variable-length string. Trailing spaces are removed when the value is stored. The range of M is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion.

· DECIMAL[(M[,D])] [ZEROFILL]

An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: 'unpacked' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the minus sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of Decimal values is the same as for Double, but the actual range for a given Decimal column may be constrained by the choice of M and D. If D is left out, it's set to 0. If M is left out, it's set to 10.

· ENUM ('value1','value2',...)

An enumeration. A string object that can have only one value, chosen from the list of values, 'value1', 'value2', ..., NULL or the special "" error value.

An ENUM can have a maximum of 65535 distinct values.

· SET('value1','value2',...)

A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members.

· BLOB is a binary large object that can hold a variable amount of data.

· TEXT is the same as BLOB but it is case insensitive. In comparison, BLOB is case sensitive.

Assume for example that we want a table called persons that has two columns id and name. ID is the primary key, and we want it to be auto incremented:

create table persons (id int(5)not null auto_increment ,name varchar(30),primary key(id));

After creating your table, you need to populate it. The load data and insert statements are useful for this.

You could create a text file 'Per.txt' containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the create table statement. For missing values you can use NULL values.

To represent these in your text file, use \n.

To load the text file 'per.txt' into the persons table, use this command:

mysql> load data local infile "Per.txt" into table persons;

When you want to add new records one at a time, the insert statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the create table statement.

Suppose you need to add a new record to persons. Use an insert statement like this:

insert into persons values (NULL, 'Antonio Paz');

Since we specified auto increment for id, the database server will assign the next available one.

One of the important features of mysql is that you can also run it in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:

mysql> mysql < batch-file-in

You can catch the output in a file for further processing:

mysqll> mysql < batch-file-in > batch-file-out

Examples

Example 1:

In this example, we will create a table called student1 that uses enum type with other types:

create table student1 (id int not null, name varchar(30), year enum("first","second","third"), primary key (id));

Then we can describe the table as follows:

After that we can insert values as follows:

insert into student1 values (1,'John Steve',1);

insert into student1 values (2,'Smith William',2);

insert into student1 values (3,'Robert Scotte',3);

Then we can query the table as follows:

Example 2:

In the following example, we will create a second table called sports that uses set type to represent the kind of sports with other types including the student id so we can join them together later:

create table sports (num int not null, id int, kind set( "swimming","tennis", "football"), primary key (num));

Then we can describe the table as follows:

After that we can insert values as follows:

insert into sports values(1,1,”tennis,footbal”);

insert into sports values(2,2,””);

insert into sports values(3,3,”tennis”);

Then we can query the table as follows:

And we can join the two tables as follow:

Reference: http://www.mysql.com