SQL - Overview             SQL - Syntax SQL - Expressions SQL - Select Database SQL - Insert Query SQL - AND & OR Clau SQL - Like Clause SQL - Group By SQL - Quick Guide

SQL - RDBMS Concepts          SQL - Data Types SQL - Create Database SQL - Create Table SQL - Select Query SQL - Update Query SQL - Top Clause SQL - Distinct Keyword

SQL - Databases              SQL - Operators SQL - Drop Database SQL - Drop Table SQL - Where Clause SQL - Delete Query SQL - Order By SQL - Sorting Results

PHP Introduction

The PHP Hypertext Preprocessor (PHP) is a programming language that allows web developers to create dynamic content that interacts with databases. PHP is basically used for developing web based software applications.  This tutorial helps you to build your base with PHP.

php - what is it?

Taken directly from PHP's home, PHP.net, "PHP is an HTML-embedded scripting language. Much of its syntax is borrowed from C, Java and Perl with a couple of unique PHP-specific features thrown in. The goal of the language is to allow web developers to write dynamically generated pages quickly."

This is generally a good definition of PHP. However, it does contain a lot of terms you may not be used to. Another way to think of PHP is a powerful, behind the scenes scripting language that your visitors won't see!

When someone visits your PHP webpage, your web server processes the PHP code. It then sees which parts it needs to show to visitors(content and pictures) and hides the other stuff(file operations, math calculations, etc.) then translates your PHP into HTML. After the translation into HTML, it sends the webpage to your visitor's web browser.

php - what's it do?

It is also helpful to think of PHP in terms of what it can do for you. PHP will allow you to:

• Reduce the time to create large websites.
• Create a customized user experience for visitors based on information that you have gathered from them.
• Open up thousands of possibilities for online tools. Check out PHP - HotScriptsfor examples of the great things that are possible with PHP.
• Allow creation of shopping carts for e-commerce websites.

what you should know

Before starting this tutorial it is important that you have a basic understanding and experience in the following:

• HTML - Know the syntax and especially HTML Forms.
• Basic programming knowledge - This isn't required, but if you have any traditional programming experience it will make learning PHP a great deal easier.

tutorial overview

This tutorial is aimed at the PHP novice and will teach you PHP from the ground up. If you want a drive-through PHP tutorial this probably is not the right tutorial for you.

Remember, you should not try to plow through this tutorial in one sitting. Read a couple lessons, take a break, then do some more after the information has had some time to sink in.

MySQL

MySQL has been criticized in the past for not supporting all the features of other popular and more expensive DataBase Management Systems. However, MySQL continues to improve with each release (currently version 5), and it has become widely popular with individuals and businesses of many different sizes.

what is a database?

A database is a structure that comes in two flavors: a flat database and a relational database. A relational database is much more oriented to the human mind and is often preferred over the gabble-de-gook flat database that are just stored on hard drives like a text file. MySQL is a relational database.

In a relational structured database there are tables that store data. The columns define which kinds of information will be stored in the table. An individual column must be created for each type of data you wish to store (i.e. Age, Weight, Height).

On the other hand, a row contains the actual values for these specified columns. Each row will have 1 value for each and every column. For example a table with columns (Name, Age, Weight-lbs) could have a row with the values (Bob, 65, 165). If all this relational database talk is too confusing, don't despair. We will talk about and show a few examples in the coming lessons.

why use a database?

Databases are most useful when it comes to storing information that fits into logical categories. For example, say that you wanted to store information of all the employees in a company. With a database you can group different parts of your business into separate tables to help store your information logically. Example tables might be: Employees, Supervisors, and Customers. Each table would then contain columns specific to these three areas. To help store information related to each employee, the Employees table might have the following columns: Hire, Date, Position, Age, and Salary.

learn mysql

Before you begin this tutorial you should have a basic knowledge of the information covered in our PHP and HTML tutorials.

This tutorial focuses heavily on using MySQL in a PHP environment. It is aimed at teaching those who have web hosts with PHP and MySQL already installed. If you are unsure, please contact your web host.

MySQL Useful Resources

MySQL and PERL - Its a tutorial from Tutorials Point which explains you how to use MySQL along with PERL and DBI module. Here you will learn all the required MySQL operations alongwith examples.

MySQL Official Website - Here you can download the latest MySQL release, get the MySQL news update. The mailing list is also a great resources for anyone who want to build dynamic websites using MySQL.

PHP Official Website: - A complete resource for PHP stuff. Starting from latest PHP updates to latest function manual is available at this site.

MySQL at Wikipedia - A small article on MySQL, worth to go through it.

MySQL Reference Manual - A complete official reference for MySQL.

MySQL-sr-lib - MySQL General Purpose Stored Routines Library. This is a A repository of ready to use routines for everyday needs. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License.

Understanding SQL - If you are new to SQL then go through this tutorial to understand SQL basics. SQL, the Structured Query Language, is a mature, powerful, and versatile relational query language. 

Here is the list of all important MySQL functions. Each function has been explained along with suitable example.

• MySQL Group By Clause - The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
• MySQL IN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
• MySQL BETWEEN Clause - This is a clause which can be used alongwith any MySQL query to specify a condition.
• MySQL UNION Keyword - Use a UNION operation to combine multiple result sets into one.
• MySQL COUNT Function - The MySQL COUNT aggregate function is used to count the number of rows in a database table.
• MySQL MAX Function - The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
• MySQL MIN Function - The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
• MySQL AVG Function - The MySQL AVG aggregate function selects the average value for certain table column.
• MySQL SUM Function - The MySQL SUM aggregate function allows selecting the total for a numeric column.
• MySQL SQRT Functions - This is used to generate a square root of a given number.
• MySQL RAND Function - This is used to generate a random number using MySQL command.
• MySQL CONCAT Function - This is used to concatenate any string inside any MySQL command.
• MySQL DATE and Time Functions - Complete list of MySQL Date and Time related functions.
• MySQL Numeric Functions - Complete list of MySQL functions required to manipulate numbers in MySQL.
• MySQL String Functions - Complete list of MySQL functions required to manipulate strings in MySQL.

There are two simple ways in MySQL to load data into MySQL database from a previously backed up file.

Importing Data with LOAD DATA:

MySQL provides a LOAD DATA statement that acts as a bulk data loader. Here's an example statement that reads a file dump.txt from your current directory and loads it into the table mytbl in the current database:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

• 
  
  If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using looking into absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.
• 
  
  By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.
• 
  
  To specify a file format explicitly, use a FIELDS clause to describe the characteristics of fields within a line, and a LINES clause to specify the line-ending sequence. The following LOAD DATA statement specifies that the datafile contains values separated by colons and lines terminated by carriage returns and new line character:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl -> FIELDS TERMINATED BY ':' -> LINES TERMINATED BY '\r\n';

• 
  
  LOAD DATA assumes the columns in the datafile have the same order as the columns in the table. If that's not true, you can specify a list to indicate which table columns the datafile columns should be loaded into. Suppose your table has columns a, b, and c, but successive columns in the datafile correspond to columns b, c, and a. You can load the file like this:

mysql> LOAD DATA LOCAL INFILE 'dump.txt' -> INTO TABLE mytbl (b, c, a);

Importing Data with mysqlimport

MySQL also includes a utility program named mysqlimport that acts as a wrapper around LOAD DATA so that you can load input files directly from the command line.

To load a data from dump.txt into mytbl use following command at UNIX prompt.

$ mysqlimport -u root -p --local database_name dump.txt password *****

If you use mysqlimport, command-line options provide the format specifiers. mysqlimport commands that correspond to the preceding two LOAD DATA statements look like this:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \ --lines-terminated-by="\r\n" database_name dump.txt password *****

The order in which you specify the options doesn't matter for mysqlimport, except that they should all precede the database name.

The mysqlimport statement uses the --columns option to specify the column order:

$ mysqlimport -u root -p --local --columns=b,c,a \ database_name dump.txt password *****

Handling Quotes and Special Characters:

The FIELDS clause can specify other format options besides TERMINATED BY. By default, LOAD DATA assumes that values are unquoted, and interprets the backslash (\) as an escape character for special characters. To indicate the value quoting character explicitly, use ENCLOSED BY; MySQL will strip that character from the ends of data values during input processing. To change the default escape character, use ESCAPED BY.

When you specify ENCLOSED BY to indicate that quote characters should be stripped from data values, it's possible to include the quote character literally within data values by doubling it or by preceding it with the escape character. For example, if the quote and escape characters are " and \, the input value "a""b\"c" will be interpreted as a"b"c.

For mysqlimport, the corresponding command-line options for specifying quote and escape values are --fields-enclosed-by and --fields-escaped-by

The simplest way of exporting a table data into a text file is using SELECT...INTO OUTFILE statement that exports a query result directly into a file on the server host.

Exporting Data with the SELECT ... INTO OUTFILE Statement:

The syntax for this statement combines a regular SELECT with INTO OUTFILEfilename at the end. The default output format is the same as for LOAD DATA, so the following statement exports the tutorials_tbl table into /tmp/tutorials.txt as a tab-delimited, linefeed-terminated file:

mysql> SELECT * FROM tutorials_tbl -> INTO OUTFILE '/tmp/tutorials.txt';

You can change the output format using options to indicate how to quote and delimit columns and records. To export the tutorial_tbl table in CSV format with CRLF-terminated lines, use this statement:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt' -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\r\n';

The SELECT ... INTO OUTFILE has the following properties:

• The output file is created directly by the MySQL server, so the filename should indicate where you want the file to be written on the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
• You must have the MySQL FILE privilege to execute the SELECT ... INTO statement.
• The output file must not already exist. This prevents MySQL from clobbering files that may be important.
• You should have a login account on the server host or some way to retrieve the file from that host. Otherwise, SELECT ... INTO OUTFILE likely will be of no value to you.
• Under Unix, the file is created world readable and is owned by the MySQL server. This means that although you'll be able to read the file, you may not be able to delete it.

Exporting Tables as Raw Data:

The mysqldump program is used to copy or back up tables and databases. It can write table output either as a raw datafile, or as a set of INSERT statements that recreate the records in the table.

To dump a table as a datafile, you must specify a --tab option that indicates the directory where you want the MySQL server to write the file.

For example, to dump the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory, use a command like this:

$ mysqldump -u root -p --no-create-info \ --tab=/tmp TUTORIALS tutorials_tbl password ******

Exporting Table Contents or Definitions in SQL Format:

To export a table in SQL format to a file, use a command like this:

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt password ******

This will create file having content as follows:

---

-- MySQL dump 8.23 -- -- Host: localhost Database: TUTORIALS --------------------------------------------------------- -- Server version 3.23.58 -- -- Table structure for table `tutorials_tbl` -- CREATE TABLE tutorials_tbl ( tutorial_id int(11) NOT NULL auto_increment, tutorial_title varchar(100) NOT NULL default '', tutorial_author varchar(40) NOT NULL default '', submission_date date default NULL, PRIMARY KEY (tutorial_id), UNIQUE KEY AUTHOR_INDEX (tutorial_author) ) TYPE=MyISAM; -- -- Dumping data for table `tutorials_tbl` -- INSERT INTO tutorials_tbl VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO tutorials_tbl VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO tutorials_tbl VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

To dump multiple tables, name them all following the database name argument. To dump an entire database, don't name any tables after the database as follows:

$ mysqldump -u root -p TUTORIALS > database_dump.txt password ******

To backup all the databases available on your host use the following:

$ mysqldump -u root -p --all-databases > database_dump.txt password ******

The --all-databases option is available as of MySQL 3.23.12.

These method can be used to implement a database backup stretegy.

Copying Tables or Databases to Another Host:

If you want to copy tables or databases from one MySQL server to another then use mysqldump with database name and table name.

Run the following command at source host. This will dump complete database into dump.txt file:

$ mysqldump -u root -p database_name table_name > dump.txt password *****

You can copy complete database without using a particular table name as explained above.

Now ftp dump.txt file on another host and use the following command. Before running this command, make sure you have created database_name on destination server.

$ mysql -u root -p database_name < dump.txt password *****

Another way to accomplish this without using an intermediary file is to send the output of mysqldump directly over the network to the remote MySQL server. If you can connect to both servers from the host where the cookbook database resides, use this command:

$ mysqldump -u root -p database_name \ states | mysql -h other-host.com database_name

The mysqldump half of the command connects to the local server and writes the dump output to the pipe. The mysql half of the command connects to the remote MySQL server on otherhost.com. It reads the pipe for input and sends each statement to the other-host.com server.

If you take user input through a webpage and insert it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.

Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the example below, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - modify these rules as needed.

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) { $result = mysql_query("SELECT * FROM users WHERE username=$matches[0]"); } else { echo "username not accepted"; }

To demonstrate the problem, consider this excerpt:

// supposed input $name = "Qadir'; DELETE FROM users;"; mysql_query("SELECT * FROM users WHERE name='{$name}'");

The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster: the injected DELETE query removes all records from users.

Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple queries in a single function call. If you try to stack queries, the call fails.

However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.

Preventing SQL Injection:

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysql_query("SELECT * FROM users WHERE name='{$name}'");

The LIKE Quandary:

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let's you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_"); // $sub == \%something\_ mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

Tables or result sets sometimes contain duplicate records. Sometime it is allowed but sometime it is required to stop duplicate records. Sometime it is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent duplicate records occurring in a table and how to remove already existing duplicate records.

Preventing Duplicates from Occurring in a Table:

You can use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. Lets take one example, The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name

CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) );

To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it's also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values:

CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) PRIMARY KEY (last_name, first_name) );

The presence of a unique index in a table normally causes an error to occur if you insert a record into the table that duplicates an existing record in the column or columns that define the index.

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

Following example does not error out and same time it will not insert duplicate records.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 1 row affected (0.00 sec) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 0 rows affected (0.00 sec)

Use REPLACE rather than INSERT. If the record is new, it's inserted just as with INSERT. If it's a duplicate, the new record replaces the old one:

mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE and REPLACE should be chosen according to the duplicate-handling behavior you want to effect. INSERT IGNORE keeps the first of a set of duplicated records and discards the rest. REPLACE keeps the last of a set of duplicates and erase out any earlier ones.

Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.

CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name) );

Counting and Identifying Duplicates:

Following is the query to count duplicate records with first_name and last_name in a table.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions > 1;

This query will return a list of all the duplicate records in person_tbl table. In general, to identify sets of values that are duplicated, do the following:

• Determine which columns contain the values that may be duplicated.
• List those columns in the column selection list, along with COUNT(*).
• List the columns in the GROUP BY clause as well.
• Add a HAVING clause that eliminates unique values by requiring group counts to be greater than one.

Eliminating Duplicates from a Query Result:

You can use DISTINCT along with SELECT statement to find out unique records available in a table.

mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name;

An alternative to DISTINCT is to add a GROUP BY clause that names the columns you're selecting. This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns:

mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);

Removing Duplicates Using Table Replacement:

If you have duplicate records in a table and you want to remove all the duplicate records from that table then here is the procedure.

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex -> FROM person_tbl; -> GROUP BY (last_name, first_name); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;

An easy way of removing duplicate records from a table is that add an INDEX or PRIMAY KEY to that table. Even if this table is already available you can use this technique to remove duplicate records and you will be safe in future as well.

mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);