Web Trainings Institute

PHP & MySQL

1

Web Trainings Institute

Table of Contents Installation Introduction PHP and MySQL PHP Installation MySQL Installation Apache Installation Configuring and Testing software’s. XAMPP and WAMPP Installation

PHP Basics

Embedding PHP in HTML Embedding HTML in PHP Introduction of Variable Using variables Understanding Data Types Using Operators Writing Statements and Comments Using Conditional Statements If(), else if() and else if condition Statement Switch() Statements Using the while() Loop Using the do() Loop Using the for() Loop

Arrays and Custom Functions Creating an Array 2

Web Trainings Institute

Modifying Array Elements Processing Arrays with Loops Grouping Form Selections with Arrays Using Array Functions Creating User-Defined Functions Defining and Invoking Functions

Creating basic programs with PHP Reading and Writing Files Reading Data from a File Writing Data to a File Testing File Attributes. Managing Sessions and Using Session Variables Creating a Session and Registering Session Variables Destroying a Session Storing Data in Cookies Setting Cookies Retrieving Cookie Data Dealing with Dates and Times Executing External Programs

Simple Project without using Database

Learning MySQL Understanding an RDBMS Understanding a Relational Database Understanding Tables, Records, and Fields Understanding Primary and Foreign Keys 3

Web Trainings Institute

Understanding SQL and SQL Queries Understanding Database Normalization Using the MySQL Command-Line Client

Working with Databases and Tables Creating Databases Creating Tables Specifying Field Data Types Selecting the Most Appropriate Data Type Adding Field Modifiers and Keys Selecting a Table Type Altering Tables Altering Table and Field Names Altering Field Properties Adding and Removing Fields and Keys Altering Table Types Backing Up and Restoring Databases and Tables Backing Up Databases and Tables Restoring Databases and Tables from Backup Dropping Databases and Tables Viewing Database, Table, and Field Information

Editing Records and Performing Queries Inserting Records Editing and Deleting Records Performing Queries Retrieving Specific Columns Filtering Records with a WHERE Clause 4

Web Trainings Institute

Using Operators Sorting Records and Eliminating Duplicates Limiting Results Using Built-In Functions Grouping Records Joining Tables Using Subqueries Using Table and Column Aliases

Using the MySQL Security System Understanding the Need for Access Control Understanding How MySQL Access Control Works Assigning, Revoking, and Viewing User Privileges Working with User Accounts and Password Creating and Removing User Accounts Altering User Passwords

Using PHP with MySQL Managing Database Connections Performing Queries Processing Result Sets Queries Which Return Data Queries That Alter Data Handling Errors Using Ancillary Functions

Creating Forms and Validations with JavaScript HTML Basics 5

Web Trainings Institute

Creating Forms with HTML

Java Script Introduction to Java Scripting Types of Java Scripts Variables, operators, loops Using Objects, Events Common java script functions Using JavaScript in Dreamweaver Java Script Validations

Project Requirements analysis of Project Documentation of Project Project code Execution Project Testing

Introduction PHP PHP stands for "PHP: HyperText Preprocessor". PHP is a server side scripting language for making logic driven websites. Ever wonder how they made that "contact us" form on their site, which sends out emails? Well, they used PHP. Or, how they made that image upload tool? Well, they used PHP. PHP written scripts can use databases to keep track of your customer's and visitors activities on your site, send out periodical newsletters to your subscribers, upload files or images and drive the content on your site dynamically. The possibilities are endless. Most of the social networking websites you visit are writing in PHP. Yep! PHP is that powerful.

PHP is an excellent choice for Web programming. It has many advantages over other languages, including other Web-oriented languages. To get a very general understanding of how the common Web 6

Web Trainings Institute

programming languages compare, let’s compare them. ASP is Microsoft’s Web programming environment. (It’s not a language itself because it allows the programmer to choose from a few actual languages, such as VBScript or JScript.) ASP is simple, but too simple for programs that use complex logic or algorithms.

PHP’s primary use certainly isn’t to track résumés anymore; it has grown to be able to do that and just about anything else. To giveyou a better idea of what PHP can do, here are some of its common uses:

•

User forms, Forums, Blogs

•

CMS, Shopping carts, Portals, Classified Sites, Social Networking



Custom application development

How PHP works ? PHP sits between your browser and the web server. When you type in the URL of a PHP website in your browser, your browser sends out a request to the web server. The web server then calls the PHP script on that page. The PHP module executes the script, which then sends out the result in the form of HTML back to your browser, which you see on the screen. Here is a basic php diagram which illustrate the process.

PHP Process Diagram

7

Web Trainings Institute

Installation of PHP 5 1. Download & Unpack Download and install PHP from http://www.php.net/downloads.php, you should grab the newest 5.x.x Windows Binaries zip package that will work on apache.

My file was named: php-5.2.9-1-Win32.zip 2. Unzip php. In my case, I unzipped to: C:\php\ 3. Rename C:\php\php.ini-dist it to php.ini 4. Edit your php.ini Open php.ini in a text editor and scroll down about halfway through the file and look for doc_root then change it to point to whatever your Apache DocumentRoot is set to. In my case: doc_root = "C:\public_html"

Scroll down about 7 more lines and change the extension_dir from extension_dir = "./" to the location of the ext directory after you unzipped PHP. in my case: extension_dir = "C:\php\ext"

If you are going to be using your server for testing, i recommend (this is optional) you search and change the error reporting to give you info on any errors, notices, or bad coding. If you want to enable this type of stuff, search for error_reporting for and change: error_reporting = E_ALL & ~E_NOTICE & ~E_STRICT to error_reporting = E_ALL | E_NOTICE | E_STRICT 5. Editing Apache Conf File 8

Web Trainings Institute

Using Notepad open httpd.conf (should be start-menu shortcut "Apache HTTP Server 2.2 > Configure Apache Server > Edit the Apache httpd.conf Configuration File"). Either at the very beginning or end of the file add the following lines: (NOTE: be sure to change BOTH C:/php parts to the directory you installed your php to) LoadModule php5_module "C:/php/php5apache2_2.dll" AddType application/x-httpd-php .php PHPIniDir "C:/php"

Note: If you installed the older Apache 2.0, instead of the above lines, you will need to use the lines listed on the bottom step of the Apache 2.0 tutorial. 6. [OPTIONAL] Editing Apache Conf File (part 2) To get apache to automatically look for an index.php, search httpd.conf for DirectoryIndex (about line 212) and add the files you want apache to look for when a directory is loaded (if it doesn't find any of these files, it displays folder contents). Mine looks like: DirectoryIndex index.php index.html default.html 7. Testing Restart Apache if it is already running (if it doesn't start or you get errors, use your Apache "Test Configuration" shortcut in the Start Menu to see why).

To test your PHP simply create a test.php file in your Apache "DocumentRoot" folder (E:\public_html\ in my case). In your test.php file, type these 3 lines and then load the file in 9

Web Trainings Institute

your browser like http://localhost/test.php (you should get a whole long list of php variables, settings, etc): 8. Documentation Suggestion One weird thing I have noticed about PHP is that it does not come with documentation of any kind. If you are a php developer/programmer, I suggest you download the documentation. Downloads can be found on http://www.php.net/download-docs.php and I personally recommend the "English - Windows HTML Help" (chm) version as the search is so handy, although they all contain the same information. Further Installation Guide : http://php.net/manual/en/install.windows.php

Introduction to MySQL MySQL is currently the most popular open source database server in existence. On top of that, it is very commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications MySQL has the following features 

Ease of Use



Scalability and Portability



Security



Reliability



Wide Application Support

10

Web Trainings Institute

Installation of MySQL 5.1 1. Download MySQL v5.1 Download MySQL from http://dev.mysql.com/downloads/mysql/5.1.html Just make sure you get a Windows Essentials (x86) binary version. (after you click the "pick a mirror," if it asks you to login, scroll down a little and click the "No thanks, just take me to the downloads!" link under the login form)

My file was named: mysql-essential-5.1.33-win32.msi 2. Install MySQL v5.1 Run the MSI file and use the following settings o

Typical Setup

o

Skip Sign-Up

o

make sure "Configure the MySQL Server now" is checked

o

"Detailed Configuration"

o

"Developer Machine"

o

"Multifunctional Database"

o

"InnoDB Tablespace Settings" - leave everything default

o

"Decision Support (DSS)/OLAP"

o

make sure "Enable TCP/IP Networking" is checked and leave the port number at 3306 (at this point, if you have a firewall, it will usually try to access itself on the localhost) Leave "Enable Strict Mode" checked

o

"Standard Character Set"

o

check "Install As Windows Service" and I would recommend leaving "Launch the MySQL Server automatically" checked. 11

Web Trainings Institute

o

enter your root password and I would recommend leaving "Enable root access from remote machines" unchecked

o

then hit "execute" and it'll install and set it up.

3. Getting PHP5 to work with MySQL - Official Info Unfortunately PHP5 removed built-in support for MySQL. To get it to work, the easiest way is to copy the mysql library file by hand. Open the folder you unzipped your PHP to. Copy the libmysql.dll file (should be located like C:\php\libmysql.dll ) into your Window's System folder (usually C:\Windows\System32\ although might be C:\WinNT\System\ or something).

Then open up your php.ini in a text editor and search for ;extension=php_mysql.dll and remove the ; infont of that line.

Restart Apache and see if you get any errors. If it complains about "php_mysql.dll" either your extension directory isn't correct or windows can't find libmysql.dll 4. Testing MySQL Testing MySQL is not exactly easy. However, here are the common connection strings for PHP. I recommend downloading phpMyAdmin and using it to create and manage your databases, etc.

PHP Connection test
// username and password to log onto db server $dbUser='root'; 12

Web Trainings Institute

$dbPass='';

// name of database $dbName='test';

$link = mysql_connect("$dbServer", "$dbUser", "$dbPass") or die("Could not connect"); print "Connected successfully
"; mysql_select_db("$dbName") or die("Could not select database"); print "Database selected successfully
";

// close connection mysql_close($link); ?>

Introduction to Apache The Apache HTTP Server, commonly referred to as Apache is web server software notable for playing a key role in the initial growth of the World Wide Web. This server is used in LAMP Development Enviroment. The Apache Web Server is where it all started, and, although now just one project among many, it has continued to develop new and exciting features over its entire 15 year history. Installation of Apache 2.2.X 1. Download & Unpack Go to http://www.apache.org/dist/httpd/binaries/win32/ and choose a mirror. You want the latest win32-x86-no_ssl.msi "MSI Installer Package" release.

My file was named: apache_2.2.11-win32-x86-no_ssl.msi 13

Web Trainings Institute

NOTE: Apache sometimes moves the "quick download" link to the win32 binary. The long way to officially find it is Download > Other files > binaries folder (not the "Binary Releases" info link at the top) > win32 folder > then the latest win32-x86-no_ssl.msi release 2. Install When you install Apache, you'll get a prompt for "Server Information." Here is the settings I used: Network Domain: localhost Server Name: localhost Admin Email: (your any email)

[checked]: for All Users, on Port 80, as a Service 3. Starting/Stopping Apache After installing, Apache2 automatically starts. The The

icon in the System Tray means it started.

icon means the "Monitor Apache Servers" is running, but Apache2 isn't started.

You can easily start/stop/restart Apache and Apache2 via that icon in your System Tray. If you get "The requested operation has failed!" error while starting apache use the "Test Configuration" shortcut in the Start Menu to find the error (if the text window pops up then closes before you can read it, your config file is fine). 4. Testing Now the ultimate test. To see if it's serving. Open your browser and head to: http://127.0.0.1/ or http://localhost/ 14

Web Trainings Institute

If it shows the It works! you have your server software installed and running. 5. Making Apache point to your files Using Notepad open C:/Program Files/Apache Software Foundation/Apache2.2/htdocs (in the start-menu there should also be a "Apache HTTP Server 2.2 > Configure Apache Server > Edit the Apache httpd.conf Configuration File" shortcut) and search for DocumentRoot. Change it from something like DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs" to the location where your HTML files and site are located. In my case: DocumentRoot "C:/public_html" NOTE: The first time i installed Apache2, i accidentally changed ServerRoot. Make sure you change the correct line which is DocumentRoot and which is down about line 150. 6. Then scroll down about one page and change: to point to the same location you set DocumentRoot to in the last step. Restart apache to make the changes take effect

15

Web Trainings Institute

LAMP ENVIRONMENT

16

Web Trainings Institute

PHP Basics Embedding PHP in HTML One of the nicer things about PHP is that, unlike CGI scripts, which require you to write server-side code to output HTML, PHP lets you embed commands in regular HTML pages. These embedded PHP commands are enclosed within special start and end tags, which are read by the PHP interpreter when it parses the page.

Here is an example of what these tags looks like:

Example

Q: This creature can change color to blend in with its surroundings. What is its name?

A: Chameleon'; ?> 17

Web Trainings Institute

Comments in PHP



Variables Variables are the building blocks of any programming language. A variable can be thought of as a programming construct used to store both numeric and nonnumeric data. The contents of a variable can be altered during program execution, and variables can be compared and manipulated using operators

PHP supports a number of different variable types—Booleans, integers, floating point numbers, strings, arrays, objects, resources, and NULLs—and the language can automatically determine variable type by the context in which it is being used. Every variable has a name, which is preceded by a dollar ($) symbol, and it must begin with a letter or underscore character, optionally followed by more letters, numbers, and underscores. For example, $popeye, $one_day, and $INCOME are all valid PHP variable names, while $123 and $48hrs are invalid variable names.

Q: Where can we learn php in hyderabad. What is the name of institute?

18

Web Trainings Institute

$answer"; ?>

Assigning and Using Variable Values To assign a value to a variable, use the assignment operator, the equality (=) symbol. This operator assigns a value (the right side of the equation) to a variable (the left side). The value being assigned need not always be fixed; it could also be another variable, an expression, or even an expression involving other variables, as here: To use a variable value in your script, simply call the variable by name, and PHP will substitute its value at run time. For example:

Saving Form Input in Variables Forms have always been one of the quickest and easiest ways to add interactivity to your web site. A form enables you to ask customers if they like your products and casual visitors for comments. PHP can simplify the task of processing webbased forms substantially, by providing a simple mechanism to read user data submitted through a form into PHP variables. Consider the following sample form: 19

Web Trainings Institute

Enter your message:

The most critical line in this entire page is the

tag: ...

As you probably already know, the method attribute of the

tag specifies the manner in which form data will be submitted (POST), while the action attribute specifies the name of the server-side script (message.php) that will process the information entered into the form. Here is what message.php looks like: $input"; ?> To see how this works, enter some data into the form (“boo”) and submit it. The form processor should read it and display it back to you (“you said: boo”). Thus, whenever a form is POST-ed to a PHP script, all variable-value pairs within that form automatically become available for use within the script through a special PHP container variable, $_POST. To then access the value of the form variable, use its name inside the $_POST container, as in the previous script. If the form uses GET instead of POST, simply retrieve values from $_GET instead of $_POST.

In PHP, a variable does not need to be declared before adding a value to it.You do not have to tell PHP which data type the variable is.PHP automatically converts the variable to the correct data type, depending on its value. In a strongly typed programming language, you have to declare (define) the type and name of the variable before using it. In PHP, the variable is declared automatically when you use it.

20

Web Trainings Institute

Datatypes Boolean : The simplest variable type in PHP, a Boolean variable simply specifies a true or false value. Example : $auth = true; Integer : An integer is a plain-vanilla number like 75, -95, 2000, or 1. Example : $age = 99; Floating-point : A floating-point number is typically a fractional number such as 12.5 or 3.149391239129. Floating point numbers may be specified using either decimal or scientific notation. $temperature = 56.89; String : A string is a sequence of characters, like 'hello' or 'abracadabra'. String values may be enclosed in either double quotes ("") or single quotes ('')

Detecting the Data Type of a Variable To find out what type a particular variable is, PHP offers the gettype() function, which accepts a variable or value as argument. The following example illustrates this: 21

Web Trainings Institute

String values enclosed in double quotes are automatically parsed for variable names; if variable names are found, they are automatically replaced with the appropriate variable value. See the following code snippet for an example: Note that if your string contains quotes, carriage returns, or backslashes, it’s necessary to escape these special characters with a backslash. The following example illustrates this: NULL Values PHP 4.x introduced a new data type for empty variables, called NULL. The NULL data type is “special”: it means that a variable has no value. A NULL is typically seen when a variable is initialized but not yet assigned a value, or when a variable has been de-initialized with the unset() function. To see an example of NULL in action, consider the following script:
Web Trainings Institute

// returns NULL echo gettype($me); ?>

Operators Arithmetic Operators Operator

Description

Example

Result

+

Addition

x=2 x+2

4

-

Subtraction

x=2 5-x

3

*

Multiplication

x=4 x*5

20

/

Division

15/5 5/2

3 2.5

%

Modulus (division remainder)

5%2 10%8 10%2

1 2 0

++

Increment

x=5 x++

x=6

--

Decrement

x=5 x--

x=4

Assignment Operators Operator

Example

Is The Same As

=

x=y

x=y

23

Web Trainings Institute

+=

x+=y

x=x+y

-=

x-=y

x=x-y

*=

x*=y

x=x*y

/=

x/=y

x=x/y

.=

x.=y

x=x.y

%=

x%=y

x=x%y

Comparison Operators Operator

Description

Example

==

is equal to

5==8 returns false

!=

is not equal

5!=8 returns true

<>

is not equal

5<>8 returns true

>

is greater than

5>8 returns false

<

is less than

5<8 returns true

>=

is greater than or equal to

5>=8 returns false

<=

is less than or equal to

5<=8 returns true

Logical Operators Operator

Description

Example

&&

and

x=6 y=3 (x < 10 && y > 1) returns true 24

Web Trainings Institute

||

!

or

x=6 y=3 (x==5 || y==5) returns false x=6 y=3

not

!(x==y) returns true

Examples Using Arithmetic Operators Using String Operators To add strings together, use the string concatenation operator, represented by a period (.). The following example illustrates this: 25

Web Trainings Institute

You can concatenate and assign simultaneously, as in the following: Using Comparison Operators To test whether two variables are different, use any one of PHP’s many comparison operators. The following listing demonstrates most of the important ones: $median); // less-than-or-equal-to operator // returns true if left side is less than or equal to right // returns false here $result = ($median <= $mode); // greater-than-or-equal-to operator // returns true if left side is greater than or equal to right // returns true here $result = ($median >= $mode); // equality operator // returns true if left side is equal to right // returns true here $result = ($mean == $mode); 26

Web Trainings Institute

// not-equal-to operator // returns true if left side is not equal to right // returns false here $result = ($mean != $mode); // inequality operator // returns true if left side is not equal to right // returns false here $result = ($mean <> $mode); ?> The result of a comparison test is always a Boolean value (either true or false). This makes comparison operators a critical part of your toolkit, as you can use them in combination with a conditional statement to send a script down any of its multiple action paths. Using === Operator Using Logical Operators To link together related conditions in a simple and elegant manner, use one of PHP’s four logical operators—logical AND, logical OR, logical XOR, and logical NOT—as illustrated in the following listing:
Web Trainings Institute

// returns true if all conditions are true // returns true here $result = (($user == 'joe') && ($pass == 'trym3')); // logical OR // returns true if any condition is true // returns false here $result = (($status < 1) || ($saveCookie == 0)); // logical NOT // returns true if the condition is false and vice-versa // returns false $result = !($saveCookie == 1); // logical XOR // returns true if any of the two conditions are true // returns false if both conditions are true // returns false here $result = (($status == 1) xor ($saveCookie == 1)); ?> Using the Auto-Increment and Auto-Decrement Operators The auto-increment operator is a PHP operator designed to automatically increment the value of the variable it is attached to by 1. It is represented by a double addition symbol (++). To see it in action, run the following script: Thus, is functionally equivalent to . There’s also a corresponding auto-decrement operator (--), which does exactly the opposite:
Web Trainings Institute

// decrement it $total--; // $total is now 9 ?>

Using the if() Statement In PHP, the simplest form of conditional statement is the if() statement, which looks like this: Here’s an example: = 100) { echo 'Very hot!'; } ?>

Using If-Else Statement

Using Switch
Web Trainings Institute

case 'UK': $capital = 'London'; break; case 'US': $capital = 'Washington'; break; case 'FR': $capital = 'Paris'; break; default: $capital = 'Unknown'; break; } ?>

Nesting Conditional Statements To handle multiple conditions, you can “nest” conditional statements inside each other. For example, this is perfectly valid PHP code:

Using the while() Loop The first—and simplest—loop to learn in PHP is the so-called while() loop. With this loop type, so long as the conditional expression specified evaluates to true, the loop will continue to execute. When the condition becomes false, the loop will be broken and the statements following it will be executed. Here is the syntax of the while() loop:
Web Trainings Institute

while (condition is true) { do this; } ?> Example:

Using the do() Loop A while() loop executes a set of statements while a specified condition is true.If the condition evaluates as false on the first iteration of the loop, the loop will never be executed. In the previous example, if the lower limit is set to a value greater than the upper limit, the loop will not execute even once.However, sometimes you might need to execute a set of statements at least once, regardless of how the conditional expression evaluates. For such situations, PHP offers the do-while() loop. The construction of the do-while() loop issuch that the statements within the loop are executed first, and the condition to be tested is checked after. This implies that the statements within the loop block will be executed at least once. 31

Web Trainings Institute

Using For LOOP Here is what the for() loop looks like: PHP’s for() loop uses a counter that is initialized to a numeric value, and keeps track of the number of times the loop is executed. Before each execution of the loop, a conditional statement is tested. If it evaluates to true, the loop will execute once more and the counter will be incremented by 1 (or more) positions. If it evaluates to false, the loop will be broken and the lines following it will be executed instead. Example

Controlling Loop Iteration with break and continue The break keyword is used to exit a loop when it encounters an unexpected situation. A good example of this is the dreaded “division by zero” error—when dividing one number by another one (which keeps decreasing), it is advisable to check the divisor and use the break statement to exit the loop as soon as it becomes equal to zero. Here’s an example:

32

Web Trainings Institute

The continue keyword is used to skip a particular iteration of the loop and move to the next iteration immediately. This statement can be used to make the execution of the code within the loop block dependent on particular circumstances. The following example demonstrates by printing a list of only those numbers between 10 and 100 that are divisible by 12:

Array An array is a complex variable that enables you to store multiple values in a single variable; it comes in handy when you need to store and represent related information. An array variable can best be thought of as a “container” variable, which can contain one or more values. Here is an example: Creating an Array To define an array variable, name it using standard PHP variable naming rules and populate it with elements using the array() function, as illustrated in the following:
Web Trainings Institute

?> An alternative way to define an array is by specifying values for each element using index notation, like this:

In PHP, there are three kind of arrays:   

Numeric array - An array with a numeric index Associative array - An array where each ID key is associated with a value Multidimensional array - An array containing one or more arrays

Numeric Arrays A numeric array stores each array element with a numeric index.There are two methods to create a numeric array. 1. In the following example the index are automatically assigned (the index starts at 0): $cars=array("Saab","Volvo","BMW","Toyota"); 2. In the following example we assign the index manually: $cars[0]="Saab"; $cars[1]="Volvo"; $cars[2]="BMW"; $cars[3]="Toyota";

34

Web Trainings Institute

Example In the following example you access the variable values by referring to the array name and index: The code above will output: Saab and Volvo are Swedish cars.

Associative Arrays An associative array, each ID key is associated with a value.When storing data about specific named values, a numerical array is not always the best way to do it.With associative arrays we can use the values as keys and assign values to them. Example 1 In this example we use an array to assign ages to the different persons: $ages = array("Peter"=>32, "Quagmire"=>30, "Joe"=>34); Example 2 This example is the same as example 1, but shows a different way of creating the array: $ages['Peter'] = "32"; $ages['Quagmire'] = "30"; $ages['Joe'] = "34"; The ID keys can be used in a script:
Web Trainings Institute

$ages['Peter'] = "32"; $ages['Quagmire'] = "30"; $ages['Joe'] = "34"; echo "Peter is " . $ages['Peter'] . " years old."; ?> The code above will output: Peter is 32 years old. Multidimensional Arrays In a multidimensional array, each element in the main array can also be an array. And each element in the sub-array can be an array, and so on. Example In this example we create a multidimensional array, with automatically assigned ID keys: $families = array ( "Griffin"=>array ( "Peter", "Lois", "Megan" ), "Quagmire"=>array ( "Glenn" ), "Brown"=>array ( "Cleveland", "Loretta", 36

Web Trainings Institute

"Junior" ) ); The array above would look like this if written to the output: Array ( [Griffin] => Array ( [0] => Peter [1] => Lois [2] => Megan ) [Quagmire] => Array ( [0] => Glenn ) [Brown] => Array ( [0] => Cleveland [1] => Loretta [2] => Junior ) ) Example 2 Lets try displaying a single value from the array above: echo "Is " . $families['Griffin'][2] . " a part of the Griffin family?"; The code above will output:

37

Web Trainings Institute

Is Megan a part of the Griffin family? PHP Built-in Functions PHP Functions In this chapter we will show you how to create your own functions.To keep the browser from executing a script when the page loads, you can put your script into a function. The real power of PHP comes from its functions.In PHP, there are more than 700 built-in functions. A function will be executed by a call to the function. You may call a function from anywhere within a page. Create a PHP Function A function will be executed by a call to the function. Syntax function functionName() { code to be executed; } PHP function guidelines:  

Give the function a name that reflects what the function does The function name can start with a letter or underscore (not a number)

Example A simple function that writes my name when it is called:
Web Trainings Institute

echo "Kai Jim Refsnes"; } echo "My name is "; writeName(); ?> Output: My name is Kai Jim Refsnes PHP Functions - Adding parameters To add more functionality to a function, we can add parameters. A parameter is just like a variable. Parameters are specified after the function name, inside the parentheses. Example 1 The following example will write different first names, but equal last name: "; } echo "My name is "; writeName("Kai Jim"); echo "My sister's name is "; writeName("Hege"); echo "My brother's name is "; writeName("Stale"); 39

Web Trainings Institute

?> Output: My name is Kai Jim Refsnes. My sister's name is Hege Refsnes. My brother's name is Stale Refsnes. Example 2 The following function has two parameters: "; } echo "My name is "; writeName("Kai Jim","."); echo "My sister's name is "; writeName("Hege","!"); echo "My brother's name is "; writeName("Ståle","?"); ?>

40

Web Trainings Institute

Output: My name is Kai Jim Refsnes. My sister's name is Hege Refsnes! My brother's name is Ståle Refsnes? PHP Functions - Return values To let a function return a value, use the return statement. Example Output: 1 + 16 = 17

41

Web Trainings Institute

Grouping Form Selections with Arrays In addition to their obvious uses, arrays and loops also come in handy when processing forms in PHP. For example, if you have a group of related checkboxes or a multiselect list, you can use an array to capture all the selected form values in a single variable for greater ease in processing. To see how this works, create and run the following script: Select from the items below:
Power steering Rear windshield wiper 6 CD changer Fog lamps Central locking Computer-based navigation

'; // use a foreach() loop to read and display array elements foreach ($_POST['options'] as $o) 42

Web Trainings Institute

{ echo "$o
"; } } else { echo 'Nothing selected'; } } ?> Notice in this script that the name of the control contains the square braces used when defining a PHP array. The result is this: when the form is submitted, PHP will automatically create an array variable to hold the selected items. This array can then be processed with a foreach() loop, and the selected items retrieved from it. Creating User-Defined Functions A function is simply a set of program statements that perform a specific task, and that can be called, or executed, from anywhere in your program. Every programming language comes with its own functions, and typically also enables developers to define their own. For example, if you had a series of numbers, and you wanted to reduce each of them by 20 percent, you could pull out your calculator and do it manually . . . or you could write a simple PHP function called cheatTheTaxman(), send it the numbers one by one, and have it do the heavy lifting for you. Defining and Invoking Functions To understand how custom functions work, examine the following script: 43

Web Trainings Institute

Reading and Writing files in PHP PHP follows almost same way of writing and manipulating files as in standard C. That is why it becomes so easy for a developer with C lanaguage background to create php application that uses files to save and retrieve data. It is a short tutoril that will get you started with writng and manipulating text files. To open a file either for writing, manipulating (adding new data, or appending new data at end of file), we use single function called fopen, but the second argument of the function changes accordingly. For example: $F = fopen("myfile.txt","w");

Here the second argument passed was "w" only, which means write the file, if it does not exists then create it, and if does not exist create new file. Now, if a file with name myfile.txt already exists, it will be rewritten and all of its bytes are lost, if it did not exist a new file with name myfile.txt is created. The second argument (we used "w") is the file opening mode, is dependent upon your requirement. In general disadvantage of using "w" as mode is if your ifle already exists, it is rewritten, and you loose all existing bytes. So it is recommended that you use mode "w" only when you need to create a new file or really wish to turn filesize to 0 (zero). Second mode is "r", which is using for reading and writing. Using this filemode you can open a file for reading and writing. Although modes "r" as well as (append "a") allows read and write but they do not affect the file while opening it, so the "w" also allows writing + reading but as mentioned, it rewrites the file. So, to open a file for reading and writing we will write: $F = fopen("myfile.txt","r");

And same if we need to open a file which starts writing data to the end of file (appends data), we will srite:

44

Web Trainings Institute

$F = fopen("myfile.txt","a");

Remember : With both of these modes you can read as well as write data anywhere in the file.

Now question appears, if I open a file with mode "r" now I have ended up reading soem data, how can I force my script to start writing data on the end of file ? Two answers: 1. If you have read entire data, the next writing automatically starts at the end of file. 2. If you read data less then the file size, you must be somewhere within file, and if you start writing data, the script will write data to the last position of data you ended up reading with. To obtain filesize we use a function filesize, to which we only pass name of file alongwith path (if not current directory) and it returns us the size of the file in bytes. e.g. $size = filesize("myfile.txt");

Now, when you have opened a file for reading and you wish to jump to a certain position (in this case end of file), you use a function fseek which lets you jump within file to any possible position. e.g. suppose your file size is 10 bytes, you read very first two characters. Now you wish to jum pto the end of file. //reading done, assume $F is file variable

$size = filesize("myfile.txt");

fseek($F, $size); //now we are at end of file

//start writing here 45

Web Trainings Institute

The same we will do when we need to jump to the start of file as: fseek($F,0); //0 means very first byte position Bytes position in a file have zero based indexes, 0 means 1st character, 9 means 10th character and so on.

Now come to core of reading and writing: Writing is very simple, as I have mentioned in all above talk the writing starts where pointer (position) currently is so the reading too. Example 1: Create new file and write string "My name is Ali" in it $F = fopen("myfile.txt","w");

fwrite($F,My name is Ali);

fclose($f);

In this example you see two new functions fwrite and fclose. fwrite is used to write something in a file at current position, since we created new file so writing starts at very first byte automatically. And fclose is used to stop using a file anymore. This is necessary, because there is always a limit of opened files in operating system, so if your script is used much and leaves every file open without closing, it will results in no more file open able until system restart, either an http server (a web host) and/or local host. So, a new file is created and string "My name is Ali" is written in it (14 bytes written).

Example 2: Read first 7 bytes from file we created in example 1 and print them to browser

46

Web Trainings Institute

$F = fopen("myfile.txt","r");

$data = fread($F, 7);

fclose($f);

print $data;

In this example you encounter a ne function, called fread. This function is used to read specific number of bytes from a file. Its first argument is the file variable and second is number of bytes to read. So we passed 7, because our requirement at this time is to read 7 bytes only. This function returned us the bytes read and we simply displayed them using print function, so it will display "My name" only the first7 bytes we read. Take a look at next example Example 3: Read all data from file we created in example 1 and print it to browser $F = fopen("myfile.txt","r");

$size = filesize("myfile.txt");

$data = fread($F, $size);

fclose($f);

print $data;

In this example we used function filesize, which returned us the exact file size in bytes, we saved that size in a variable $size and then passed it to fread as second argument, so that the fread function reads all the data in file. I think it is simple to understand. It will display "My name is Ali" in the browser. 47

Web Trainings Institute

Modifying data Example 4: Jump to byte # 12 and write string "Goutham" $F = fopen("myfile.txt","r");

fseek($F, 11);

fwrite($F,"Goutham");

fclose($f);

print $data;

We opened file, jumped to byte number 12 using fseek functions. Why used 11 when we need to start modifying from byte 12 ? Because I already mentioned that bytes in a file are accessed using zero based index, 11 means 12, 59 means 60, and 0 means 1st byte. Now run the example 3 and you will see the display in browser is "My name is Goutham", which means we successfully have modified the file. Example 5: Add string ". My age is 30" to the end of file we created in example 1 $F = fopen("myfile.txt","a"); //note "a"

fwrite($F,". My age is 30");

fclose($f);

print $data;

48

Web Trainings Institute

We opened file with append mode, using "a" as second argument to function fopen, which automatically takes position to the end of file, s owe don’t need to use fseek in this regards. Now if you repeat running example 3 you will get the output as "My name is Goutham. My age is 30".

Extra things: 1. Another method to read entire file data in a variable $data = get_file_contents("myfile.txt");

2. Read text file as an array of strings, where each line of text is an ite of array $arr = file("myfile.txt");

3. If you dont have above function in your php distribution, use following ot read entire data $data = implode("",file("myfile.txt"));

4. to delete a file unlink("myfile.txt");

5. read page from internet pass url instead of fileme in ne of first 3 examples.

49

Web Trainings Institute

Reading Data from a File In many many situation, when you create an application, you will need to access a text file. Usually it is where you store setting configurations, some data and probably application information Imagine that you are about to access a file in your server which contains a list of email address called 'data.txt'. [email protected] [email protected] [email protected] [email protected] Before you can read or edit a file, you ought to establish a connection to it. This connection is done by using fopen( ) function. This fopen( ) function takes two argument. First, you have to specify the name of the file. In this case, I'm inheriting value from $file. Second argument is the access mode. The word 'r' represents 'read'. When you open a connection, either it is database connection or file connection, you have to close it after PHP finish it's job. In file connection, it use fclose( ) function to close file handler. And every code that will read those data within 'data.txt' should be located between fopen( ) and fclose( ). In the above example, it still has no code yet! Now we are going to read the file. "; 50

Web Trainings Institute

} fclose($fp); ?> Notice that !feof in the while statement. It tells PHP not to stop reading the file until it reach the end of the page or when it had reach it's length limit defined in fgets( ) function. fgets( ) is a function provided by PHP to get lines from file pointer. It takes two argument. One is the file pointer, in this case, our file pointer is $fp. And the second one is length limit. Thus, when the code above is executed, it will print out the result below. [email protected] [email protected] [email protected] [email protected] There is another easy way to read files in PHP. You can use file( ) function to do this. File( ) function will automatically establish a connection to file, read the content and close it when it has finished. But the result is stored in an array. Still taking the above example, we are going to print out the same result using file( ) function. Fast and easy! "; } ?> Writing Data to a File The steps involved in writing data to a file are almost identical to those involved in reading it: open the file and obtain a file handle, use the file handle to write data to it, and close the file. There are two differences:

51

Web Trainings Institute

1. You must fopen() the file in write mode ('w' for write). 2. Instead of using the fread() function to read from the file handle, use the fwrite() function to write to it. The fopen(), fwrite(), and fread() functions are all binary-safe, which means you can use them on binary files without worrying about damage to the file contents. Read more about many of the issues related to binary-safe file manipulation on different platforms at http://www.php.net/manual/en/function.fopen.php. To try this out yourself, create and run the following script: An alternative here is the file_put_contents() function, new in PHP 5.0, which takes a string and writes it to a file in a single line of code. The next example illustrates this:

52

Web Trainings Institute

Testing File Attributes PHP also comes with a bunch of functions that enable you to test the status of a file—for example, find out whether it exists, whether it’s empty, whether it’s readable or writable, and whether it’s a binary or a text file. Table 6-1 has a list of the more interesting functions in this category. Function What It Does file_exists() : Returns a Boolean indicating whether the file exists is_dir() : Returns a Boolean indicating whether the specified path isa directory is_file() Returns a Boolean indicating whether the specified file is a regular file is_link() Returns a Boolean indicating whether the specified file is a symbolic link is_executable() Returns a Boolean indicating whether the specified file is executable is_readable() Returns a Boolean indicating whether the specified file is readable is_writable() Returns a Boolean indicating whether the specified file is writable filesize() Gets file size, in bytes filemtime() Gets last modification time of file fileatime() Gets last access time of file fileowner() Gets file owner filegroup() Gets file group fileperms() Gets file permissions filetype() Gets file type TABLE 6-1 Useful PHP File Functions And here is an example that demonstrates some of these functions:
Web Trainings Institute

// set file $file = $_GET['file']; // check if file exists echo file_exists($file) ? 'File exists' : 'File does not exist'; // check if file is executable echo is_executable($file) ? 'File is executable' : 'File is not executable'; // check if file is readable echo is_readable($file) ? 'File is readable' : 'File is not readable'; // check if file is writable echo is_writable($file) ? 'File is writable' : 'File is not writable'; // print file size echo 'File size is ' . filesize($file) . ' bytes'; // print file owner echo 'File owner is ' . fileowner($file); // print file type echo 'File type is ' . filetype($file); ?> Managing Sessions and Using Session Variables You may have heard that HTTP, the protocol on which the Web runs, is a “stateless” protocol and, therefore, treats each request for a web page as a unique and independent transaction, with no relationship whatsoever to the transactions that preceded it. While this doesn’t present a problem for most web users, it throws a massive wrench in the works of transaction-based sites, which need to track the activities of each user. Consider, for example, the common shopping cart used in web storefronts: in a “stateless” environment, it is impossible to keep track of the items each user has short listed for purchase, as the stateless nature of the HTTP protocol makes it impossible to identify which transactions belong to which client or user.Consequently, what is required is a method that makes it possible to “maintain state,” something that allows client connections to be tracked and connectionspecific data to be maintained. A common solution to the problem is to use sessions to store information about each client and track its activities. This session data is preserved for the duration of the visit, and is usually destroyed on its conclusion. PHP has included built-in session support since PHP 4.0. Client transactions are identified through unique numbers; these identifiers are used to re-create each client’s prior session environment whenever required. The session identifier may be stored on the client in a cookie or it may be passed from page to page in the URL. 54

Web Trainings Institute

Creating a Session and Registering Session Variables In PHP, the session_start() function is used to create a client session andgenerate a session ID. Once a session has been created, it becomes possible to register any number of session variables; these are regular variables which can store textual or numeric information and can be manipulated by standard PHP functions, but are unique to each client. In a PHP script, session variables may be registered as key-value pairs in the special $_SESSION associative array. To see how sessions and session variables work, examine the following script, which creates a new client session and registers two session variables: On subsequent pages, calls to the session_start() function re-create the prior session environment by restoring the values of the $_SESSION associative array. This can be tested by attempting to access the values of the session variablesregistered in the previous example: Destroying a Session To destroy an extant session—for example, on user logout—reset the $_SESSION array, and then use the session_destroy() function to erase session data.
Web Trainings Institute

$_SESSION = array(); // destroy session session_destroy(); ?> Storing Data in Cookies Cookies allow web sites to store client-specific information in a file on the client system, and retrieve this information on an as-needed basis. Cookies are typically used to bypass the stateless nature of the HTTP protocol, by using the client’s disk as a storage area for persistent data; however, they’re dependent on the clientbrowser being configured to accept cookies. PHP has included support for cookie generation and retrieval since PHP 3.x. Using PHP’s built-in functions, you can create client-side cookies, store values in them, and delete them after a specified period has passed. When dealing with cookies, you should be aware of some ground rules: 1. Because cookies are used to record information about your activities on a particular site, they can only be read by the site that created them. 2. A single domain cannot set more than 20 cookies, and each cookie is limited to a maximum size of 4KB. 3. A cookie usually possesses five types of attributes. Table 6-2 lists them. 4. Of all the five attributes, only the first is not optional. Attribute in Cookies Name : Sets the name and value of the cookie Expires : Sets the date and time at which the cookie expires path : Sets the top-level directory on the domain from which cookie data can be accessed domain: Sets the domain for which the cookie is valid secure: Sets a Boolean flag indicating that the cookie should be transmitted only over a secure HTTP connection Setting Cookies In PHP, cookies are set with the setcookie() function, which accepts six arguments: the cookie name, its value, its expiry date (in UNIX timestamp format), its path and domain, and a Boolean flag indicating its security status. Only the first argument is required, all the rest are optional. To better understand this, try out the following example script: 56

Web Trainings Institute

The setcookie() function returns true if successful. By checking for this, you can verify if the cookie was sent to the browser or not. You can set multiple cookies, simply by calling setcookie() once for each cookie. Consider the following example, which sets three cookies for the same domain, each with different expiry dates: Retrieving Cookie Data Once a cookie has been set for a domain, it becomes available in the special $_COOKIE associative array, and its value may be accessed using standard array notation. Here’s an example:
Web Trainings Institute

} else { echo "Is this your first time here? Take our guided tour!"; } ?>

Deleting Cookies To delete a cookie, simply use setcookie() with its name to set the cookie’s expiry date to a value in the past. Dealing with Dates and Times Calculating and formatting date and time values is par for the course when creating web applications. And PHP comes with some fairly powerful functions to create and format time- and date-stamps. The following sections discuss some of the more common tasks in this context. Retrieving the Current Date and Time The most basic thing you can do with PHP’s date and time functions is retrieve the current date and time. And the easiest way to do this is with the getdate() function, which returns an associative array containing the current date and time. To try it out, create and run the following script:
Web Trainings Institute

echo "It is now $current_time on $current_date"; ?> As should be clear from the script above, the getdate() function returns an associative array containing keys for the current hour, minute, second, day, date,month, and year. These values can be accessed using standard array notation. Obtaining Timestamps for Arbitrary Dates and Times Most of PHP’s date functions work on the basis of timestamps. This timestamp isa unique numeric representation of a particular date, calculated as the number of seconds between January 1, 1970 and the date and time specified. In PHP, UNIX timestamps are created via the mktime() function, which accepts a series of dateand time parameters and converts them into a timestamp. Here’s an example:

To obtain a timestamp for an arbitrary date or time, pass the mktime()function six parameters: the hour, minute, second, month, day, and year. To obtaina timestamp for the current moment in time, call mktime() without any arguments.

59

Web Trainings Institute

What is RDBMS ? A relational database stores data in separate tables instead of placing all data in one large table. A relational database then allows Data Base Administrator's (DBA's) to define relationships between these tables. These relationships enable DBA's to combine data from several tables for querying and reporting. This is accomplished through the use of keys, which are database fields used to uniquely identify specific records in a table. Relational database technology allows databases to be larger, faster, and more efficient. The concept of a relational database was first developed by Dr. Edger F. (Ted) Codd in A Relational Model of Data for Large Shared Data Banks in 1970. Dr. Codd defined thirteen standards which must be met before a database can be considered to be a relational database: 0. A relational DBMS must be able to manage databases entirely through its relational capabilities. 1. Information rule-- All information in a relational database (including table and column names) is represented explicitly as values in tables. 2. Guaranteed access--Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name. 3. Systematic null value support--The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain. 4. Active, online relational catalog--The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language. 5. Comprehensive data sublanguage--At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions. 6. View updating rule--All views that are theoretically updatable can be updated through the system.

60

Web Trainings Institute

7. Set-level insertion, update, and deletion -- The DBMS supports not only setlevel retrievals but also set-level inserts, updates, and deletes. 8. Physical data independence--Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered. 9. Logical data independence--Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures. 10. Integrity independence--The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed. 11. Distribution independence--Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed. 12. Nonsubversion--It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages. Alternatives to the relational database model include the heirarchical model, the network model, and the object model. Tables, Records, and Fields Every database is composed of one or more tables. These tables, which structure data into rows and columns, are what lend organization to the data. Here’s an example of what a typical table looks like: +-----+---------------------+-------+ | mid | mtitle | myear | +-----+---------------------+-------+ | 1 | Rear Window | 1954 | | 2 | To Catch A Thief | 1955 | | 3 | The Maltese Falcon | 1941 | | 4 | The Birds | 1963 | | 5 | North By Northwest | 1959 | | 6 | Casablanca | 1942 | | 7 | Anatomy Of A Murder | 1959 | +-----+---------------------+-------+ As you can see, a table divides data into rows, with a new entry (or record) onevery row. The data in each row is further broken down into columns (or fields), each of which contains a value for a particular 61

Web Trainings Institute

attribute of that data. For example, if you consider the record for the movie Rear Window, you’ll see that the recordis clearly divided into separate fields

Primary, Foreign Keys and Normalization The relationships in a RDBMS ensure that there is no redundant data. What is redundant data, you might ask? I’ll answer you with example. An online store, offers computers for sale and the easiest way to track the sales will be to keep them in a database. You can have a table called Product, which will hold information about each computer - model name, price and the manufacturer. You also need to keep some details about the manufacturer like their website and their support email. If you store the manufacturer details in the Product table, you will have the manufacturer contact info repeated for each computer model the manufacturer produces: model Price Manufacturer ManufacturerWebsite ManufacturerEmail Inspiron B120 $499 Dell http://www.dell.com [email protected] Inspiron B130 $599 Dell http://www.dell.com [email protected] Inspiron E1705 $949 Dell http://www.dell.com [email protected] Satellite A100 $549 Toshiba http://www.toshiba.com [email protected] Satellite P100 $934 Toshiba http://www.toshiba.com [email protected] To get rid of the redundant manufacturer data in the Product table, we can create a new table called Manufacturer, which will have only one entry (row) for each manufacturer and we can link (relate) this table to the Product table. To create this relation we need to add additional column in the Product table that references the entries in the Manufacturer table. A relationship between 2 tables is established when the data in one of the columns in the first table matches the data in a column in the second table. To explain this further we have to understand SQL relational concepts – Primary Key and Foreign Key. Primary Key is a column or a combination of columns that uniquely identifies each row in a table. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. In the most common scenario the relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table. Consider the new Product and Manufacturer tables below: Manufacturer ManufacturerID Manufacturer ManufacturerWebsite 1 Dell http://www.dell.com 62

ManufacturerEmail [email protected]

Web Trainings Institute

2

Toshiba

http://www.toshiba.com [email protected]

Product model Price ManufacturerID Inspiron B120 $499 1 Inspiron B130 $599 1 Inspiron E1705 $949 1 Satellite A100 $549 2 Satellite P100 $934 2 The first table is Manufacturer which has 2 entries for Dell and Toshiba respectively. Each of these entries has a ManufacturerID value, which is unique integer number. Because the ManufacturerID column is unique for the Manufacturer table we can use it as a Primary Key in this table. The Product table retains the Model and the Price columns, but has a new column called ManufacturerID, which matches the values of the ManufacturerID column in the Manufacturer table. All values in the ManufacturerID column in the Product table have to match one of the values in the Manufacturer table Primary Key (for example you can’t have ManufacturerID with value of 3 in the Product table, simply because there is no manufacturer with this ManufacturerID defined in the Manufacturer table). I’m sure you’ve noticed that we used the same name for the Primary Key in the first table as for the Foreign Key in the second. This was done on purpose to show the relationship between the 2 tables based on these columns. Of course you can call the 2 columns with different names, but if somebody sees your database for a first time it won’t be immediately clear that these 2 tables are related. But how do we ensure that the Product table doesn’t have invalid entries like the last entry below: model Price ManufacturerID Inspiron B120 $499 1 Inspiron B130 $599 1 Inspiron E1705 $949 1 Satellite A100 $549 2 Satellite P100 $934 2 ThinkPad Z60t $849 3 We do not have a manufacturer with ManufacturerID of 3 in our Manufacturer table, hence this entry in the Product table is invalid. The answer is that you have to enforce referential integrity between the 2 tables. Different RDBMS have different ways to enforce referential integrity, and I will not go into more details as this is not important to understand the concept of relationship between tables. 63

Web Trainings Institute

There are 3 types of relations between tables – One-To-Many, Many-To-Many and One-To-One. The relation we created above is One-To-Many and is the most common of the 3 types. In One-To-Many relation a row in one of the tables can have many matching rows in the second table, but a row the second table can match only one row in the first table. In our example, each manufacturer (a row in the Manufacturer table) produces several different computer models (several rows in the Product table), but each particular product (a row in the Product table) has only one manufacturer (a row in the Manufacturer table). The second type is the Many-To-Many relation. In this relation many rows from the first table can match many rows in the second and the other way around. To define this type of relation you need a third table whose primary key is composed of the 2 foreign keys from the other 2 table. To clarify this relation lets review the following example. We have a Article table (ArticleID is primary key) and Category (CategoryID is primary key) table. Every article published in the Article table can belong to multiple categories. To accommodate that, we create a new table called ArticleCategory, which has only 2 columns – ArticleID and CategoryID (these 2 columns form the primary key for this table). This new table called sometimes junction table defines the Many-To-Many relationship between the 2 main tables. One article can belong to multiple categories, and every category may contain more than one article. In the One-To-One relation each row in the first table may match only one row in the second and the other way around. This relationship is very uncommon simply because if you have this type of relation you may as well keep all the info in one single table. By dividing the data into 2 tables we successfully removed the redundant manufacturer details from the initial Product table adding an integer column referencing the new Manufacturer table instead. The process of removing redundant data by creating relations between tables is known as Normalization. Normalization process uses formal methods to design the database in interrelated tables. Understanding SQL and SQL Queries Putting data into a database is only half the battle—the other half involves using it effectively. This section tells you a little bit about SQL, which is the primary means of communicating with a database and extracting the data you require. SQL began life as SEQUEL, the Structured English Query Language; the namewas later changed to SQL for legal reasons. SEQUEL was a part of System/R, a prototype of the first relational database system created by IBM in 1974. In the late1970s, SQL was selected as the query language for the Oracle 64

Web Trainings Institute

RDBMS. This put iton the map and, by the 1980s, SQL was used in almost all commercial RDBMS. In1989, SQL became an ANSI standard. The latest version of this standard, referred to as SQL92 or SQL2, is currently used on most of today’s commercial RDBMSs(including MySQL).

65

Web Trainings Institute

SQL statements resemble spoken English and can broadly be classified into three categories: ■ Data Definition Language (DDL) DDL consists of statements that define the structure and relationships of a database and its tables. Typically,these statements are used to create, delete, and modify databases and tables; specify field names and types; set indexes; and establish relationships between tables. ■ Data Manipulation Language (DML) DML statements are related to altering and extracting data from a database. These statements are used to add records to, and delete records from, a database; perform queries;retrieve table records matching one or more user-specified criteria; and join tables together using their common fields. ■ Data Control Language (DCL) DCL statements are used to define access levels and security privileges for a database. You would use these statements to grant or deny user privileges, assign roles, change passwords, view permissions, and create rulesets to protect access to data. Here are a few examples of valid SQL statements: CREATE DATABASE addressbook; DESCRIBE catalog; SELECT title FROM books WHERE targetAge > 3; DELETE FROM houses WHERE area < 100; As the previous examples demonstrate, SQL syntax is close to spoken English,which is why most novice programmers find it easy to learn and use. Every SQL statement begins with an “action word” and ends with a semicolon. White space,tabs, and carriage returns are ignored. This makes the following two commands equivalent: DELETE FROM houses WHERE monthlyRent > 25000; DELETE FROM houses WHERE monthlyRent > 25000;

Understanding Database Normalization An important part of designing a database is a process known as normalization.Normalization refers to the activity of streamlining a database design by eliminating redundancies and repeated values. Most often, redundancies are eliminated by placing repeating groups of values into separate tables and linking 66

Web Trainings Institute

them through foreign keys. This not only makes the database more compact and reduces the disk space it occupies, but it also simplifies the task of making changes. In nonnormalized databases, because values are usually repeated in different tables, altering them is a manual (and error-prone) find-and-replace process. In a normalized database, because values appear only once, making changes is a simple onestep UPDATE. The normalization process also includes validating the database relationships to ensure that there aren’t any crossed wires and to eliminate incorrect dependencies. This is a worthy goal, because when you create convoluted table relationships,you add greater complexity to your database design … and greater complexity translates into slower query time as the optimizer tries to figure out how best to handle your table joins. A number of so-called normal forms are defined to help you correctly normalize a database. A normal form is simply a set of rules that a database must conform to. Five such normal forms exist, ranging from the completely nonnormalized database to the fully normalized one. Using the MySQL Command-Line Client To begin, ensure that your MySQL server is running, and then connect to it with the mysql command-line client. Remember to send a valid password with your username, or else MySQL will reject your connection attempt. (Throughout this section and the ones that follow, boldface type is used to indicate commands that you should enter at the prompt.) [user@host]# mysql -u root -p Password: ****** If all went well, you’ll see a prompt like this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 134 to server version: 4.0.12 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> The mysql> you see is an interactive prompt, where you enter SQL statements. Statements entered here are transmitted to the MySQL server using a proprietary client-server protocol, and the results are transmitted back using the same manner. Try this out by sending the server a simple statement: mysql> SELECT 5+5; +-----+ | 5+5 | +-----+ 67

Web Trainings Institute

| 10 | +-----+ 1 row in set (0.06 sec) Here, the SELECT statement is used to perform an arithmetic operation on the server and return the results to the client (you can do a lot more with the SELECT statement, and it’s all covered in Chapter 10). Statements entered at the prompt must be terminated with either a semicolon or a \g signal, followed by a carriage return to send the statement to the server. Statements can be entered in either uppercase or lowercase type. The response returned by the server is displayed in tabular form, as rows and columns. The number of rows returned, as well as the time taken to execute the command, are also printed. If you’re dealing with extremely large databases, this information can come in handy to analyze the speed of your queries. As noted previously, white space, tabs, and carriage returns in SQL statements are ignored. In the MySQL command-line client, typing a carriage return without ending the statement correctly simply causes the client to jump to a new line and wait for further input. The continuation character -> is displayed in such situations to indicate that the statement is not yet complete. This is illustrated in the next example, which splits a single statement over three lines: mysql> SELECT 100 -> * -> 9 + (7*2); +-----------------+ | 100 * 9 + (7*2) | +-----------------+ | 914 | +-----------------+ 1 row in set (0.00 sec) Notice that the SQL statement in the previous example is only transmitted to the server once the terminating semicolon is entered. Most of the time, you’ll be using SQL to retrieve records from one or more MySQL tables. Consider, for example, the following simple SQL query, which counts all the records in the user table from the mysql database (this database comes preinstalled with MySQL): mysql> SELECT COUNT(*) FROM mysql.user; +----------+ | COUNT(*) | 68

Web Trainings Institute

+----------+ |4| +----------+ 1 row in set (0.11 sec) To obtain help on using the MySQL client, type help at the mysql> prompt. mysql> help List of all MySQL commands: (Commands must appear first on line and end with ';') help (\h) Display this help. ? (\?) Synonym for `help'. clear (\c) Clear command. connect (\r) Reconnect to the server. Optional arguments are db and host. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. ... To close the connection to the server and exit the client, type quit at the mysql> prompt. mysql> quit Bye

69

Web Trainings Institute

Working with Databases and Tables Creating Databases Because all tables are stored in a database, the first command you need to know is the CREATE DATABASE command, which initializes an empty database. Try itout by creating a database called db2: mysql> CREATE DATABASE db2; Query OK, 1 row affected (0.05 sec) Databases in MySQL are represented as directories on the disk, and tables are represented as files within those directories. Therefore, database names must comply with the operating system’s (OS) restrictions on which characters are permissible within directory names. Database names cannot exceed 64 characters and names that contain special characters or consist entirely of digits or reserved words must be quoted with the backtick (`) operator. You can select a particular database for use with the USE command. Select the db2 database you just created to try this: mysql> USE db2; Database changed Once you select a database with the USE command, it becomes the default database for all operations.

Creating Tables Because this is a new database, no tables are in it yet. To create a table, use the CREATE TABLE command, as in the following: mysql> CREATE TABLE movies ( -> mid int(10) UNSIGNED NOT NULL AUTO_INCREMENT, -> mtitle varchar(255) NOT NULL default '', -> myear year(4) NOT NULL default '0000', -> PRIMARY KEY (mid) -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.10 sec) The CREATE TABLE statement begins with the table name, followed by a set of parentheses. These parentheses enclose one or more field definitions, separated by commas. Each field definition contains 70

Web Trainings Institute

the field name, its data type, and any special modifiers or constraints that apply. Following the closing parenthesis is an optional table type specifier, which tells MySQL which storage engine to use for this table. Table and field names must conform to the same rules that apply to database names. MySQL tables are stored as files within the database directory and, as such, are subject to the host operating system’s rules on file names.

Once you log in, a phpMyAdmin screen appears as shown below.

Creating a table in your database The left-hand frame in phpMyAdmin is used for navigation.You will see your database displayed here (in this case called mydomain). As you create tables, they will show below this. Click on your database the navigation frame and a new window will appear on the right hand side.

71

Web Trainings Institute

We will create a table in the database, called "people". Use the Create new table feature. Type in the name of the new table into the Name: people, and the number of columns in the table (4) into Fields:. This tutorial is only designed to show you the basic php/MySQL/phpMyAdmin functions. You can delete it using the Drop function. You will want to allow for growth in your table.

Click Go and you should see something like this. The table title now appears with under the database name.

72

Web Trainings Institute

Now enter the names and attributes of our table fields. Enter the following information as above: Field

Type Length Default Extra

id

int

6

name

char

100

telephone char

50

birthday

50

char

0

auto_increment

The Length value indicates the maximum allowable length of characters for input. There are many different values that can be set for Type; see further documentation here. The Types specified in this example aren't the most efficient, but just used for the purposes of this exercise. The "id" field, which will be used as a Primary key for this table, has been set to auto_increment, saving you from having to having to type in the next number in sequence when you input records. Set the Default to 0

73

Web Trainings Institute

Once you've entered all the values, click Save. A screen like this will appear.

Congratulations!-You have created your table! The corresponding SQL command for creating these fields is also displayed. This isn't needed but in time you will start to recognise MySql commands Note that you can use Drop to delete a table or fields. When you are ready we suggest you check out all of the options on this page. Inputting data into the table. Click the tab labeled "Insert" - and another window should appear, like this. 74

Web Trainings Institute

Now type in the details for each of the fields for this record. The "id" column was set to automatically increment so you do not need to enter a number. Note - if you ever get lost with phpMyAdmin navigation click "Home" in the left hand nav bar and start again. Now click Save and the record is saved to the people table. The previous window reappears with the SQL command for the insert. You can keep adding recordsby re-selecting Insert". For multiple records, you can select the "Insert another new row" radio button on the input form. When you've finished entering several records into the table, you can check them by clicking on the Browse tab. You can click on individual records for editing or deleting.

75

Web Trainings Institute

You can use the Select tab to refine your display when your database starts grows to many pages of records. Backup your data You "don't know what you've got 'til its gone"! - Click on your database name in the left hand navigation bar - Click on EXPORT (top tab) - Highlight the table/s you want to back up - Select STRUCTURE and DATA radio button 76

Web Trainings Institute

- Select "Enclose table and field names with backquotes" - Select "Save as file" and "zipped" check boxes - Click "Go" and a zipped archive file will be generated. Well done! - you've created a database, a table and fields, entered in a few records, viewed the records, edited and perhaps deleted some of them and practised backing up. Creating Sample Tables We will use a geographical information system as an example. We decide that we need information about cities and countries, so we design two tables, which will be part of a database called geodb (although any database name would do). To create the tables, we can use phpMyAdmin's Structure subpage in Database view, or we can use the SQL query box to enter the appropriate statement:

The table creation is accomplished with the CREATE TABLE statement, in which we give the new table's name. The statement begins with CREATE TABLE, followed by the table name. Then, enclosed in brackets, we put the list of columns, and information about the keys. Each column is assigned a name, data type, the NULL or NOT NULL attribute (here, NOT NULL means the column cannot have a NULL value) and a default value, if appropriate. CREATE TABLE cities ( id int(11) NOT NULL auto_increment, city_name varchar(50) NOT NULL default '', 77

Web Trainings Institute

latitude varchar(15) NOT NULL default '', longitude varchar(15) NOT NULL default '', population int(11) NOT NULL default '0', country_code char(2) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM AUTO_INCREMENT=1 ; The id column is our primary key, a column which uniquely identifies each city. Its data type is INT (an integer number), and MySQL will assign unique numbers to it, thanks to the auto_increment attribute. Note that we cannot use the city name as a primary key, as some city names are not unique in the world. We also use an integer for the population data. The other columns use character (CHAR) or variable character (VARCHAR) data types. When we know the exact length of data, it's better to use CHAR, specifying the length of the column as in CHAR(2). Otherwise we use a variable character data type, which will take only the space needed by each piece of data, and we specify the maximum length, as in VARCHAR(15). After the columns list, we have some table-specific information, like its type, and the first value for the autoincrement column. SQL statements end with a semi-colon. Having created our cities table, we do the same operation, this time for the countries table.

CREATE TABLE countries ( country_code char(2) NOT NULL default '', country_name varchar(100) NOT NULL default '' ) TYPE=MyISAM; We notice that the country_code column is present in both tables. This shows the relational principle: the country_code in cities refers to the same column in countries. This way, we save on space, having each country name only once in our database.

Another article (Migrating to InnoDB) will explain in greater details this relational technique. 78

Web Trainings Institute

We are now ready to enter some data in our tables.

Data modification In this section, we will learn the basic syntax for the INSERT, UPDATE, DELETE, and SELECT statements.

Adding Data with INSERT Let's first examine the INSERT statement, by looking at the code phpMyAdmin generates when we do an Insert operation. We bring up the Insert sub-page, in Table view for the countries table, and we enter data about a country:

When we click Go, the data is inserted and phpMyAdmin shows us the INSERT statement used:

INSERT INTO `countries` ( `country_code` , `country_name` ) VALUES ('ca', 'Canada'); After the INSERT INTO part, we have the table name. In MySQL, we can enclose table names and column names within backticks, in case there are special characters in them, like reserved words or accented characters. Then we open a first set of brackets, listing the columns in which we want to insert, separated by commas. The reserved word VALUES follows, then the last set of brackets enclosing the values, in the same order as the columns list. If the values have a character data type, we have to enclose them within quotes. We can now insert a city.

INSERT INTO `cities` ( `id` , `city_name` , `latitude` , `longitude` , `population` , `country_code` ) VALUES ('', 'Sherbrooke', '45 23 59.00', '-71 46 11.00', 125000, 'ca'); 79

Web Trainings Institute

Here, we put an empty value for id, because this column's auto-increment attribute will provide a value. We also see that the population value, being numeric, does not need to be surrounded by quotes. Let's end this section by inserting some data for another country and city, which we will need later.

INSERT INTO `countries` ( `country_code` , `country_name` ) VALUES ('zh', 'China'); INSERT INTO `cities` ( `id` , `city_name` , `latitude` , `longitude` , `population` , `country_code` ) VALUES ('', 'Shanghai', '31 13 58.00', '121 26 59.99', 11000000, 'zh');

Updating Data with UPDATE We first click on Browse for table cities, displayed our single row of data.

By clicking on the small pencil-shaped icon (or Edit link), we go to the Edit panel for this row. We decide to change the population value to 130000. After a click on Save, phpMyAdmin shows the following statement: UPDATE `cities` SET `population` = '130000' WHERE `id` = '1' LIMIT 1 ; Here we have the UPDATE keyword, followed by the table name. The SET keyword introduces the list of modifications (here only the population), which follows the format column = new value. We now see the condition WHERE `id` = '1', which uses the primary key information to limit the change to only this row, i.e. only this city. The limit 1 part is a safeguard added by phpMyAdmin, in case there would be no primary key defined, to avoid doing the change to more than one row. More than one column can be changed in a single UPDATE operation:

80

Web Trainings Institute

UPDATE `cities` SET `city_name` = 'Sherbrooke, Qu・ bec', `population` = '130001' WHERE `id` = '1' LIMIT 1 ; Deleting Data with DELETE In Browse mode on table cities, clicking on the small red trash-can icon (or Delete link) brings up a dialog to confirm the execution of the following statement: DELETE FROM `cities` WHERE `id` = '1' LIMIT 1 ; The syntax is simple, involving just the table name, and the condition to apply for the delete operation.

Omitting the WHERE condition in an UPDATE or DELETE operation is perfectly legal in SQL, but then the operation takes place on every rows of the table! Retrieving Data with SELECT Retrieving information from our tables is probably the operation we do most of the times. This is the way to get answers to questions like ・ what are the cities with a population over a certain number?・ . In fact, we previously did a SELECT when we clicked on the Browse link for table cities. This generated a simple form of the SELECT statement: SELECT * FROM `cities` LIMIT 0,30; Here, the asterisk means ・ all the columns・ . We add FROM and the name of the table which we want to query. The LIMIT 0,30 means to start at row number 0 (the first one), and select a maximum of 30 rows. Let's try a Search to see more options for the SELECT. We go to the Search sub-page for table cities, and we choose only some columns we need:

81

Web Trainings Institute

Then at the bottom of the page, we choose to display by the result by population in descending order:

Executing the search generates the following query:

SELECT `city_name` , `population` FROM `cities` WHERE 1 ORDER BY `population` DESC LIMIT 0,30 We see that the asterisk has been replaced by a comma-separated list of columns. A condition WHERE 1 has been added by phpMyAdmin, this is a condition which is always true and selects all rows. We will see in a moment that we can replace it with some other condition. Also, the clause ORDER BY appears, followed by the column on which we want to sort results, and the keyword DESC for descending order (we could also use ASC for ascending). 82

Web Trainings Institute

Conditions To easily add a condition, on the results page we can click on SQL-query: Edit, which brings the Query window popup. We add a condition on the country:

SELECT `city_name` , `population` FROM `cities` WHERE country_code = 'zh' ORDER BY `population` DESC which displays all cities located in China (ok, we were a bit lazy with data entry, but you get the picture). Conditions can be expressed using a rich array of operators and functions. Here are two examples: Finding the Canadian cities with a population over 100000:

WHERE population > 100000 AND country_code = 'ca' Finding the cities whose name starts with ・ A・ :

WHERE city_name like 'A%'

Aggregate functions Summary information may be generated by grouping on a specific column. Here we ask the average city population per country:

SELECT country_code, AVG(population) FROM cities GROUP BY country_code Other possible aggregate functions are MIN(), MAX(), SUM() and COUNT(), which compute respectively the minimum value, maximum value, sum of values, and number of values. For example, we could get the number of cities per country with: 83

Web Trainings Institute

SELECT country_code, count(city_name) FROM cities GROUP BY country_code

Joins Normally, a relational database involves many tables, linked on common keys. We may need at times to run queries on more than one table. Linking, or joining, tables can be done using different techniques; we will focus on a simple method involving key comparison. In the following query, the FROM clause contains a comma-separated list of tables. In the columns list, we use the table name and a dot as a prefix before each column name (not strictly necessary if each column name is only present in one table). SELECT cities.city_name, cities.population, countries.country_name FROM cities, countries WHERE cities.country_code = countries.country_code LIMIT 0,30

Specifying Field Data Types Once you have identified the tables and the fields for your database, the next step is to determine each fields data type. With any relational database management system, you need to define what kind of information each field will contain. In most relational database management systems, there are three primary categories of field types: 84

Web Trainings Institute

1. Text 2. Numbers 3. Dates and Times Within each of these primary categories, there are variations of these categories, some of which may be specific to individual RDMSs. I will highlight particular differences as they arise. It is important to give careful thought and consideration to field types because they dictate what information can be stored and how it is stored which may affect database performance. MySQL Data Types Below is a list of data types for MySQL (adapted from Ullman L. MySQL. A visual quickstart guide. Peachpit Press: Berkeley. 2003.): Note: The square brackets [] indicate an optional parameter to be put in parentheses, while parentheses () indicate required arguments. Type CHAR[Length]

Size Length bytes

VARCHAR(Length)

String length + 1 bytes

TINYTEXT

String length + 1 bytes

TEXT

String length + 2 bytes

MEDIUMTEXT

String length + 3 bytes

LONGTEXT

String length + 4 bytes

TINYINT[Length]

1 byte

SMALLINT[Length]

2 bytes

MEDIUMINT[Length]

3 bytes

INT[Length]

4 bytes

Description A fixed-length field from 0 to 255 characters long. A fixed-length field from 0 to 255 characters long. A string with a maximum length of 255 characters A string with a maximum length of 65,535 characters. A string with a maximum length of 16,777,215 characters. A string with a maximum length of 4,294,967,295 characters. Range of -128 to 127 or 0 to 255 unsigned. Range of -32,768 to 32,767 or 0 to 65,535 unsigned. Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned Range of -2,147,483,648 to

85

Web Trainings Institute

BIGINT[Length]

8 bytes

FLOAT

4 bytes

DOUBLE[Length, Decimals]

8 bytes

DECIMAL[Length, Decimals] DATE DATETIME

Length +1 bytes or Length + 2 bytes 3 bytes 8 bytes

TIMESTAMP

4 bytes

TIME ENUM

3 bytes 1 or 2 bytes

SET

1, 2, 3, 4, or 8 bytes

86

2,147,483,647 or 0 to 4,294,967,295 unsigned Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned A small number with a floating decimal point. A large number with a floating decimal point. A DOUBLE stored as a string, allowing for a fixed decimal point. In the format YYYY-MM-DD In the format YYYY-MM-DD HH:MM:SS. In the format YYYYMMDDHHMMSS; acceptable range ends in the year 2037. In the format of HH:MM:SS. Short for enumeration, that is, each column can have one of several possible values. Like ENUM except that each column can have more than one of several possible values.

Web Trainings Institute

Selecting the Most Appropriate Data Type Choosing the data type best suited to the values you expect to enter into thecorresponding field is extremely important. When making this decision, take into account the following factors: ■ The range and type of values that the field will hold ■ The types of calculations you expect to perform on those values ■ The manner in which the data is to be formatted for display purposes ■ The manner in which the data is to be sorted and compared against other fields ■ The available subtypes for each field and their storage efficiencies By taking all these factors into consideration at the time of designing your database, you reduce the chance of incompatibilities and storage inefficiencies later. Using the wrong data type can affect both the performance of your RDBMS and the types of operations you can perform on that field. For example, using a VARCHAR type on a field that is meant for numeric or date values could result in unexpected behavior when you perform calculations on it, just as using a large TEXT field for small string values could lead to a waste of space and inefficient indexing. Adding Field Modifiers and Keys You can apply a number of additional constraints, or modifiers, to a field to increase the consistency of the data that will be entered into it, and to mark it as “special” in some way. These modifiers can either appear as part of the field definition if they apply only to that specific field (for example, a default value for a field) or after all the field definitions if they relate to multiple fields (for example, a multicolumn primary key). ■ You can specify whether the field is allowed to be empty or if it must necessarily be filled with data by placing the NULL and NOT NULL modifiers after each field definition. ■ You can specify a default value for a field with the DEFAULT modifier. This default value is used if no value is specified for that field when inserting a record. In the absence of a DEFAULT modifier for NOT NULL fields, MySQL automatically inserts a nonthreatening default value into the field. You can have MySQL automatically generate a number for a field (by incrementing the previous value by 1) with the AUTO_INCREMENT modifier. This is particularly useful when you need to generate row numbers for each record in the table. However, the AUTO_INCREMENT modifier can only be applied to numeric fields that are both NOT NULL and belong to the PRIMARY KEY. A table may only contain one AUTO_INCREMENT field.

87

Web Trainings Institute

■ For fields that accept string values, you can specify the character set for these values with the CHARACTER SET modifier (new in MySQL 4.1.1). However, this feature is only supported in MySQL’s MyISAM, MERGE, and InnoDB table types (see the section entitled “Selecting a Table Type” for more on MySQL’s table types). ■ You can index a field with the INDEX modifier. When a field is indexed in this manner, MySQL no longer needs to scan each row of the table for a match when performing queries; instead, it can simply look up the index. This speeds up searches and reduces query response time. Indexing is recommended for fields that frequently appear in the WHERE, ORDER BY, and GROUP BY clauses of SELECT queries, and for fields used to join tables together. ■ You can specify that values entered into a field must be either unique—that is, not duplicated—or NULL with the UNIQUE modifier.

Selecting a Table Type Following the field definitions and modifiers come one or more table modifiers, which specify tablelevel attributes. Of these, the most frequently used one is the TYPE modifier, which tells MySQL which table type to use. A number of such types are available, each with different advantages. Here is a list: ■ MyISAM The MyISAM format is optimized for speed and reliability, it supports tables in excess of 4GB in size, and it can be compressed to save space. This is MySQL’s default table type and, as such, contains numerous MySQL-specific optimizations and features. Select this table type by adding TYPE = MYISAM to your CREATE TABLE statement. ■ InnoDB The successor to the MyISAM format, the InnoDB format, is the most sophisticated table type available in MySQL. It supports transactions and foreign keys (the only MySQL table type to do both), and allows multiple simultaneous users to execute SELECT statements; this improves performance and query response times. InnoDB tables are fully portable between different operating systems, and include crash recovery features to avoid data corruption or loss. Select this table type by adding TYPE = INNODB to your CREATE TABLE statement. ■ HEAP A HEAP table is stored in memory, making it extremely fast. This format is optimized for temporary tables and it is rarely used for other purposes. This is because the data in a HEAP table is available only while the server is running, and is automatically erased when the server shuts down and the memory is flushed. 88

Web Trainings Institute

Select this table type by adding TYPE = HEAP to your CREATE TABLE statement. ■ BerkeleyDB The BerkeleyDB format is one of the more advanced table formats supported by MySQL. It supports transactions, checkpoints, crash recovery, and page-level locking. However, it also has certain disadvantages: BerkeleyDB tables are not easily portable between different operating systems and they lack many of the optimizations of the MyISAM format, making them slower and less memory efficient. Select this table type by adding TYPE = BDB to your CREATE TABLE statement. ■ MERGE The MERGE table format makes it possible for a collection of MyISAM tables to be treated as one, by combining them into a single “virtual” table. This table format makes improving performance or increasing query efficiency possible in certain situations; however, it can only be used for tables that are completely identical in their internal structure. Select this table type by adding TYPE = MERGE to your CREATE TABLE statement. ■ ISAM The forerunner of the newer MyISAM format, the ISAM format is primarily offered for compatibility with older MySQL tables. It lacks many of the features of the MyISAM format, cannot handle large tables, and is more prone to fragmentation (which degrades performance). Select this table type by adding TYPE = ISAM to your CREATE TABLE statement. Now that you know what the various components of a CREATE TABLE statement and writing the corresponding CREATE TABLE statement for each. Here’s how: mysql> CREATE TABLE persons ( -> pid int(11) NOT NULL auto_increment, -> pname varchar(255) NOT NULL default '', -> PRIMARY KEY (pid) -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE roles ( -> mid int(11) NOT NULL default '0', -> pid int(11) NOT NULL default '0', -> role enum('A','D') NOT NULL default 'A', -> PRIMARY KEY mid (mid,pid,role) -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.11 sec) 89

Web Trainings Institute

Altering Tables Table definitions created with the CREATE TABLE command are not set in stone— you’re free to alter them at a later date as well. The SQL command to do this is the ALTER TABLE command. It lets you add or delete fields; alter field types; add, remove, or modify keys; alter the table type; and change the table name (among other things). The following sections discuss these capabilities in greater detail.

Altering Table and Field Names To alter a table name, use an ALTER TABLE command with a supplementary RENAME clause. The following example demonstrates, by renaming table bills to invoices: mysql> ALTER TABLE bills RENAME TO invoices; Query OK, 0 rows affected (0.11 sec) An alternative is to use the RENAME TABLE command, which does the same thing: mysql> RENAME TABLE bills TO invoices; Query OK, 0 rows affected (0.06 sec) You can just as easily alter a field name. Here’s an example, which uses the ALTER TABLE command with a CHANGE clause to modify the name of field address to address1: mysql> ALTER TABLE users CHANGE address address1 VARCHAR(255); Query OK, 0 rows affected (0.17 sec) Notice that you must include the column definition when changing a field name in this manner, or else MySQL will generate an error and disallow the operation.

Altering Field Properties You can use the CHANGE clause discussed in the previous section to alter a field’s type and properties as well, simply by using a new column definition instead of the original one. Here’s an example, which changes the field named tel defined as VARCHAR(30) to a field named age with definition INYINT(2): mysql> ALTER TABLE users CHANGE tel age TINYINT(2); Query OK, 0 rows affected (0.05 sec) 90

Web Trainings Institute

When you CHANGE a field from one type to another, MySQL will automatically attempt to convert the data in that field to the new type. If the data in the field is inconsistent with the new field definition—for example, a field defined as NOT NULL contains NULL values, or a field marked as UNIQUE contains duplicate values—MySQL will generate an error. You can alter this default behavior by adding an IGNORE clause to the ALTER TABLE command that tells MySQL to ignore such inconsistencies.

Adding and Removing Fields and Keys You can add a new field to a table by including an ADD clause in your ALTER TABLE command. The following example demonstrates, by adding a field named salary to the employees table: mysql> ALTER TABLE employees ADD salary INT(7) NOT NULL; Query OK, 0 rows affected (0.06 sec) You can also do the reverse—delete an existing field from a table—by using a DROP clause instead of an ADD clause. The following example removes the field added in the previous operation (together with any data it might have contained): mysql> ALTER TABLE employees DROP salary; Query OK, 0 rows affected (0.05 sec) You can delete a table’s primary key with the DROP PRIMARY KEY clause, as illustrated here: mysql> ALTER TABLE users DROP PRIMARY KEY; Query OK, 0 rows affected (0.06 sec) and add a new primary key with the ADD PRIMARY KEY clause, as illustrated here: mysql> ALTER TABLE users ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.05 sec) Altering Table Types You can alter the table type by adding a TYPE clause to the ALTER TABLEcommand, as in the following example: mysql> ALTER TABLE data TYPE = INNODB; Query OK, 6 rows affected (0.11 sec) 91

Web Trainings Institute

Dropping Databases and Tables To delete a database, use the DROP DATABASE command, which deletes the named database and all its tables permanently. Try this out by creating and dropping a database: mysql> CREATE DATABASE music; Query OK, 1 row affected (0.05 sec) mysql> DROP DATABASE music; Query OK, 0 rows affected (0.49 sec) Similarly, you can delete a table with the DROP TABLE command. Try this out by creating and dropping a table: mysql> CREATE TABLE members ( memberId INT NOT NULL ); Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE members; Query OK, 0 rows affected (0.00 sec) The DROP TABLE command will immediately wipe out the specified table, together with all the data it contains—so use it with care! If what you wanted was to empty the table of all records, use the TRUNCATE TABLE command instead, which internally DROP-s the table, and then re-creates it. The AUTO_INCREMENT counter, if one exists, is automatically reset in TRUNCATE TABLE operations (this does not happen if you simply delete all the records in the table with a DELETE command). Here is an example: mysql> TRUNCATE TABLE movies; Query OK, 0 rows affected (0.01 sec)

Editing Records and Performing Queries Inserting Records Once you’ve created a table, it’s time to begin entering data into it-and the SQL command to accomplish this is the INSERT command. The syntax of the INSERTcommand is illustrated in the following example: 92

Web Trainings Institute

mysql> INSERT INTO movies (mtitle, myear) VALUES ('Rear Window', 1954); Query OK, 1 row affected (0.06 sec) The INSERT command is followed by the optional keyword INTO, a table name, and a field list, in parentheses, which indicates which fields the values are to be inserted into. A VALUES clause completes the command, by specifying the values to be inserted into the previously named fields. You can also use an abbreviated form of the INSERT command, in which the field list is left unspecified. The following example, which is equivalent to the previous one illustrates the following: mysql> INSERT INTO movies VALUES (NULL, 'Rear Window', 1954); Query OK, 1 row affected (0.06 sec) When using this shorter format, the order in which values are inserted must correspond to the sequence of fields in the table (you can determine the field order with a quick call to the DESCRIBE command, described in Chapter 9).Normally, the first version of the INSERT command is preferable, because it offers you the flexibility of inserting values in any order you please and protects you from structural changes in the table. Because of this, the following statements are equivalent: mysql> INSERT INTO movies (mtitle, myear) VALUES ('Rear Window', 1954); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO movies (myear, mtitle) VALUES (1954, 'Rear Window'); Query OK, 1 row affected (0.06 sec) In MySQL, you can insert multiple records into a table at once, by using multiple VALUES() clauses within the same INSERT statement. To see how this works, try running the following command: mysql> INSERT INTO movies (mtitle, myear) VALUES ('Rear Window', 1954), ('To Catch A Thief', 1955), ('The Maltese Falcon', 1941); Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0 Fields that are not specified in the INSERT command will either be set to NULL or to their default values, depending on how they have been defined.MySQL comes with built-in intelligence to automatically deal with conflicts Now that you know how to insert records, try inserting some sample records for the three tables created in the previous section, using the sample data in 93

Web Trainings Institute

You can start with these samples: mysql> INSERT INTO movies VALUES (1,'Rear Window',1954); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO persons VALUES (1,'Alfred Hitchcock','M','1899-08-13'); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO roles VALUES (1,1,'D'), (1,3,'A'); Query OK, 2 rows affected (0.06 sec)

Editing and Deleting Records Data in a database usually changes over time, which is why SQL includes anUPDATE command designed to change existing values in a table. As with the DELETE command described previously, you can use the UPDATE command to change all the values in a particular column, or change only those values matching a particular condition. To illustrate how this works, consider the following example, which changes the value of the field 'The Maltese Falcon' to 'Maltese Falcon, The'. mysql> UPDATE movies SET mtitle = 'Maltese Falcon, The' WHERE mtitle = 'The Maltese Falcon'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 You can update multiple fields at once, simply by using multiple SET clauses. The following example illustrates, by updating record #7 with a new movie title and year: mysql> UPDATE movies SET mtitle = 'Vertigo', myear = 1958 WHERE mid = 7; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 Thus, the SET clause specifies the field name, as well as the new value for the field. The WHERE clause is used to identify which rows of the table to change. In the absence of this clause, all the rows of the table are updated with the new value. Try this out by entering the following command, which updates the psex field in the persons table: mysql> UPDATE persons SET psex = 'M'; Query OK, 1 row affected (0.06 sec) Rows matched: 6 Changed: 1 Warnings: 0 94

Web Trainings Institute

Performing Queries Just as you can add records to a table with the INSERT command, you can retrieve them with the SELECT command. The SELECT command is one of the most versatile and useful commands in SQL. It offers tremendous flexibility in extracting specific subsets of data from a table. In its most basic form, the SELECT statement can be used to evaluate expressions and functions, or as a “catch-all” query that returns all the records in a specific table. Here is an example of using SELECT to evaluate mathematical expressions: mysql> SELECT 75 / 15, 61 + (3 * 3); +---------+--------------+ | 75 / 15 | 61 + (3 * 3) | +---------+--------------+ | 5.00 | 70 | +---------+--------------+ 1 row in set (0.05 sec) And here is an example of using SELECT to retrieve all the records in a table: mysql> SELECT * FROM movies; +-----+---------------------+-------+ | mid | mtitle | myear | +-----+---------------------+-------+ | 1 | Rear Window | 1954 | | 2 | To Catch A Thief | 1955 | | 3 | The Maltese Falcon | 1941 | | 4 | The Birds | 1963 | | 5 | North By Northwest | 1959 | | 6 | Casablanca | 1942 | | 7 | Anatomy Of A Murder | 1959 | +-----+---------------------+-------+ 7 rows in set (0.00 sec) Retrieving Specific Columns

95

Web Trainings Institute

The asterisk (*) in the previous example indicates that you’d like the output of SELECT to contain all the columns present in the table. If, instead, you’d prefer to see one or two specific columns only in the result set, you can specify the column name(s) in the SELECT statement, like this: mysql> SELECT mtitle FROM movies; +---------------------+ | mtitle | +---------------------+ | Rear Window | | To Catch A Thief | | The Maltese Falcon | | The Birds | | North By Northwest | | Casablanca | | Anatomy Of A Murder | +---------------------+ 7 rows in set (0.00 sec)

Filtering Records with a WHERE Clause You can also restrict which records appear in the result set, by adding a WHERE clause to your SELECT statement. This WHERE clause lets you define specific criteria used to filter records from the result set. Records that do not meet the specified criteria will not appear in the result set. For example, suppose you want to find out which year Casablanca was released: mysql> SELECT myear FROM movies WHERE mtitle = 'Casablanca'; +-------+ | myear | +-------+ | 1942 | +-------+ 1 row in set (0.11 sec)

Using Operators

96

Web Trainings Institute

The = symbol previously used is an equality operator, used to test whether the left side of the expression is equal to the right side. MySQL comes with numerous such operators that can be used in the WHERE clause for comparisons and calculations. Table 10-1 lists the important operators in MySQL, by category. Here is an example of using a comparison operator in the WHERE clause, to list all movies released after 1950: mysql> SELECT myear, mtitle FROM movies WHERE myear > 1950; +-------+---------------------+ | myear | mtitle | +-------+---------------------+ | 1954 | Rear Window | | 1955 | To Catch A Thief | | 1963 | The Birds | | 1959 | North By Northwest | | 1959 | Anatomy Of A Murder | +-------+---------------------+ 5 rows in set (0.00 sec) Operators Arithmetic operators + Addition - Subtraction * Multiplication / Division; returns quotient % Division; returns modulus

Comparison operators = Equal to <> aka != Not equal to <=> NULL-safe equal to < Less than <= Less than or equal to > Greater than >= Greater than or equal to BETWEEN Exists in specified range 97

Web Trainings Institute

IN Exists in specified set IS NULL Is a NULL value IS NOT NULL Is not a NULL value LIKE Wildcard match REGEXP aka RLIKE Regular expression match

Logical operators NOT aka ! Logical NOT AND aka && Logical AND OR aka || Logical OR XOR Exclusive OR You can combine multiple conditions by using the AND or OR logical operators. This next example lists all movies released between 1955 and 1965: mysql> SELECT mtitle FROM movies WHERE myear >= 1955 AND myear <= 1965; +---------------------+ | mtitle | +---------------------+ | To Catch A Thief | | The Birds | | North By Northwest | | Anatomy Of A Murder | +---------------------+ 4 rows in set (0.06 sec) Another way to perform this comparison is with the BETWEEN operator: mysql> SELECT mtitle FROM movies WHERE myear BETWEEN 1955 AND 1965; +---------------------+ | mtitle | +---------------------+ | To Catch A Thief | | The Birds | | North By Northwest | | Anatomy Of A Murder | +---------------------+ 4 rows in set (0.06 sec) 98

Web Trainings Institute

The LIKE operator can be used to perform queries using wildcards, and comes in handy when you’re not sure what you’re looking for. Two types of wildcards are allowed when using the LIKE operator: the % wildcard, which is used to signify zero or more occurrences of a character, and the _ wildcard, which is used to signify exactly one occurrence of a character. Sorting Records and Eliminating Duplicates If you’d like to see the data from your table ordered by a specific field, SQL offers the ORDER BY clause. This clause enables you to specify both the column name and the direction in which you would like to see data (ASCending or DESCending). Here is an example of sorting the persons table by name, in ascending order: mysql> SELECT * FROM persons ORDER BY pname ASC; +-----+--------------------+------+------------+ | pid | pname | psex | pdob | +-----+--------------------+------+------------+ | 1 | Alfred Hitchcock | M | 1899-08-13 | | 2 | Cary Grant | M | 1904-01-18 | | 3 | Grace Kelly | F | 1929-11-12 | | 4 | Humphrey Bogart | M | 1899-12-25 | | 6 | James Stewart | M | 1908-05-20 | | 5 | Sydney Greenstreet | M | 1879-12-27 | +-----+--------------------+------+------------+ 6 rows in set (0.00 sec) And here is the same table sorted by date of birth, in descending order: mysql> SELECT * FROM persons ORDER BY pdob DESC; +-----+--------------------+------+------------+ | pid | pname | psex | pdob | +-----+--------------------+------+------------+ | 3 | Grace Kelly | F | 1929-11-12 | | 6 | James Stewart | M | 1908-05-20 | | 2 | Cary Grant | M | 1904-01-18 | | 4 | Humphrey Bogart | M | 1899-12-25 | | 1 | Alfred Hitchcock | M | 1899-08-13 | | 5 | Sydney Greenstreet | M | 1879-12-27 | +-----+--------------------+------+------------+ 6 rows in set (0.00 sec) 99

Web Trainings Institute

Limiting Results You can limit the number of records returned by MySQL with the LIMIT clause, as illustrated in the following: mysql> SELECT mtitle FROM movies LIMIT 0,4; +--------------------+ | mtitle | +--------------------+ | Rear Window | | To Catch A Thief | | The Maltese Falcon | | The Birds | +--------------------+ 4 rows in set (0.00 sec)

Need for Speed MySQL 4.0 includes a query cache, which can substantially improve performance by caching the results of common queries and returning this cached data to the caller without having to reexecute the query each time. You can even combine the ORDER BY and LIMIT clauses to return a sorted list restricted to a certain number of values. The following example illustrates, by listing the three oldest people (as per their birth dates) in the persons table: mysql> SELECT pname FROM persons ORDER BY pdob LIMIT 0,3; +--------------------+ | pname | +--------------------+ | Sydney Greenstreet | | Alfred Hitchcock | | Humphrey Bogart | +--------------------+ 3 rows in set (0.00 sec)

Using Built-In Functions MySQL comes with over 100 built-in functions to help you perform calculations and process the records in a result set. These functions can be used in a SELECT statement, either to manipulate field values or 100

Web Trainings Institute

in the WHERE clause. The following example illustrates, by using MySQL’s COUNT() function to return the total number of records in the movies table: mysql> SELECT COUNT(*) FROM movies; +----------+ | COUNT(*) | +----------+ |7| +----------+ 1 row in set (0.00 sec) You can calculate string length with the LENGTH() function, as in the following: mysql> SELECT pname, LENGTH(pname) FROM persons; +--------------------+---------------+ | pname | LENGTH(pname) | +--------------------+---------------+ | Alfred Hitchcock | 16 | | Cary Grant | 10 | | Grace Kelly | 11 | | Humphrey Bogart | 15 | | Sydney Greenstreet | 18 | | James Stewart | 13 | +--------------------+---------------+ 6 rows in set (0.00 sec)

You can use the DATE() function to format date and time values into a human-readable form, as illustrated in the following: mysql> SELECT pname, DATE_FORMAT(pdob, '%W %d %M %Y') FROM persons; +--------------------+----------------------------------+ | pname | DATE_FORMAT(pdob, '%W %d %M %Y') | +--------------------+----------------------------------+ | Alfred Hitchcock | Sunday 13 August 1899 | | Cary Grant | Monday 18 January 1904 | | Grace Kelly | Tuesday 12 November 1929 | | Humphrey Bogart | Monday 25 December 1899 | | Sydney Greenstreet | Saturday 27 December 1879 | | James Stewart | Wednesday 20 May 1908 | +--------------------+----------------------------------+ 101

Web Trainings Institute

6 rows in set (0.00 sec) You can even use functions in the WHERE clause of a SELECT statement. The following example illustrates, by listing all those people who would be more than 100 years old today if they were still alive: mysql> SELECT pname FROM persons WHERE YEAR(NOW()) - YEAR(pdob) > 100; +--------------------+ | pname | +--------------------+ | Alfred Hitchcock | | Humphrey Bogart | | Sydney Greenstreet | +--------------------+ 3 rows in set (0.06 sec) Grouping Records You can group records on the basis of a specific field with MySQL’s GROUP BY clause. Each group created in this manner is treated as a single row, even though it internally contains multiple records. Consider the following example, which groups the records in the persons table on the basis of their sex: mysql> SELECT * FROM persons GROUP BY psex; +-----+------------------+------+------------+ | pid | pname | psex | pdob | +-----+------------------+------+------------+ | 1 | Alfred Hitchcock | M | 1899-08-13 | | 3 | Grace Kelly | F | 1929-11-12 | +-----+------------------+------+------------+ 2 rows in set (0.00 sec) A number of specialized functions are available when grouping records in this manner. The most commonly used one in this context is the COUNT() function, which you saw earlier. In the context of a GROUP BY clause, this function can be used to count the number of records in each group. The following example illustrates by counting the number of males and females in the persons table: mysql> SELECT psex, COUNT(psex) FROM persons GROUP BY psex; +------+-------------+ | psex | COUNT(psex) | +------+-------------+ |M|5| 102

Web Trainings Institute

|F|1| +------+-------------+ 2 rows in set (0.00 sec) Here’s another example, this one returning the number of persons linked to each movie in the roles table: mysql> SELECT mid, COUNT(pid) FROM roles GROUP BY mid; +-----+------------+ | mid | COUNT(pid) | +-----+------------+ |1|3| |2|3| |3|2| |4|1| |5|2| |6|1| +-----+------------+ 6 rows in set (0.06 sec) You can further filter the groups by adding a HAVING clause to the GROUP BY clause. This HAVING clause works much like a regular WHERE clause, letting you further filter the grouped data by a specific condition. The following examplerevises the previous one to only return those movies having two or more persons linked to them: mysql> SELECT mid, COUNT(pid) FROM roles GROUP BY mid HAVING COUNT(pid) >= 2; +-----+------------+ | mid | COUNT(pid) | +-----+------------+ |1|3| |2|3| |3|2| |5|2| +-----+------------+ 4 rows in set (0.00 sec) Joining Tables So far, all the queries you’ve seen have been concentrated on a single table. But SQL also enables you to query two or more tables at a time, and to display a combined result set. This is technically referred to as 103

Web Trainings Institute

a join, because it involves “joining” different tables at specific points to create new views of the data. MySQL has supported joins well right from its inception, and today boasts support for standard SQL2-compliant join syntax, which makes it possible to combine table records in a variety of sophisticated ways. When using a join, the recommendation is that you prefix each field name with the name of the table it belongs to. For example, you would use movies.mid to refer to the field named mid in the table movies, and roles.pid to refer to the pid field in the roles table. Inner Joins Here’s an example of a simple join: mysql> SELECT * FROM movies, roles WHERE movies.mid = roles.mid; +-----+---------------------+-------+-----+-----+------+ | mid | mtitle | myear | mid | pid | role | +-----+---------------------+-------+-----+-----+------+ | 1 | Rear Window | 1954 | 1 | 1 | D | | 1 | Rear Window | 1954 | 1 | 3 | A | | 1 | Rear Window | 1954 | 1 | 6 | A | | 2 | To Catch A Thief | 1955 | 2 | 1 | D | | 2 | To Catch A Thief | 1955 | 2 | 2 | A | | 2 | To Catch A Thief | 1955 | 2 | 3 | A | | 3 | Maltese Falcon, The | 1941 | 3 | 4 | A | | 3 | Maltese Falcon, The | 1941 | 3 | 5 | A | | 4 | The Birds | 1963 | 4 | 1 | D | | 5 | North By Northwest | 1959 | 5 | 1 | D | | 5 | North By Northwest | 1959 | 5 | 2 | A | | 6 | Casablanca | 1942 | 6 | 4 | A | +-----+---------------------+-------+-----+-----+------+ 12 rows in set (0.00 sec) In this case, the movies and roles tables have been joined together through the common field mid. Such a join is referred to as an inner join, because its result set contains only those records that match in all the tables in the join. Records that do not match are excluded from the final result set. Outer Joins MySQL also supports outer joins, which are asymmetrical-all records from one side of the join are included in the final result set, regardless of whether they match records on the other side of the join. Consider the following example, which illustrates by using a left outer join to connect the movies table to the roles table: 104

Web Trainings Institute

mysql> SELECT * FROM movies LEFT JOIN roles ON movies.mid = roles.mid; +-----+---------------------+-------+------+------+------+ | mid | mtitle | myear | mid | pid | role | +-----+---------------------+-------+------+------+------+ | 1 | Rear Window | 1954 | 1 | 1 | D | | 1 | Rear Window | 1954 | 1 | 3 | A | | 1 | Rear Window | 1954 | 1 | 6 | A | | 2 | To Catch A Thief | 1955 | 2 | 1 | D | | 2 | To Catch A Thief | 1955 | 2 | 2 | A | | 2 | To Catch A Thief | 1955 | 2 | 3 | A | | 3 | Maltese Falcon, The | 1941 | 3 | 4 | A | | 3 | Maltese Falcon, The | 1941 | 3 | 5 | A | | 4 | The Birds | 1963 | 4 | 1 | D | | 5 | North By Northwest | 1959 | 5 | 1 | D | | 5 | North By Northwest | 1959 | 5 | 2 | A | | 6 | Casablanca | 1942 | 6 | 4 | A | | 7 | Vertigo | 1958 | NULL | NULL | NULL | +-----+---------------------+-------+------+------+------+ 13 rows in set (0.06 sec) As you can see, all the rows from the table on the left side of the join appear in the final result set. Those that have a corresponding value in the table on the right side as per the match condition have that value displayed; the rest have a NULL value displayed. This kind of join comes in handy when you need to see which values from one table are missing in another table-all you need to do is look for the NULL rows. From a quick glance at the previous example, you can see that entries for all the movies in the movies table exist in the roles table, except for the movie Vertigo. Thus, outer joins come in handy when you’re looking for corrupted, or “dirty,” data in interrelated tables. Using Sub Queries Subqueries, as the name suggests, are queries nested inside other queries. They make it possible to use the results of one query directly in the conditional tests or FROM clauses of other queries. Subqueries can substantially simplify the task of writing SQL-based applications, by reducing the number of application-level query statements to be executed in a given program. Subqueries come in many shapes, sizes, and forms. The most common is a SELECT within a SELECT, such that the results of the inner SELECT serve as values for the WHERE clause of the outer SELECT. However, while this is certainly one of the most common uses of subqueries, it’s not the only one. You 105

Web Trainings Institute

can use subqueries in a number of other places, including within grouped result sets, with comparison and logical operators, with membership tests, in UPDATE and DELETE operations, and within a query’s FROM clause. To see how a subquery works, try out the following example, which prints a list of all those movie IDs starring Cary Grant: mysql> SELECT mid FROM roles WHERE role = 'A' AND pid = (SELECT pid FROM persons WHERE pname = 'Cary Grant'); +-----+ | mid | +-----+ |2| |5| +-----+ 2 rows in set (0.00 sec) Here, the inner query is executed first, and returns the ID of the record for “Cary Grant” from the persons table. This ID (#1) is then substituted in the outer query’s WHERE clause, and the query is executed on the roles table to list all those movies in which he performed. However, this is still incomplete-the previous double query only returns a list of movie IDs, not titles. For this to be truly valuable, you need the movie titles. So, wrap the previous combination in yet another query, which takes the list of IDs generated and matches them against the movies table to return the corresponding titles: mysql> SELECT mtitle FROM movies WHERE mid IN (SELECT mid FROM roles WHERE role = 'A' AND pid = (SELECT pid FROM persons WHERE pname = 'Cary Grant')); +--------------------+ | mtitle | +--------------------+ | To Catch A Thief | | North By Northwest | +--------------------+ 2 rows in set (0.06 sec) Thus, a subquery makes it possible to combine two or more queries into a single statement, and to use the results of one query in the conditional clause of the other.Subqueries are usually regular SELECT statements, separated from their parent query by parentheses. As the previous example illustrates, you can nest subqueries to any depth, as long as the basic rules are followed. 106

Web Trainings Institute

Using Table and Column Aliases For table and field names that are either too long to comfortably use or too complex to read, use the AS keyword to alias the name to a different value. The following example demonstrates, by aliasing the name of the personstable to p and the psex, pname, and pdob fields to Sex, realName, and DateOfBirth: mysql> SELECT p.psex AS Sex, p.pname AS RealName, p.pdob AS DateOfBirth FROM persons AS p; +-----+--------------------+-------------+ | Sex | RealName | DateOfBirth | +-----+--------------------+-------------+ | M | Alfred Hitchcock | 1899-08-13 | | M | Cary Grant | 1904-01-18 | | F | Grace Kelly | 1929-11-12 | | M | Humphrey Bogart | 1899-12-25 | | M | Sydney Greenstreet | 1879-12-27 | | M | James Stewart | 1908-05-20 | +-----+--------------------+-------------+ 6 rows in set (0.00 sec) This also works on fields that are the result of a calculation or function operation. The following examples demonstrate this: mysql> SELECT COUNT(*) AS total FROM movies; +-------+ | total | +-------+ |7| +-------+ 1 row in set (0.00 sec) mysql> SELECT pname AS name, YEAR(NOW()) - YEAR (pdob) AS age FROM persons ORDER BY age; +--------------------+------+ | name | age | +--------------------+------+ 107

Web Trainings Institute

| Grace Kelly | 75 | | James Stewart | 96 | | Cary Grant | 100 | | Alfred Hitchcock | 105 | | Humphrey Bogart | 105 | | Sydney Greenstreet | 125 | +--------------------+------+ 6 rows in set (0.05 sec)

108

Web Trainings Institute

Using MySQL and PHP PHP has included support for MySQL since version 3.x, although the procedure to activate this support has varied widely between versions. PHP 4.x included a set of MySQL client libraries, which were activated by default. PHP 5.x no longer bundles these libraries, however, due to licensing issues, so you need to obtain and install them separately. Then, you need to explicitly activate the MySQL extension— ext/mysql—by adding the --with-mysql option to PHP’s configure script. The MySQL API built into PHP is designed to accomplish four primary goals: ■ Manage database connections ■ Execute queries ■ Process query results ■ Provide debugging and diagnostic information To illustrate these functions, let’s create a simple MySQL database table, and then use PHP to connect to the server, retrieve a set of results, and format them for display on a web page. The sample table used here consists of a single table named items, which holds a list of products and their prices. Here are the SQL queries needed to create and initialize this table: CREATE TABLE items ( itemID int(11) NOT NULL auto_increment, itemName varchar(255) NOT NULL default '', itemPrice float NOT NULL default '0', PRIMARY KEY (itemID) ) TYPE=MyISAM; INSERT INTO items VALUES (1, 'Paperweight', '3.99'); INSERT INTO items VALUES (2, 'Key ring', '2.99'); INSERT INTO items VALUES (3, 'Commemorative plate', '14.99'); INSERT INTO items VALUES (4, 'Pencils (set of 4)', '1.99'); INSERT INTO items VALUES (5, 'Coasters (set of 3)', '4.99'); You can enter these commands either interactively or noninteractively through the MySQL client program. Once entered, run a SELECT query to ensure that the data has been successfully imported. mysql> SELECT * FROM items; +--------+---------------------+-----------+ | itemID | itemName | itemPrice | +--------+---------------------+-----------+ | 1 | Paperweight | 3.99 | | 2 | Key ring | 2.99 | 109

Web Trainings Institute

| 3 | Commemorative plate | 14.99 | | 4 | Pencils (set of 4) | 1.99 | | 5 | Coasters (set of 3) | 4.99 | +--------+---------------------+-----------+ 5 rows in set (0.00 sec) Now, to do the same thing using PHP, create the following PHP script:

0) { // print HTML table echo ''; echo ''; // iterate over record set // print each field while($row = mysql_fetch_row($result)) { echo ''; echo ''; echo ''; echo ''; echo ''; } 110

Web Trainings Institute

echo '

ID	Name	Price
' . $row[0] . '	' . $row[1] . '	' . $row[2] . '

'; } else { // print error message echo 'No rows found!'; } // once processing is complete // free result set mysql_free_result($result); // close connection to MySQL server mysql_close($connection); ?> Example Output

111

Web Trainings Institute

An examination of the previous script will reveal that using PHP to perform and process a MySQL query involves several steps, which the following explains. 1. To begin communication with the MySQL database server, you first need to open a connection to the server. All communication between PHP and the database server takes place through this connection, which is initialized by the mysql_connect() function. The mysql_connect() function requires three parameters: the host name of the MySQL server, and the MySQL username and password required to gain access to it. If the function is able to successfully initialize a connection, it returns a link identifier, which is stored in the variable $connection. This identifier is used throughout the script when communicating with the database

2. Once a connection has been initialized, the next step is to select a databasefor use (this is equivalent to the SQL USE command) with the mysql_select_db() command, and then send the server a query through the mysql_query() function. Both functions use the last opened connection as their default for all operations. 3. The result set returned by the query is assigned to the variable $result. This result set may contain, depending on your query, zero or more rows or columns of data. The number of rows in the result set is obtained from the mysql_num_rows() function. Assuming one or more rows exist, the mysql_fetch_row() function is used to iterate over the result set and retrieve rows as arrays. Individual field values can then be accessed as array elements.

Managing Database Connections In PHP, connections to the MySQL server are opened via the mysql_connect()function, which accepts a number of different arguments: the hostname (and,optionally, the port number) of the MySQL server, the MySQL username to gainaccess, and the corresponding password. Here are some examples:
Web Trainings Institute

} ?> If a connection can be established, the mysql_connect() function returns a link identifier, which is used by other functions to communicate with the server. If a connection cannot be established, the function returns false and, depending on the default error level of the PHP script, an error message indicating the cause of failure will be printed to the output device (usually the browser). To avoid this automatically generated error message, it is necessary to prefix the call to mysql_connect() with PHP’s special error-suppression operator: @. Here is an example: Normally, the link to the server remains open for the lifetime of the script,and is automatically closed by PHP once the script completes executing. Thatsaid, just as it’s good manners to close the doors you open, it’s good programming practice to explicitly close the MySQL connection once you finish using it. This is accomplished by calling the mysql_close() function, which closes the link and returns the used memory to the system. Here is an example: Performing Queries 113

Web Trainings Institute

Once a connection has been opened, the next step is to select a database for use.This is done with the mysql_select_db() function, which accepts a database name as argument. It can optionally also accept a link identifier; if this is not specified, the function defaults to using the last opened connection. Here’s an example of how it may be used: Once the database has been selected, it becomes possible to execute queries on it. In PHP, MySQL queries are handled via the mysql_query() function, which accepts a query string and a link identifier and sends it to the server represented by the link identifier. If no link identifier is specified, the last opened link is used as the default. Here is an example: Depending on the type of query, the return value of mysql_query() differs: ■ If the query is a data-retrieval query—for example, a SELECT or SHOW query—then mysql_query() returns a resource identifier pointing to the query’s result set, or false on failure. The resource identifier can then be used to process the records in the result set. ■ If the query is a data manipulation query—for example, an INSERT or UPDATE query—then mysql_query() returns true if the query succeeds, or false on failure.The result-set processing functions outlined in the next section can now be used to extract data from the return value of mysql_query().

Processing Result Sets The return value of a successful mysql_query() invocation can be processed in a number of different ways, depending on the type of query executed. Queries Which Return Data

114

Web Trainings Institute

For SELECT-type queries, a number of techniques exist to process the returned data. The simplest is the mysql_fetch_row() function, which returns each record as a numerically indexed PHP array. Individual fields within the record can then be accessed using standard PHP-array notation. The following example illustrates this: 0) { // iterate over record set // print each field while($row = mysql_fetch_row($result)) { echo $row[0] . " - " . $row[1] . "\n"; } } else { // print error message echo 'No rows found!'; } // once processing is complete // free result set mysql_free_result($result); // close connection to MySQL server mysql_close($connection); ?> Notice, in the previous listing, how the call to mysql_fetch_row() is wrapped in a mysql_num_rows() conditional test. The mysql_num_rows() function returns the number of records in the result set and comes in handy to check whether the query returned any records at all. 115

Web Trainings Institute

You can use PHP’s mysql_fetch_assoc() function to represent each row as an associative array of field-value pairs, a minor variation of the previously used technique. Take a look: 0) { // iterate over record set // print each field while($row = mysql_fetch_assoc($result)) { echo $row['itemName'] . " - " . $row['itemPrice'] . "\n"; } } else { // print error message echo 'No rows found!'; } // once processing is complete // free result set mysql_free_result($result); // close connection to MySQL server mysql_close($connection); ?> In this case, field values are accessed using the field name instead of the index. There’s also the mysql_fetch_object() function, which returns each row as an object, with properties corresponding to the field names. Here is an example:
Web Trainings Institute

// open connection to MySQL server $connection = mysql_connect('localhost', 'guest', 'pass') or die ('Unable to connect!'); // select database for use mysql_select_db('db2') or die ('Unable to select database!'); // create and execute query $query = 'SELECT itemName, itemPrice FROM items'; $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error()); // check if records were returned if (mysql_num_rows($result) > 0) { // iterate over record set // print each field while($row = mysql_fetch_object($result)) { echo $row->itemName . " - " . $row->itemPrice . "\n"; } } else { // print error message echo 'No rows found!'; } // once processing is complete // free result set mysql_free_result($result); // close connection to MySQL server mysql_close($connection); ?> In this case, each $row object is created with properties corresponding to the field names in that row. Row values can be accessed using standard $object-> property notation. Queries That Alter Data You can also use PHP’s MySQL API for queries that don’t return a result set, for example, INSERT or UPDATE queries. Consider the following example, which demonstrates by asking for user input through a form, and then INSERT-ing that data into the database: 117

Web Trainings Institute

Item name:

Item price: '; // print number of rows affected echo mysql_affected_rows() . ' record(s) affected'; // close connection mysql_close($connection); 118

Web Trainings Institute

} ?> Here, the user is first presented with a form asking for an item and its associated price. Once the form is submitted, the form input is used inside to create an INSERT query, which is then sent to the database with the mysql_query() method. Because mysql_query() returns a Boolean indicating whether the query was successful, it is possible to check whether the INSERT took place and return an appropriate message. The previous example has three new functions: ■ The mysql_escape_string() function escapes special characters (like quotes) in the user input, so it can be safely entered into the database. If the magic_quotes_gpc setting in your PHP configuration file is enabled, you might need to first call stripslashes() on the user input before calling mysql_escape_string(), to avoid characters getting escaped twice. ■ The mysql_insert_id() function returns the ID generated by the previous INSERT query (useful only if the table into which the INSERT occurs contains an AUTO_INCREMENT field). ■ The mysql_affected_rows() function returns the total number of rows affected by the last operation. All these functions come in handy when dealing with queries that alter the database. Handling Errors Before you go out there and start building data-driven web sites, you should be aware that PHP’s MySQL API also comes with some powerful error-tracking functions that can reduce debugging time. Take a look at the following example, which contains a deliberate error in the SELECT query string:
Web Trainings Institute

if(!$result) { echo 'MySQL error ' . mysql_errno() . ': ' . mysql_error(); mysql_close($connection); } ?> The mysql_errno() function displays the error code returned by MySQL if there’s an error in your SQL statement, while the mysql_error() function returns the actual error message. Turn these both on, and you’ll find they can significantly reduce the time you spend fixing bugs. Using Ancillary Functions In addition to the functions discussed in previous sections, PHP’s MySQL API comes with a number of ancillary functions that may be used to find out more about the databases and tables on the MySQL server or to obtain server status information. Table 13-1 lists the important functions in this category. And here’s an example that demonstrates some of these functions in action: '; for ($x=0; $x
Web Trainings Institute

mysql_list_dbs() Returns a list of databases available on the MySQL server mysql_list_tables() Returns a list of tables available in a specified MySQL database mysql_list_fields() Returns information about the fields of a specified MySQL table mysql_stat() Returns status information about the MySQL server mysql_info() Returns information about the last executed query mysql_db_name() Returns a name of a database from the list generated by mysql_list_dbs() mysql_tablename() Returns a name of a table from the list generated by mysql_list_tables() mysql_ping() Tests the server connection

121

Web Trainings Institute

Working with HTML Forms Forms are a vital tool for the webmaster to receive information from the web surfer, such as: their name, email address, credit card, etc. A form will take input from the viewer and depending on your needs, you may store that data into a file, place an order, gather user statistics, register the person to your web forum, or maybe subscribe them to your weekly newsletter. Text Fields Before we teach you how to make a complete form, let's start out with the basics of forms. Input fields are going to be the meat of your form's sandwich. The has a few attributes that you should be aware of.    

type - Determines what kind of input field it will be. Possible choices are text, submit, and password. name - Assigns a name to the given field so that you may reference it later. size - Sets the horizontal width of the field. The unit of measurement is in blank spaces. maxlength - Dictates the maximum number of characters that can be entered.

HTML Code:

Name:
Password:

Input Forms: Name: Password: Do not use the password feature for security purposes. The data in the password field is not encrypted and is not secure in any way.

122

Web Trainings Institute

HTML Form Email Now we will add the submit functionality to your form. Generally, the button should be the last item of your form and have its name attribute set to "Send" or "Submit". Name defines what the label of the button will be. Here is a list of important attributes of the submit: In addition to adding the submit button, we must also add a destination for this information and specify how we want it to travel to that place. Adding the following attributes to your

will do just this.  

method - We will only be using the post functionality of method, which sends the data without displaying any of the information to the visitor. action - Specifies the URL to send the data to. We will be sending our information to a fake email address.

HTML Code: Name:
Password:

Email Forms: Name: Password: Send

HTML Radio Buttons Radio buttons are a popular form of interaction. You may have seen them on quizzes, questionnaires, and other web sites that give the user a multiple choice question. Below are a couple attributes you should know that relate to the radio button.  

value - specifies what will be sent if the user chooses this radio button. Only one value will be sent for a given group of radio buttons (see name for more information). name - defines which set of radio buttons that it is a part of. Below we have 2 groups: shade and size. 123

Web Trainings Institute

HTML Code:

What kind of shirt are you wearing?
Shade: Dark Light
Size: Small Medium Large

Radios: What kind of shirt are you wearing? Shade: Size:

Dark Small

Light Medium

Large

Email Myself

If you change the email address to your own and "Email Myself" then you should get an email with "shade=(choice) size=(choice)". HTML Check Boxes Check boxes allow for multiple items to be selected for a certain group of choices. The check box's name and value attributes behave the same as a radio button. HTML Code:

Select your favorite cartoon characters. Goofy Donald 124

Web Trainings Institute

Bugs Bunny Scooby Doo

Check Boxes: Select the 2 greatest toons. Goofy Donald Bugs Bunny Scooby Doo Email Myself

HTML Drop Down Lists Drop down menues are created with the and tags. is the list itself and each is an available choice for the user. HTML Code:

College Degree? Choose One Some High School High School Degree Some College Bachelor's Degree Doctorate

125

Web Trainings Institute

Drop Down Lists: Education?

Email Yourself

HTML Selection Forms Yet another type of form, a highlighted selection list. This form will post what the user highlights. Basically just another type of way to get input from the user. The size attribute selects how many options will be shown at once before needing to scroll, and the selected option tells the browser which choice to select by default. HTML Code:

Musical Taste Emo Metal/Rock Hip Hop Ska Jazz Country Classical Alternative Oldies Techno

126

Web Trainings Institute

Selection Forms:

Musical Taste Emo Metal/Rock Hip Hop Ska

Email Yourself

HTML Upload Forms First of all, to actually make the upload form function correctly you must know a scripting language of some sort. PHP and PERL work fine, Javascript is also an option. We have an entire upload example demonstrated here, PHP File Upload. The HTML code for the upload form does nothing more than create an interface for the user to see and work with. An upload form consists of three basic parts. The first being a hidden field. This hidden field does nothing more than limit the allowed file size of our uploaded file. The second part is the input field itself. In this field, the user has the option to type in the full local URL of the file or he/she may click the browse button to thumb through directory after directory. HTML codes this automatically when we place the type="file" attribute within the input tag. HTML Code:

HTML Text Areas Text areas serve as an input field for viewers to place their own comments onto. Forums and the like use text areas to post what you type onto their site using scripts. For this form, the text area is used as a way to write comments to somebody. Rows and columns need to be specified as attributes to the tag. Rows are roughly 12pixels high, the same as in word programs and the value of the columns reflects how many characters wide the text area will be. i.e. The example below shows a text area 5 rows tall and 20 characters wide. Another attribute to be aware of is the wrap. Wrap has 3 values. <br /> <br /> wrap= o o<br /> <br /> "off" "virtual" 127<br /> <br /> Web Trainings Institute<br /> <br /> o<br /> <br /> "physical"<br /> <br /> Virtual means that the viewer will see the words wrapping as they type their comments, but when the page is submitted to you, the web host, the document sent will not have wrapping words. Physical means that the text will appear both to you, the web host, and the viewer including any page breaks and additional spaces that may be inputed. The words come as they are. Off of course, turns off word wrapping within the text area. One ongoing line. HTML Code: <form method="post" action="mailto:youremail@email.com"> <textarea rows="5" cols="20" wrap="physical" name="comments"> Enter Comments Here Text Area: Enter Comments Here

Email Yourself

Also note that any text placed between the opening and closing textarea tags will show up inside the text area when the browser views it.

128

Web Trainings Institute

JavaScript 



  









  

An Object Based Scripting language similar in Concept to VBScript i.e., it is an interpreted language and supports DOM. Thus use of window, document, form, navigator, location objects is similar to VBScript. Syntax is similar to C language (has less things in common with Java Language). Arrays and Collections use [ ] brackets instead of ( ). Unlike VBScript, JavaScript is case Sensitive. Very popular for Client-side Scripting since both Netscape and Internet Explorer support JavaScript. There are a few versions of this language e.g., Microsoft version is referred to as JScript. There is also a standard version referred to as ECMAScript. JavaScript counts time in milliseconds. The reference point is January 1, 1970. Variables do not need to be declared but it is a good practice to do so. var is used to declare variables (similar to dim in VBScript). Client side Javascript code can be placed in HTML Comments inside a

130

Web Trainings Institute

Client Side Scripting using JSCRIPT

Text Box Attributes and Reading Data From Them

---

 Name:  ID :    



Use \n instead of VbCrLf or Chr(13) to put a string on the next line

Example: if statement, reading data from check boxes, “\n”

Client Side Scripting

Using the Check Box INPUT CONTROL

---

Check the Items You would like in your Dinner

  Salad



132



 Web Trainings Institute



 Rice  Fish  Dessert 

Example: for loop, arrays in Javascript [ ], reading data from option buttons. Use new to create your own arrays e.g., a1 = new Array(20);

Client Side Scripting

Using the Radio Button Control

(Also referred to as Option Button sometimes)

---

Choose a main Dinner Item

 Pizza  Steak  Fish  
 
 Choose a Dessert 
 Chocolate Cake 



134



 Web Trainings Institute



Cheese Cake







Icecream Sundae  
 


onClick="cmdSubmit_OnClick();"> 

Example: “\t”, reading data from a drop down list box.

Client Side Scripting

Reading Data from the Drop Down List Box Control

(SELECT and OPTION Tags are used to create the List Box)

---

Choose a Fruit

   Orange  Apple  Bannana  Mango  
  

136

Web Trainings Institute

Example: Reading data from a list Box.

137

Web Trainings Institute

Client Side Scripting

Reading Data from the List Box Control

(Use SIZE attribute to change a drop down to a List Box)

(Use MULTIPLE attribute to allow multiple Selections)

---

Choose one or more Fruits

   Orange  Apple  Bannana  Mango  
  

138

Web Trainings Institute



Example: Reading data from Text Area. Continuing on multiple lines.

Client Side Scripting

Reading Data from the Text Area

(Text Area behaves like a multiline Text Box)

139

Web Trainings Institute

 Please provide Some Comments about Our Web Site: 
  Comments:  
  
 

Example: Collecting Filename from File control.

Client Side Scripting

Collecting File Name from FILE type INPUT Control

 Click on the Button to Show File Open Dialog: 




 
  
 

Example: Image type Submit button (if placed inside a form).

141

Web Trainings Institute

Client Side Scripting

An Image Type SUBMIT Button

 Click on the Image Button Below to Find its Properties:  
 

142

Web Trainings Institute



Example: Data Validation, parseInt, parseFloat, isNaN functions.

Web Adder with Data Validation

 
 Value1:  Value2:  ----------------Result:   




144



 Web Trainings Institute

Example: Writing your own functions.

Javascript Client Side Scripting

Writing Your own Functions - Finding Minimum

 Enter some numbers in the three text boxes, then Click on the "Calculate Min" Button to Find the minimum of the three numbers entered: 
 Enter First Number :  Enter Second Number :  Enter Third Number : 



 
 

146

Web Trainings Institute



Example: Elements collection in a form, for loop.

Guest Book

What do you think of our Web site? Please provide your comments.

---

 E-mail Address:  Rating:  Excellent  Very Good  Good  Fair



148



 Web Trainings Institute



 Poor General Comments    

Exercise: Convert the Vbscript code in Clientsc18.htm to Javascript. Example: Collecting a response from the user by using the confirm method of the window object. Note that the window object also provides the alert function (which is similar to MsgBox in Vbscript) and the prompt function (similar to InputBox in Vbscript)

Test of the Confirm Function in Javascripts

Click on the Test Button to see Message Box

Example: Use of the prompt function.

150

Web Trainings Institute

Prompt function Example (method of the window object)

Click on the Test Button to see Prompt Box

151

Web Trainings Institute

Javascript String Functions:        

Javascript uses the + operator for catenating strings. Often a new operator is used to create strings (although not required) e.g., s1 = new String(“hello”); toUpperCase( ) converts a string to upper case, toLowerCase( ) converts a string to lower case. If s1 is a string, then s1.substring(2,5) returns a string starting from the third character to the sixth character (remember start position is 0). s1.length( ) reurns the length of the string. pos = s1.indexOf(“hi”,5); will search the s1 string for the string “hi” starting from position 6 and return its location if found. names = new Array(5); creates an array of strings with indices varying from 0 to 4. name = “Bill Gates”; nm = name.split(“ “); will create an array of strings where nm[0] will contain “Bill” and nm[1] = “Gates”.

Example: Use of split function.

Javascript String Function Example

Click on the Button below to do some String Processing

---

JavaScript Date and Time Functions   

JavaScript measures time in milliseconds. The reference point is 1/1/1970. d1 = new Date( ); returns the date and time. Date.parse(“June 25, 1997”) returns a date object with a count of milliseconds since 1/1/1970. 153

Web Trainings Institute

Example:

Test of Javascript Date and Time Functions

Click Button to Invoke Date Time Functions

154

Web Trainings Institute

Example:

Test of JavaScript Date difference Capabilities

---

Please enter your Birthday :

Example: Switch statement, onLoad event, Math functions.

Possibly a A different Color each time the document is loaded or refreshed

157