PHP Tutorial

Introduction

2009-03-13T22:37:00.001-07:00

PHP is a language that was designed to be easily embedded into HTML pages. Most PHP pages have PHP code and HTML intermixed. When a Web server reads a PHP page, it is looking for two things to let it know it should start reading the page as PHP rather than HTML, the start and end PHP tags: <?php and ?>, respectively.

If you have configured your php.ini file to accept "short tags" (which are enabled by default), then you can use the syntax <? and ?> instead. Additionally, you can configure your php.ini file so that it accepts ASP style tags, <% and %>. This feature is turned off by default, and its only real purpose seems to be to allow certain HTML editors to recognize the in-between code as something other than HTML, in which case the editor won't mangle the code by imposing its own set of HTML syntax rules upon the code.

Get Started:

The code above, when viewed via a Web server, simply prints out String enclosed in the quotes.

In general, individual lines of PHP code should end with a semicolon, although it is not necessary to use semicolons if a beginning or an ending bracket is used (this will make sense when you look at if/then statements).

For example:

echo "<p>a line of code";

echo "<p>another line of code;

Variables

2009-03-13T22:36:00.001-07:00

Variables in PHP are denoted by the "$". To assign the value "Hello World" to the variable $str, you simply call it in your code:

$str = "Hello World";

Strings must be enclosed by quotes, but they can contain variables themselves:

$a = 12;

$string = "The value of a is $a";

// $string = "The Value of a is 12";

PHP variables do not have to be declared ahead of time, nor do they require a type definition. Note that you may get a warning about using undeclared variables if you try to use them before giving them a value (depending on how you set up error reporting in php.ini). For example:

$a = 4;

$c = $a + $b;

// $c = 4, but a warning appears "Warning: Undefined variable..".

Warnings do not stop a script from continuing. If you forgot to add a semicolon at the end of one of the lines, then you would get a Parser error, which prohibits the script from running.

Since PHP variables are not typed, you don't have to worry about performing mathematical equations on the wrong type, as you might in C. For example:

$a = 4;

$b = "5";

$c = $a + $b;

// $c = 9;

PHP also supports boolean variables, which can be assigned either a one or a zero, or the words true or false:

$a = true;

$b = 1;

//$a = $b

$c = false;

$d = 0;

//$c = $d

Variable Naming Rules

A variable name must start with a letter or an underscore.
A variable name can only contain alpha-numeric characters and underscores (a-Z, 0-9, and _ )

A variable name should not contain spaces. If a variable name is more than one word, it should be separated with underscore ($my_string), or with capitalization ($myString)

Comments:

You can include comments in your PHP scripts. Comments are ignored by the Web server, and any comments contained within the PHP code are not sent to a browser. There are three forms of comments:

# Used just like it is used in PERL; comments out the remainder of the line after the # symbol.

// Used just like it is in JavaScript; comments out the remainder of the line after the // symbols.

/* and */— Comments out anything in between the two sets of symbols. This is the same syntax used in C to comment code.

Examples of comments in PHP code:

echo "Hello"; #prints out "Hello"

echo "Hello"; //prints out "Hello"

/* The following

prints out "Hello" */

echo "Hello";

You will often see comments denoted using the // characters or the /* and */ characters. The # character is rarely seen, although it is still valid.

PHP Strings

2009-03-13T22:35:00.000-07:00

String variables are used to store and manipulate a piece of text.

<?php

$msg="Hello World";

echo $msg;

The output will be

Hello World

The Concatenation Operator

Dot (.) is used as concatenation operator. To concatenate two variables together, use the dot (.) operator:

<?php

$str1="Hello World";

$str2="Adam";

echo $ str1. " " . $ str2;

?>

Hello World Adam

Length of a String:
The strlen() function is used to find the length of a string.

<?php

$string=”Hello World”;

echo strlen($string);

Output will be

The strpos() function:

The strpos() function is used to search for a string or character within another string.
If the string is found in the string, this function will return the position of the first match. If no match is found, it will return FALSE.

<?php

echo strpos("Example of strops function","of");

?>

The output will be

Operators

2009-03-13T22:34:00.000-07:00

Operators are used to manipulate or perform operations on variables and values. There are many operators used in PHP, we have separated them into the following categories.

Assignment Operators

Arithmetic Operators

Comparison Operators

Logical Operators

Assignment Operators:
Assignment operators are used to set a variable equal to a value or set a variable to another variable's value. ‘=’ is used for assignment operator.

Example:$var = 23;
$var2 = $var;

Operator	Example	Is The Same As
=	a=b	a=b
+=	a+=b	a=a+b
-=	a-=b	a=a-b
*=	a*=b	a=a*b
/=	a/=b	a=a/b
.=	a.=b	a=a.b
%=	a%=b	a=a%b

Arithmetic Operators:

Operator	Function
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulus (division remainder)
++	Increment
--	Decrement

Comparison Operators:

Operator	Function	Example	Result
==	Equal To	$x == $y	false
!=	Not Equal To	$x != $y	true
<	Less Than	$x < $y	true
>	Greater Than	$x > $y	false
<=	Less Than or Equal To	$x <= $y	true
>=	Greater Than or Equal To	$x >= $y	false

Logical Operators:

Operator	Function	Example
&&	and	x=8 y=4 (x < 10 && y > 1) returns true
\|\|	or	x=12 y=8 (x>=5 \|\| y<=5) returns true
!	not	x=6 y=3 !(x==y) returns true

If…Else Statement

2009-03-13T22:33:00.000-07:00

One of the most common PHP language constructs that you will encounter is the if…then statement. The if…then statement allows you to evaluate an expression and then, depending if it is true or not, take a course of action. For example:

$x = 1;

if($x) {

echo "True!";

}

Since $x = 1, then PHP interprets it in the context of the if statement to be a boolean type. Since 1 = true, the if statements prints out the message. Literally you can read it as "If True, then print out "True!".

Another example, but this time with an "else" clause:

$a = 5;

$b = "10";

if($a > $b) {

echo "$a is greater than $b";

} else {

echo "$a is not greater than $b";

}

PHP doesn't care that $a is an integer and $b is a string. It recognizes that $b also makes a pretty good integer as well. It then evaluates the expression, "If $a is greater than $b then print out that $a is greater than $b; if not (else) then print out $a is not greater than $b." It's also important to note that you don't say, "$a is less than $b," since it is possible that $a could be equal to $b. If that is the case, then the second part of the expression, the else statement, is the part that runs.

Note the use of brackets to enclose the actions. Also note the lack of semicolons where the brackets are used.

One final example is the if…elseif…else statement:

if($a == $b) {

// do something

} elseif ($a > $b) {

// do something else

} elseif($a < $b) {

// do yet something else

} else {

// if nothing else we do this...

}

Switch Statement:

The Switch statement is used to perform one of several different actions based on one of several different conditions. Switch statements allow for additional evaluations of the data, even though one of the cases may have been met. Switch statements can also save you from typing many if…elseif…elseif… statements.

Syntax:

switch (expression)

{

case label1:

Statement(s);

break;

case label2:

Statement(s);

break;

default:

Statement(s);

}

Example:

$a = "100";

switch($a) {

case(10):

echo "The value is 10";

break;

case (100):

echo "The value is 100<br>";

case (1000):

echo "The value is 1000";

break;

default:

echo "<p>Wrong Input";

}

switch statements have four basic parts:

The switch— The switch identifies what value or expression is going to be evaluated in each of the cases. In the example above, you tell the switch statement that you are going to evaluate the variable $a in the subsequent cases.

The case— The case statement evaluates the variable you passed from the switch command. You can use case() the same way you'd use an if statement. If the case holds true, then everything after the case statement is executed, until the parser encounters a break command, at which point execution stops and the switch is exited.

Breakpoints— Defined by the break command, exit the parser from the switch. Breakpoints are normally put after statements executed when a case() is met.

Default— The default is a special kind of case. It is executed if none of the other case statements in the switch have been executed.

PHP Loops

2009-03-13T22:32:00.000-07:00

Loops are used to repeat statement or block of statement more than one time. The idea of a loop is to do something over and over again until the task has been completed.

In PHP we have the following looping statements:

For Loop:

For loops are useful constructions to loop through a finite set of data, such as data in an array.

Syntax:

for ( initialization; conditional statement; increment/decrement){

statement(s);

}

Initialization—You can pass in an already assigned variable or assign a value to the variable in the for statement.

The condition required to continue the for loop— The statements are executed until the given condition goes false.

Statement to modify the counter on each pass of the loop.

Example:
<?php

for ($i=1; $i<=10; $i++)

{

echo "Hello World<br />";

}

Foreach Loop:

Foreach loops allow you to quickly traverse through an array.

$array = array("name" => "Jet", "occupation" => "Bounty Hunter" );

foreach ($array as $val) {

echo "<P>$val";

}

Prints out:

<P>Jet

<P>Bounty Hunter

You can also use foreach loops to get the key of the values in the array:

foreach ($array as $key => $val) {

echo "<P>$key : $val";

}

Prints out:

<P>name : Jet

<P>occupation : Bounty Hunter

While Loop:

While loops are another useful construct to loop through data. While loops continue to loop until the expression in the while loop evaluates to false. A common use of the while loop is to return the rows in an array from a result set. The while loop continues to execute until all of the rows have been returned from the result set.

Syntax:
while (condition){

statement(s);

}

Example:

<?php

$i=1;

while($i<=9)

{

echo "The number is " . $i . "<br />";

$i++;

}

Do-While Loop:

Do while loops are another useful loop construct. Do while loops work exactly the same as normal while loops, except that the script evaluates the while expression after the loop has completed, instead of before the loop executes, as in a normal while loop.

The important difference between a do while loop and a normal while loop is that a do while loop is always executed at least once. A normal while loop may not be executed at all, depending on the expression. The following do while loop is executed one time, printing out $i to the screen:

$i = 0;

do {

print $i;

} while ($i>0);

Whereas the following is not executed at all:

$i = 0;

while($i > 0) {

print $i;

}

Arrays:

An array is a data structure that stores one or more values in a single value. PHP supports both numerical arrays (array items are indexed by their numerical order) as well as associative arrays (array items are indexed by named keys).

Example:

$a = array(1, 2, 3, 4);

//$a[0] = 1

//$a[1] = 2

//$a[2] = 3

//$a[3] = 4

$b = array("name"=>"Fred", "age" => 30);

//$b['name'] = "Fred"

//$b['age'] = 30

PHP Functions

2009-03-13T22:31:00.000-07:00

In addition to PHP's built-in functions, you can create your own functions. Remember that if you want to use variables that exist outside of the function, then you must declare them as global variables or assign them as arguments to the function itself.

function check_age($age) {

if ($age > 21) {

return 1;

} else {

return 0;

}

//usage:

if(check_age($age)) {

echo "You may enter!";

} else {

echo "Access Not Allowed!";

exit();

}

The function would be called from within your script at the appropriate time, using the following syntax:

$age = 10;

check_age($age);
Example:
function simpletable($text) {

print("<table border=0 cellpadding=0 cellspacing=0><tr>");

print("<td>$text</td>");

print("</tr></table>");

}

phpinfo( )

phpinfo() is a very useful function that allows you to see what version of PHP is running on your Web server, as well as all of the specific settings that are enabled in that version.

phpinfo();

PHP Forms

2009-03-13T22:30:00.000-07:00

Forms are the most fundamental method of interaction for your users. Users must use a form to enter information into a site. Think about it, every bulletin board, shopping cart, feedback form, and poll is a type of form. Without forms, the Web is nothing more than a publishing medium for those who can FTP Web pages up to a server.
GET & POST Methods:

There are two methods that you can use when creating a form in HTML. They are post and get, as in:

or:

If you don't specify a method, then the Web server assumes that you are using the get method. So what's the deal? They do the same thing right? Well, almost. You may have noticed that the URL looks a lot longer after you submit a form that uses the get method. For example, you may see something like:

http://studiesinn.com/form.php?name=fred&age=20&comments=This+site+rocks

That's because the get method puts the contents of the form right in the URL. There are a few disadvantages to this. First, depending on your Web server's operating system and software, there is a limit to how much data you can send through a form using the get method. On many systems, this limit is 256 characters. Also, the individual get queries may be stored in your Web server logs. If you are using space on a shared server, then other people may be able to decipher data sent from your forms that use the get method.

The post method was created to correct the inadequacies of the get method. The information sent using the post method is not visible in the URL, and form data cannot be deciphered by looking in the Web server logs. There also isn't such a small limit on the amount of data that can be sent from a form. Again, it depends on your server, but you probably won't ever hit the limit of sending data using the post method for a text-based form.

I use the post method for my scripts unless I need to debug something. When you need to debug something on a form, it is easy enough to switch to the get method (by changing the action line in your script) and then check the URL after you submit your buggy form. You can usually pick up typos and such with a quick look.

Example:

<form action="display.php" method="get">

Name: <input type="text" name="name" />

Age: <input type="text" name="age" />

<input type="submit" />

</form>

When you click the "Submit" button on the form the URL sent could look something like this:

http://www.studiesinn.com/display.php?name=adam&age=22

On display.php page you can retrieve the value by using the following code

Welcome <?php echo $_GET["name"]; ?>.<br />

You are <?php echo $_GET["age"]; ?> years old!

The $_REQUEST Variable:

The PHP $_REQUEST variable can be used as the replacement of $_GET, $_POST, and $_COOKIE. The $_REQUEST variable can be used to get the result from form data sent with both the GET and POST methods.

Example:

<form action="display.php" method="post">

Enter your name: <input type="text" name="name" />

Enter your age: <input type="text" name="age" />

<input type="submit" />

</form>

When you click the Submit button on the form, following URL will be sent.

http://www.studiesinn.com/display.php

On display.php page you can retrieve the value as

Welcome <?php echo $_POST["name"]; ?>.<br />

You are <?php echo $_POST["age"]; ?> years old!

PHP Sessions

2009-03-13T22:29:00.000-07:00

You cannot track variables across a user session unless you start the session on each page on which you want to use or alter those variables. Starting a session uses the session_start() function:

session_start();

session_start() takes no arguments. If you are starting a new session, then the function initializes the session and creates the necessary temp files to track the session. If a $PHPSESSID is found by the function, either by a cookie or a GET variable, then the function resumes the current session and the page has access to any variables that have been registered to the session.

Once you have started the session, you need to register some variables with it. The session will not track variables until they have been registered using the session_register() function:

session_register(STRING);

The STRING argument to session_register() should be the name of the variable that you want to register with the session so that it may be accessed across any session-enabled pages.

Once you have started the session and registered one or more variables, you can use those variables across any session enabled pages on your site.

Example:

session_start();

if(!isset($count)) {

session_register("count");

$count = 1;

}

You have been to this page <?=$count?> times.

$count++;

?>

Destroying Sessions:

There are two ways to combat the buildup of files in your temporary directory that will not, in most cases, adversely affect your users' sessions.

The first is to make use of the session_destroy() function:

session_destroy();

session_destroy() takes no arguments. session_destroy() unregisters all session variables associated with the user session and removes any session files created by the session. Remember that even if a variable is unregistered with a session, the variable still exists with its value intact on the current page.

PHP Cookies

2009-03-13T22:28:00.000-07:00

A cookie is a small bit of information stored on a viewer's computer by his or her web browser by request from a web page. The information is constantly passed in HTTP headers between the browser and web server; the browser sends the current cookie as part of its request to the server and the server sends updates to the data back to the user as part of its response. In addition to the information it stores, each cookie has a set of attributes: an expiration date, a valid domain, a valid domain path and an optional security flag. These attributes help ensure the browser sends the correct cookie when a request is made to a server.

Syntax:

setcookie(name, value, expire, path, domain);

Example:

<?php

setcookie("c_name", "Adam", time()+60);

?>

Retrieve a Cookie:
The $_COOKIE variable is used to retrieve a cookie.

<?php

echo $_COOKIE["user"];

PHP Files

2009-03-13T22:27:00.000-07:00

PHP is a very useful language for working with files. Although it may not be as robust as other languages, such as PERL, when it comes to parsing files, PHP still provides great power and flexibility when it comes to working with text files.

Opening a File:
Before you can use PHP to read a file, you must first open it. To open a file, you use the fopen() function.

$file_handler = fopen($file, $mode)

You must assign the function to a variable, as above. This is referred to as a file pointer. You use the file pointer to reference the open file throughout your script.

The fopen() function takes two arguments:

$file is the name (and path, if required) of the file.

$mode is one of a list of available modes:

1. r— Open the file as read-only. You cannot write anything to the file. Reading begins at the beginning of the file.

2. r+ — Open the file for reading and writing. Reading and writing begin at the beginning of the file. You will delete existing contents if you write to the file without first moving the internal pointer to the end of the file.

3. w— Open the file for write-only. You cannot read data from the file. Writing begins at the beginning of the file. You will again delete contents if you write to the file without first moving the pointer to the end of the file. If the file specified by the $fie argument does not exist, then PHP attempts to create the file. Make sure your permissions are set correctly!

4. w+ — As above, but the file may also be read.

5. a— This mode is the same as the 'w' mode, with the exception that the internal pointer is placed at the end of the file. Existing contents will not be overwritten unless you rewind the internal pointer to the beginning of the file.

6. a+ — As above, but the file may also be read.

Additionally, if the file is a binary file, you must include a "b" in the mode if your OS is Windows. This chapter only deals with ASCII text files, so you will not require the "b: mode in the following example. If you do need to open a binary file, an example is below:

$file_handle = fopen($filename, "rb+")

Note the differences between the "w" and "a" modes. Using "w" to open a file effectively deletes the contents of the file, while using the "a" mode retains the contents of the file and allows you to append additional data to the end of the file.

Once you have the file opened, you can then read from or write to the file.

Reading a File:There are mult

E-mail

2009-03-13T22:26:00.000-07:00

mail() function is used to send emails from inside a script in php. The Syntax of mail() function is as under.mail(to,subject,message,headers,parameters)

Parameter	Description
to	Required. Specifies the receivers email
subject	Required. Specifies the subject of the email
message	Required. Contain the message to be sent. Each line should be separated with a Line Fed (\n). Lines should not exceed 70 characters
headers	Optional. Specifies additional headers, like From, Cc, and Bcc. The additional headers should be separated with a CRLF (\r\n)
parameters	Optional. Specifies an additional parameter to the sendmail program.

Example:

<?php
$to = 'noname@testing.com';

$subject = 'E-mail subject';

$message = 'E-mail message';

$headers = 'From: webmaster@studiesinn.com' . "\r\n" .

'Reply-To: webmaster@ webmaster.com' . "\r\n" .

'X-Mailer: PHP/' . phpversion();

mail($to, $subject, $message, $headers);

?>

Error Handling

2009-03-13T22:23:00.000-07:00

Error handling is the process of changing the control flow of a program in response to error conditions. Error conditions can be caused by a variety of factors - programmer error, corrupt input data, software requirements deficiencies and in many other conditions.
A web application needs to be able to gracefully handle all of these potential problems - recovering from them where possible and exiting gracefully when the error is fatal.

We will show different error handling methods:

Simple "die()" statements

Custom errors and error triggers

Error reporting

Simple die() statement:
<?php

if(!file_exists("myfile.txt"))

{

die("File not found");

}

else

{

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

}

If the file does not exist you get an error message like this:

File not found

Custom errors and error triggers

You can create a custom error handler in PHP to replace the standard output to the browser. First you set up the error handler to replace the normal one, and set the ini values.

ini_set('display_errors', 'Off');

ini_set('log_errors', 'On');

define('ERROR_LOG_PATH', 'error_log.txt');

set_error_handler("custom_err_handler");

The custom function is defined we must then create it. The function takes 4 arguments.

$num: level of the error raised
$str: contains the error message
$file: filename that the error was raised in
$line: line number the error was raised at

function custom_err_handler($num, $str, $file, $line) {

print "Error Handler Called!";

$err = "";

$err .= "PHP Error\n";

$err .= "Number: [" . $num . "]\n";

$err .= "String: [" . $str . "]\n";

$err .= "File: [" . $file . "]\n";

$err .= "Line: [" . $line . "]\n\n";

error_log($err, 3, ERROR_LOG_PATH);

}

function Errfunction() {

trigger_error("errror message");

}

Now call the Errfunction()

myFunction();

File Upload

2009-03-13T22:22:00.000-07:00

You must first build an HTML form that lets users select a file to upload.

<form enctype="multipart/form-data" action="uploadfile.php" method="POST">

Select file to upload: <input name="file" type="file" /><br />

<input type="submit" value="Upload File" />

</form>

uploadfile.php code:

$target_path = "myfiles/";

$target_path = $target_path . basename( $_FILES[‘file’]['name']);

if(move_uploaded_file($_FILES[‘file’]['tmp_name'], $target_path)) {

echo "The file ". basename( $_FILES[‘file’]['name'])." has been uploaded";

} else{

echo "Error Uploading File, Please try again.";

}

$_FILES['file']['name']
It contains the original path of the user uploaded file.

$_FILES['file']['tmp_name']

It contains the path to the temporary file that resides on the server. The file should exist on the server in a temporary directory with a temporary name.

The include() Function:

The include() function takes all the text in a specified file and copies it into the file that uses the include function.

Example:

<html>
<body>
<?php include("header.php"); ?>
<h1>Welcome to The Home Page</h1>
</body>
</html>

PHP Databases

2009-03-13T22:21:00.000-07:00

A database is a collection of data that is stored independently of the manner in which you collect it or may wish to retrieve it. It is organized for efficient storage and retrieval, based on the nature of the data, rather than the collection or retrieval methods.
What is MySQL
MySQL is an open source relational database management system (RDBMS)that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. It is a Database Management System which is available for both Linux and Windows.

In a database, there are tables. Database tables contain rows, columns, and cells. Tables are used to organize and group your data by common characteristics or principles.
Database Query:

Database Query is a form generator and query manager that works with any database. Database Query automatically creates forms for selecting, updating, inserting, or deleting data in MySQL, Oracle, DB2, or any other database. With MySQL, we can query a database for specific information and have a recordset returned.

Connecting to a MySQL Database

2009-03-13T22:20:00.000-07:00

In order to pull information from a MySQL database to your php pages, you need to connect to the database first.
Syntax:
mysql_connect(servername,username,password);
servername: It specifies the server to connect to. Default value is "localhost:3306"
username: It specifies the username to log in with. Default value is the name of the user that owns the server process
password: It specifies the password to login. Default is ""

Note:
To execute the SQL statements we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

Example:
<?php
$server = "localhost";
$user = "";
$pass = "";
$db = "mydb";

$conn = mysql_connect("$host", "$user", "$pass") or die ("Unable to connect.");
mysql_select_db("$db", $conn);

?>
Create a Database

PHP also provide a function to create MySQL database, mysql_create_db().

$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$query = "CREATE DATABASE mydb";
$result = mysql_query($query,$con);
?>

Create a Table

CREATE TABLE statement is used to create a database table in MySQL database.

$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydb", $con);
mysql_query("CREATE TABLE emp_info(
emp_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),Emp_name VARCHAR(30),
Emp_age INT)")
or die(mysql_error());
echo "Table Created";

MySQL Data Types

2009-03-13T22:15:00.001-07:00

Numeric Data Types:

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string, allowing for a fixed decimal point.

TEXT TYPES

CHAR( )	Fixed section from 0 to 255 characters long.
VARCHAR( )	Variable section from 0 to 255 characters long.
TINYTEXT	String with a maximum length of 255 characters.
TEXT	String with a maximum length of 65535 characters.
BLOB	String with a maximum length of 65535 characters.
MEDIUMTEXT	String with a maximum length of 16777215 characters.
MEDIUMBLOB	String with a maximum length of 16777215 characters.
LONGTEXT	String with a maximum length of 294967295 characters.
LONGBLOB	String with a maximum length of 4294967295 characters.

Date Data Types:

DATE	YYYY-MM-DD.
DATETIME	YYYY-MM-DD H:MM:SS.
TIMESTAMP	YYYYMMDDHHMMSS.
TIME	HH:MM:SS.

MISC Types

ENUM ( )	ENUMERATION means that each column may have one of specified possible values.
SET	SET is similar to ENUM. However, SET can have up to 64 list items and it can store more than one choice

Inserting Data Into a Table

2009-03-13T22:14:00.001-07:00

The INSERT INTO SQL statement impregnates our table with data. Here is a general form of INSERT.

INSERT INTO table_name (column1, column2....) VALUES (value1, value2,....)

<?php
$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("mydb", $con);
mysql_query("INSERT INTO empinfo (FirstName, LastName, Age)
VALUES ('Adam', 'John', '22')");
mysql_close($con);
?>

Select Statement

2009-03-13T22:12:00.000-07:00

The select query is used to retrieve records from a database.

SELECT	Retrieves fields from one or more tables.
FROM	Tables containing the fields.
WHERE	Criteria to restrict the records returned.
GROUP BY	Determines how the records should be grouped.
HAVING	Used with GROUP BY to specify the criteria for the grouped records.
ORDER BY	Criteria for ordering the records.
LIMIT	Limit the number of records returned.

Syntax:

SELECT column_name(s)
FROM table_name
Example:

<?php
$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mydb", $con);
$result = mysql_query("SELECT * FROM empinfo");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'] . “ “ $row[‘Age’];
echo "<br />";
}
mysql_close($con);
?>

Returned data from mysql_query() function will be stored in the result variable. msql_fetch_array() function is used to return the first row from the recordset as an array. The while loop loops through all the records in the recordset and retrieve all the records from the recordset.

Following function can be used in the select statement.

Function	Description
AVG()	Returns the average value in a group of records.
COUNT()	Returns the number of records in a group of records
MAX()	Returns the largest value in a group of records.
MIN()	Returns the lowest value in a group of records
SUM()	Returns the sum of a field.

Display the Result in an HTML Table

<?php
$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());

}
mysql_select_db("mydb", $con);

result = mysql_query("SELECT * FROM empinfo");

echo "<table border='1' align=’center’>

<tr>

<td><b>Firstname</b></td>

<td><b>Lastname</b></td>

<td><b>Age</b></td>

</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>";

echo "<td>" . $row['LastName'] . "</td>";

echo "<td>" . $row['Age'] . "</td>";

echo "</tr>";

}
echo "</table>";
mysql_close($con);

Where Clause

2009-03-13T22:11:00.000-07:00

In Select Query we use WHERE clause to select only data that matches a specified criteria.

Syntax
SELECT column(s) FROM tablename WHERE column operator value
Example:
Table Name(empinfo)

FirstName	LastName	Email	DOB
Steve	Martin	smart.steve@studiesinn.com	08-04-1988
David	Fernandas	davidfernandas@studiesinn.com	04-16-1977
Ajay	Rathor	ajayrathor@studiesinn.com	08-24-1986

<?php
$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydb", $con);
$result = mysql_query("SELECT * FROM empinfo where FirstName=’David’");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'] . “ “ $row[‘DOB’];
echo "<br />";
}
mysql_close($con);
?>

Output will be.

David Fernandas 04-16-1977
Operator that can be used in where clause are as under

= Equal
!= Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern

Order By

2009-03-13T22:09:00.000-07:00

We use Order By Keyword to sort the records in a recordset. The general syntax is as under.

Syntax:

SELECT columnname(s) FROM tablename ORDER BY columnname SortOrder

Example:
Table Name(empinfo)

FirstName	LastName	Email	DOB
Steve	Martin	smart.steve@studiesinn.com	08-04-1988
David	Fernandas	davidfernandas@studiesinn.com	04-16-1977
Ajay	Rathor	ajayrathor@studiesinn.com	08-24-1986

?php

$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydb", $con);
$result = mysql_query("SELECT * FROM empinfo order by FirstName asc");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'] . “ “ $row[‘DOB];
echo "<br />";
}
mysql_close($con);
?>

The Output will be
Ajay Rathor 08-24-1986
David Fernandas 04-16-1977
Steve Martin 08-04-1988

Note:
SortOrder is optional if you does not use it the records will be sorted in ascending order (1 to 10 or a to z). Use DESC as SortOrder to sort records in descending order. RAND() is used for random sort. SQL statements are not case sensitive. You can use both capital letters and small letter.

Update Statement

2009-03-13T22:03:00.001-07:00

Update statement is used to modify the data in a database table. The general syntax is as under.

Syntax
UPDATE tablename SET columnname = newvalue WHERE columnname = value
Example:
Table Name(empinfo)

FirstName	LastName	Email	DOB
Steve	Martin	smart.steve@studiesinn.com	08-04-1988
David	Fernandas	davidfernandas@studiesinn.com	04-16-1977
Ajay	Rathor	ajayrathor@studiesinn.com	08-24-1986

<?php
$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydb", $con);
$result = mysql_query("Update empinfo Set DOB=’07-05-1988’ where FirstName=’Steve’");
mysql_close($con);
?>

FirstName	LastName	Email	DOB
Steve	Martin	smart.steve@studiesinn.com	07-05-1988
David	Fernandas	davidfernandas@studiesinn.com	04-16-1977
Ajay	Rathor	ajayrathor@studiesinn.com	08-24-1986

Delete From Statement

2009-03-13T22:01:00.000-07:00

DELETE FROM statement is used to delete rows from a database table. The general syntax is as under.

Syntax
DELETE FROM tablename WHERE columnname = value

Example:
Table Name(empinfo)

FirstName	LastName	Email	DOB
Steve	Martin	smart.steve@studiesinn.com	08-04-1988
David	Fernandas	davidfernandas@studiesinn.com	04-16-1977
Ajay	Rathor	ajayrathor@studiesinn.com	08-24-1986

<?php
$con = mysql_connect("localhost:3306","adam","123456");
if (!$con)
{
die('Could not connect: ' . mysql_error());

}

mysql_select_db("mydb", $con);
$result = mysql_query("Delete From empinfo where FirstName=’Steve’");
mysql_close($con);
?>

FirstName	LastName	Email	DOB
David	Fernandas	davidfernandas@studiesinn.com	04-16-1977
Ajay	Rathor	ajayrathor@studiesinn.com	08-24-1986