Posted by Unknown - 0 comments

filetype: comments.html

<html>
<head>
<title>comment box</title>
</head>

<body>
<form action="sabincomment.php" method="post">
name:<input type="text" id="name" name="name"><br>
comment:<textarea id="comment"name="comment" rows=4 cols=60>your text here</textarea>
<input type="submit" id="submit" name="submit" value="comment">
</form>
http://webstufflearning.blogspot.com/
</body>
</html>

filetype:sabincomment.php

<?
$name=$_POST['name'];
$comment=$_POST['comment'];
$con=mysql_connect("localhost","root","");
mysql_select_db("share",$con);
$sql="INSERT INTO comment(name , comment) VALUES ('".$name."','".$comment."')";
$result=mysql_query($sql);
mysql_close();
if($result){
header('location:http://localhost/comments/display.php');
}

?>

http://webstufflearning.blogspot.com/

filetype:display.php

<html>

<head>

<title>comment box</title>

</head>

<body>

$con=mysql_connect("localhost","root","");

mysql_select_db("share",$con);

$sql="SELECT * from comment";

$result=mysql_query($sql);

mysql_close();

while($row=mysql_fetch_array($result)){

$name=$row['name'];

$comment=$row['comment'];

echo "$name:<br>$comment:";}

http://webstufflearning.blogspot.com/

</body>

</html>

text area

Posted by Unknown - 0 comments

<?php
$text = $_GET['Comments']; 



mysql_connect ("localhost", "user", "password") or die ('Error: ' . mysql_error());
mysql_select_db("databasename") or die ('Data error:' . mysql_error());

$query="INSERT INTO KeepData (player_data)VALUES ('$text')";

mysql_query($query) or die ('Error updating database' . mysql_error());




?>

 <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
 <textarea id="Comments" name="Comments">
 example text
 </textarea>
<input type="submit" name="mysubmit" value="Save Post" />
 </form>

Posted by Unknown - 0 comments

$Web Database Applications with PHP \& MySQL$

Chapter 2. PHP

In this chapter, we introduce the PHP scripting language. PHP is similar to high-level languages such as C, Perl, Pascal, FORTRAN, and Java, and programmers who have experience with any of these languages should have little trouble learning PHP. This chapter serves as an introduction to PHP; it's not a programming guide. We assume you are already familiar with programming in a high-level language.
The topics covered in this chapter include:

Programmers new to PHP should read Section 2.1, which describes the basic structure of a PHP script and its relationship to HTML, and includes discussion of how PHP handles variables and types. The two sections that follow, Section 2.2 and Section 2.3, deal with conditional statements and looping structures and should be familiar material. We then present a short example that puts many of the basic PHP concepts together.
The remainder of the chapter expands on the more advanced features of PHP, presents a reference to selected library functions, and discusses some of the common mistakes that programmers make when learning PHP. This material can be examined briefly, and used later as a reference while reading Chapter 4 to 13 and while programming in PHP. However, programmers new to PHP should consider reading the beginning of the Section 2.5 and Section 2.6 sections to understand the way PHP supports these concepts, as there are important differences from other languages.
We don't attempt to cover every function and every library that are supported by PHP. However, we provide brief descriptions of the supported libraries in Appendix E. In later chapters, we discuss more specialized library functions that support the topics and techniques presented here.

2.1. Introducing PHP

The current version of PHP is PHP4, which we call PHP throughout this book. The current release at the time of writing is 4.0.6.PHP is a recursive acronym that stands for PHP: Hypertext Preprocessor; this is in the naming style of GNU, which stands for GNU's Not Unix and which began this odd trend. The name isn't a particularly good description of what PHP is and what it's commonly used for. PHP is a scripting language that's usually embedded or combined with HTML and has many excellent libraries that provide fast, customized access to DBMSs. It's an ideal tool for developing application logic in the middle tier of a three-tier application.

2.1.1. PHP Basics

Example 2-1 shows the first PHP script in this book, the ubiquitous "Hello, world." When requested by a web browser, the script is run on the web server and the resulting HTML document sent back to the browser and rendered as shown in Figure 2-1.

Figure 2-1. The rendered output of Example 2-1 shown in the Netscape browser

Example 2-1. The ubiquitous Hello, world in PHP

<!DOCTYPE HTML PUBLIC
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
  <title>Hello, world</title>
</head>
<body bgcolor="#ffffff">
  <h1>
  <?php
    echo "Hello, world";
  ?>
  </h1>
</body>
</html>

Example 2-1 illustrates the basic features of a PHP script. It's a mixture of HTML—in this case it's mostly HTML—and a PHP script. The script in this example:

<?php
  echo "Hello, world";
?>

simply prints the greeting, "Hello, world."
The PHP script shown in Example 2-1 is rather pointless: we could simply have authored the HTML to include the greeting directly. Because PHP integrates so well with HTML, using PHP to produce static strings is far less complicated and less interesting than using other high-level languages. However, the example does illustrate several features of PHP:

The begin and end script tags are <?php and ?> or, more simply, just <? and ?>. The longer begin tag style <?php avoids conflicts with other processing instructions that can be used in HTML. We use both styles in this book.
Other begin and end tag styles can also be configured, such as the HTML style that is used with JavaScript or other embedded scripts: <script language="PHP"> and </script>.
Whitespace has no effect, except to aid readability for the developer. For example, the script could have been written succinctly as <?php echo "Hello, world";?> with the same effect. Any mix of spaces, tabs, carriage returns, and so on in separating statements is allowed.
A PHP script is a series of statements, each terminated with a semicolon. Our simple example has only one statement: echo "Hello, world";.
A PHP script can be anywhere in a file and interleaved with any HTML fragment. While Example 2-1 contains only one script, there can be any number of PHP scripts in a file.
When a PHP script is run, the entire script including the start and end script tags <?php and ?> is replaced with the output of the script.

TIP: When we present a few lines of code that are sections of larger scripts, we usually omit the start and end tags.

The freedom to interleave any number of scripts with HTML is one of the most powerful features of PHP. A short example is shown in Example 2-2; a variable, $outputString="Hello, world", is initialized before the start of the HTML document, and later this string variable is output twice, as part of the <title> and <body> elements. We discuss more about variables and how to use them later in this chapter.

Example 2-2. Embedding three scripts in a single document

<?php $outputString = "Hello, world"; ?>
<!DOCTYPE HTML PUBLIC 
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
  <title><?php echo $outputString; ?></title>
</head>
<body bgcolor="#ffffff">
  <h1><?php echo $outputString; ?></h1>
</body>
</html>

The flexibility to add multiple scripts to HTML can also lead to unwieldy, hard-to-maintain code. Care should be taken in modularizing code and HTML; we discuss how to separate code and HTML using templates in Chapter 13.

2.1.1.1. Creating PHP scripts

A PHP script can be written using plain text [4] and can be created with any text editor, such as joe, vi, nedit, emacs, or pico.

[4]While printable characters with the most significant bit are allowed, PHP scripts are usually written using characters from the 7-bit ASCII character set.

If you save a PHP script in a file with a .php extension under the directory configured as Apache's document root, Apache executes the script when a request is made for the resource. Following the installation instructions given in Appendix A, the document root is:

/usr/local/apache/htdocs/

Consider what happens when the script shown in Example 2-1 is saved in the file:

/usr/local/apache/htdocs/example.2-1.php

Apache—when configured with the PHP module—executes the script when requests to the URL http://localhost/example.2-1.php are made, assuming the web browser is running on the same machine as the web server.
If directory permissions don't permit creation of files in the document root, it's also possible to work in the user home directories. If the installation instructions in Appendix A have been followed, a directory can be created by a user beneath her home directory and the permissions set so that the directory is readable by the web server:

mkdir ~/public_html
chmod a+rx ~/public_html

The example file can then be created with the filename:

~/public_html/example.2-1.php

The file can then be retrieved with the URL http://localhost/~user/example.2-1.php, where user is the user login name.

2.1.1.2. Comments

Comments can be included in code using familiar styles from other high-level programming languages. This includes the following styles:

// This is a one-line comment

#  This is another one-line comment style

/* This is how you
   can create a multi-line
   comment */

2.1.1.3. Outputting data with echo and print

The echo statement used in Example 2-1 and Example 2-2 is frequently used and designed to output any type of data. The print statement can be used for the same purpose. Consider some examples:

echo "Hello, world";

// print works just the same
print "Hello, world";

// numbers can be printed too
echo 123;

// So can the contents of variables
echo $outputString;

The difference between print and echo is that echo can output more than one argument:

echo "Hello, ", "world";

There is also a shortcut that can output data. The following very short script outputs the value of the variable $temp:

<?=$temp; ?>

The print and echo statements are also often seen with parentheses:

echo "hello";

// is the same as
echo ("hello");

Parentheses make no difference to the behavior of print. However, when they are used with echo, only one output parameter can be provided.The echo and print statements can be used for most tasks and can output any combination of static strings, numbers, arrays, and other variable types discussed later in this chapter. We discuss more complex output with printf in Section 2.6 later in this section.

2.1.1.4. String literals

PHP can create double- and single-quoted string literals. If double quotation marks are needed as part of a string, the easiest approach is to switch to the single-quotation style:

echo 'This works';
echo "just like this.";

// And here are some strings that contain quotes
echo "This string has a ': a single quote!";
echo 'This string has a ": a double quote!';

Quotation marks can be escaped like this:

echo "This string has a \": a double quote!";
echo 'This string has a \': a single quote!';

One of the convenient features of PHP is the ability to include the value of a variable in a string literal. PHP parses double-quoted strings and replaces variable names with the variable's value. The following example shows how:

$number = 45;
$vehicle = "bus";
$message = "This $vehicle holds $number people";

// prints "This bus holds 45 people"
echo $message;

To include backslashes and dollar signs in a double-quoted string, the escaped sequences \\ and \$ can be used. The single-quoted string isn't parsed in the same way as a double-quoted string and can print strings such as:

'a string with a \ and a $'

We discuss parsing of string literals in more detail in Section 2.6.

2.1.2. Variables

Variables in PHP are identified by a dollar sign followed by the variable name. Variables don't need to be declared, and they have no type until they are assigned a value. The following code fragment shows a variable $var assigned the value of an expression, the integer 15. Therefore, $var is defined as being of type integer.

$var = 15;

Because the variable in this example is used by assigning a value to it, it's implicitly declared. Variables in PHP are simple: when they are used, the type is implicitly defined—or redefined—and the variable implicitly declared.
The variable type can change over the lifetime of the variable. Consider an example:

$var = 15;
$var = "Sarah the Cat";

This fragment is acceptable in PHP. The type of $var changes from integer to string as the variable is reassigned. Letting PHP change the type of a variable as the context changes is very flexible and a little dangerous.
Variable names are case-sensitive in PHP, so $Variable, $variable, $VAriable, and $VARIABLE are all different variables.

WARNING: One of the most common sources of bugs in PHP is failing to detect that more than one variable has accidentally been created. The flexibility of PHP is a great feature but is also dangerous. We discuss later how to set the error reporting of PHP so that it creates warning messages sensitive to unassigned variables being used.

2.1.3. Types

PHP has four scalar types—boolean, float, integer, and string—and two compound types, array and object.

TIP: In this book, and particularly in this chapter, we present function prototypes that specify the types of arguments and return values. There are many functions that allow arguments or return values to be of different types, which we describe as mixed.

Variables of a scalar type can contain a single value at any given time. Variables of a compound type—array or object—are made up of multiple scalar values or other compound values. Arrays and objects have their own sections later in this chapter. Other aspects of variables—including global variables and scope—are discussed later, with user-defined functions.Boolean variables are as simple as they get: they can be assigned either true or false. Here are two example assignments of a Boolean variable:

$variable = false;
$test = true;

An integer is a whole number, while a float is a number that has an exponent and a fractional part. The number 123.01 is a float, and so is 123.0. The number 123 is an integer. Consider the following two examples:

// This is an integer
$var1 = 6;

// This is a float
$var2 = 6.0;

A float can also be represented using an exponential notation:

// This is a float that equals 1120
$var3 = 1.12e3;

// This is also a float that equals 0.02
$var4 = 2e-2

You've already seen examples of strings earlier, when echo( ) and print( ) were introduced, and string literals are covered further in Section 2.6. Consider two example string variables:

$variable = "This is a string";
$test = 'This is also a string';

2.1.4. Constants

Constants associate a name with a simple, scalar value. For example, the Boolean values true and false are constants associated with the values 1 and 0, respectively. It's also common to declare constants in a script. Consider this example constant declaration:

define("pi", 3.14159);

// This outputs 3.14159
echo pi;

Constants aren't preceded by a $ character; they can't be changed once they have been defined; they can be accessed anywhere in a script, regardless of where they are declared; and they can only be simple, scalar values.
Constants are useful because they allow parameters internal to the script to be grouped. When one parameter changes—for example, if you define a new maximum number of lines per web page—you can alter this constant parameter in only one place and not throughout the code.

2.1.5. Expressions, Operators, and Variable Assignment

We've already described simple examples of assignment, in which a variable is assigned the value of an expression using an equals sign. Most numeric assignments and expressions that work in other high-level languages also work in PHP. Here are some examples:

// Assign a value to a variable
$var = 1;

// Sum integers to produce an integer
$var = 4 + 7;

// Subtraction, multiplication, and division
// that might have a result that is a float or 
// an integer, depending on the initial value of $var
$var = (($var - 5) * 2) / 3;

// These all add 1 to $var
$var = $var + 1;
$var += 1;
$var++;

// And these all subtract 1 from $var
$var = $var - 1;
$var -= 1;
$var--;

// Double a value
$var = $var * 2;
$var *= 2;

// Halve a value
$var = $var / 2;
$var /= 2;

// These work with float types too
$var = 123.45 * 28.2;

There are many mathematical functions available in the math library of PHP for more complex tasks. We introduce some of these in Section 2.9.
String assignments and expressions are similar:

// Assign a string value to a variable
$var = "test string";

// Concatenate two strings together
// to produce "test string"
$var = "test" . " string";

// Add a string to the end of another
// to produce "test string"
$var = "test";
$var = $var . " string";

// Here is a shortcut to add a string to
// the end of another
$var .= " test";

2.1.5.1. Expressions

Expressions in PHP are formulated in much the same way as other languages. An expression is formed from literal values (integers, strings, floats, Booleans, arrays, and objects), operators, and function calls that return values. An expression has a value and a type; for example, the expression 4 + 7 has the value 11 and the type integer, and the expression "Kelpie" has the value Kelpie and the type string. PHP automatically converts types when combining values in an expression. For example, the expression 4 + 7.0 contains an integer and a float; in this case, PHP considers the integer as a floating-point number, and the result is a float. The type conversions are largely straightforward; however, there are some traps, which are discussed later in this section.

2.1.5.2. Operator precedence

The precedence of operators in an expression is similar to the precedence defined in any other language. Multiplication and division occur before subtraction and addition, and so on. However, reliance on evaluation order leads to unreadable, confusing code. Rather than memorize the rules, we recommend you construct unambiguous expressions with parentheses, because parentheses have the highest precedence in evaluation.
For example, in the following fragment $variable is assigned a value of 32 because of the precedence of multiplication over addition:

$variable = 2 + 5 * 6;

The result is much clearer if parentheses are used:

$variable = 2 + (5 * 6);

2.1.6. Type Conversion

PHP provides several mechanisms to allow variables of one type to be considered as another type. Variables can be explicitly converted to another type with the following functions:

string strval(mixed variable)
integer intval(mixed variable)
float floatval(mixed variable)

The function settype(mixed variable, string type) can explicitly set the type of variable to type, where type is again one of array, boolean, float, integer, object, or string.
PHP supports type-casting in much the same way as C, to allow the type of an expression to be changed. By placing the type name in parentheses in front of a variable, PHP converts the value to the desired type:

`(int) $var` or `(integer) $var`	Cast to integer
`(bool) $var` or `(boolean) $var`	Cast to Boolean
`(float) $var`, `(double) $var` or `(real) $var`	Cast to float
`(string) $var`	Cast to string
`(array) $var`	Cast to array
`(object) $var`	Cast to object

The rules for converting types are mostly common sense, but some conversions may not appear so straightforward. Table 2-1 shows how various values of $var are converted using the (int), (bool),(string), and (float) casting operators.

Table 2-1. Examples of type conversion in PHP using casting operators

Value of $var	(int) $var	(bool) $var	(string) $var	(float) $var
null	0	false	""	0
true	1	true	"1"	1
false	0	false	""	0
0	0	false	"0"	0
3.8	3	true	"3.8"	3.8
"0"	0	false	"0"	0
"10"	10	true	"10"	10
"6 feet"	6	true	"6 feet"	6
"foo"	0	true	"foo"	0

2.1.6.1. Automatic type conversion

Automatic type conversion occurs when two differently typed variables are combined in an expression or when a variable is passed as an argument to a library function that expects a different type. When a variable of one type is used as if it were another type, PHP automatically converts the variable to a value of the required type. The same rules are used for automatic type conversion as are demonstrated in Table 2-1.
Some simple examples show what happens when strings are added to integers and floats and when strings and integers are concatenated:

// $var is set as an integer = 115
$var = "100" + 15;

// $var is set as a float = 115.0
$var = "100" + 15.0;

// $var is set as a string = "39 Steps"
$var = 39 . " Steps";

Not all type conversions are so obvious and can be the cause of hard-to-find bugs:

// $var is set as an integer = 39
$var = 39 + " Steps";

// $var is an integer = 42
$var = 40 + "2 blind mice";

// $var is a float, but what does it mean
$var = "test" * 4 + 3.14159;

Automatic type conversion can change the type of a variable. Consider the following example:

$var = "1"; // $var is a string == "1"
$var += 2;  // $var is now an integer == 3
$var /= 2;  // $var is now a float == 1.5
$var *= 2;  // $var is still a float == 3

WARNING: Care must be taken when interpreting non-Boolean values as Boolean. Many library functions in PHP return values of different types: false if a valid result could not be determined, or a valid result. A valid return value of 0, 0.0, "0", an empty string, null, or an empty array is interpreted false when used as a Boolean value.The solution is to test the type of the variable using the functions described in the next section.

2.1.7. Examining Variable Type and Content

Because PHP is flexible with types, it provides the following functions that can check a variable's type:

boolean is_int(mixed variable)
boolean is_float(mixed variable)
boolean is_bool(mixed variable)
boolean is_string(mixed variable)
boolean is_array(mixed variable)
boolean is_object(mixed variable)

All the functions return a Boolean value of true or false for the variable variable, depending on whether it matches the variable type that forms the name of the function. For example, the following prints 1, that is, true:

$test = 13.0;
echo is_float($test); // prints 1 for true

2.1.7.1. Debugging with print_r( ) and var_dump( )

PHP provides the print_r( ) and var_dump( ) functions, which print the type and value of an expression in a human-readable form:

print_r(mixed expression)
var_dump(mixed expression [, mixed expression ...])

These functions are useful for debugging a script, especially when dealing with arrays or objects. To test the value and type of $variable at some point in the script, the following code can be used:

$variable = 15;
var_dump($variable);

This prints:

int(15)

While the var_dump( ) function allows multiple variables to be tested in one call, and provides information about the size of the variable contents, print_r( ) provides a more concise representation of arrays and objects. These functions can be used on variables of any type, and we use them throughout this chapter to help illustrate the results of our examples.

2.1.7.2. Testing, setting, and unsetting variables

During the running of a PHP script, a variable may be in an unset state or may not yet be defined. PHP provides the isset( ) function and the empty( ) language construct to test the state of variables:

boolean isset(mixed var)
boolean empty(mixed var)

isset( ) tests if a variable has been set with a non-null value, while empty( ) tests if a variable has a value. The two are different, as shown by the following code:

$var = "test";

// prints: "Variable is Set"
if (isset($var)) echo "Variable is Set";

// does not print
if (empty($var)) echo "Variable is Empty";

A variable can be explicitly destroyed using unset( ):

unset(mixed var [, mixed var [, ...]])

After the call to unset in the following example, $var is no longer defined:

$var = "foo";

// Later in the script
unset($var);

// Does not print
if (isset($var)) echo "Variable is Set";

Another way to test that a variable is empty is to force it to the Boolean type using the (bool) cast operator discussed earlier. The example interprets the $var variable as type Boolean, which is equivalent to testing for !empty($var):

$var = "foo";

// Both lines are printed
if ((bool)$var)    echo "Variable is not Empty";
if (!empty($var))  echo "Variable is not Empty";

Table 2-2 show the return values for isset($var), empty($var), and (bool)$var when the variable $var is tested. Some of the results may be unexpected: when $var is set to "0", empty( ) returnstrue.

Table 2-2. Expression values

State of the variable $var	isset($var)	empty($var)	(bool)$var
$var = null;	false	true	false
$var = 0;	true	true	false
$var = true	true	false	true
$var = false	true	true	false
$var = "0";	true	true	false
$var = "";	true	true	false
$var = "foo";	true	false	true
$var = array( );	true	true	false
unset $var;	false	true	false

Posted by Unknown - 0 comments

3.6. Querying with SQL SELECT

We begin this section by covering the basics of using the SELECT statement. We then introduce the WHERE clause for selecting data that matches a condition. The section concludes with an introduction to the more advanced features of SELECT statements.

3.6.1. Basic Querying

The SELECT statement is used to query a database and for all output operations in SQL. Consider an example query:

SELECT surname, firstname FROM customer;

This outputs the values of the attributes surname and firstname from all rows, or records, in the customer table. Assuming we previously inserted four rows when we created the winestore database, the output from the MySQL command interpreter is:

+-----------+-----------+
| surname   | firstname |
+-----------+-----------+
| Marzalla  | Dimitria  |
| LaTrobe   | Anthony   |
| Fong      | Nicholas  |
| Stribling | James     |
+-----------+-----------+
4 rows in set (0.04 sec)

Any attributes of a table may be listed in a SELECT statement by separating each with a comma. If all attributes are required, the shortcut of an asterisk character (*) can be used. Consider the statement:

SELECT * FROM region;

This outputs all the data from the table region:

+-----------+---------------------+-------------+------+
| region_id | region_name         | description | map  |
+-----------+---------------------+-------------+------+
|         1 | Goulburn Valley     | NULL        | NULL |
|         2 | Rutherglen          | NULL        | NULL |
|         3 | Coonawarra          | NULL        | NULL |
|         4 | Upper Hunter Valley | NULL        | NULL |
+-----------+---------------------+-------------+------+
4 rows in set (0.07 sec)

SELECT statements can also output data that isn't from a database. Consider the following example:

SELECT curtime( );

This example runs a function that displays the current time:

+-----------+
| curtime( ) |
+-----------+
| 08:41:50  |
+-----------+
1 row in set (0.02 sec)

The SELECT statement can even be used as a simple calculator, using the mathematical functions described in the later section Section 3.9:

SELECT log(100)*4*pi( );

This outputs:

+-----------------+
| log(100)*4*pi( ) |
+-----------------+
|       57.870275 |
+-----------------+
1 row in set (0.19 sec)

3.6.2. WHERE Clauses

A WHERE clause is used as part of most SELECT queries; it limits retrieval to those rows that match a condition.
Consider this grape-growing region table containing the details of nine regions:

SELECT * from region;
+-----------+---------------------+-------------+------+
| region_id | region_name         | description | map  |
+-----------+---------------------+-------------+------+
|         1 | Goulburn Valley     | NULL        | NULL |
|         2 | Rutherglen          | NULL        | NULL |
|         3 | Coonawarra          | NULL        | NULL |
|         4 | Upper Hunter Valley | NULL        | NULL |
|         5 | Lower Hunter Valley | NULL        | NULL |
|         6 | Barossa Valley      | NULL        | NULL |
|         7 | Riverland           | NULL        | NULL |
|         8 | Margaret River      | NULL        | NULL |
|         9 | Swan Valley         | NULL        | NULL |
+-----------+---------------------+-------------+------+
9 rows in set (0.00 sec)

It is possible to select only a few rows with a SELECT statement by adding a WHERE clause. For example, to show only the first three regions, you can issue the following statement:

SELECT * FROM region
  WHERE region_id<=3;

This outputs all attributes for only the first three region rows:

+-----------+------------------+-------------+------+
| region_id | region_name      | description | map  |
+-----------+------------------+-------------+------+
|         1 | Goulburn Valley  | NULL        | NULL |
|         2 | Rutherglen       | NULL        | NULL |
|         3 | Coonawarra       | NULL        | NULL |
+-----------+------------------+-------------+------+
3 rows in set (0.00 sec)

You can combine the attribute and row restrictions and select only the region_name and region_id attributes for the first three regions:

SELECT region_id, region_name FROM region 
  WHERE region_id <= 3;
+-----------+------------------+
| region_id | region_name      |
+-----------+------------------+
|         1 | Goulburn Valley  |
|         2 | Rutherglen       |
|         3 | Coonawarra       |
+-----------+------------------+
3 rows in set (0.00 sec)

More complex WHERE clauses use the Boolean operators AND and OR, as well as the functions described later in Section 3.9. The Boolean operators AND and OR have the same function as the PHP && and ||operators introduced in Chapter 2.
Consider an example query that uses the Boolean operators:

SELECT * FROM customer 
  WHERE surname='Marzalla' AND 
        firstname='Dimitria';

This retrieves rows that match both criteria, that is, those customers with a surname Marzalla and a firstname Dimitria.
Consider a more complex example:

SELECT cust_id FROM customer 
  WHERE (surname='Marzalla' AND firstname LIKE 'M%') 
      OR email='john@lucaston.com';

This finds rows with either the surname Marzalla and a firstname beginning with M, or customers with the email address john@lucaston.com. The OR operator isn't exclusive, so an answer can have anemail of john@lucaston.com, a surname of Marzalla, and a firstname beginning with M. This query, when run on the winestore database, returns:

+---------+
| cust_id |
+---------+
|     440 |
|     493 |
+---------+
2 rows in set (0.01 sec)

SELECT queries are often sophisticated and a long WHERE clause may include many AND and OR operators. More complex examples of queries are shown in the later section Section 3.7.
The WHERE clause is also a common component of UPDATE and DELETE statements, and we have shown simple examples of using WHERE with these earlier in this chapter. Consider another example of anUPDATE with a WHERE clause:

UPDATE wine SET winery_id = 298 WHERE winery_id = 299;

In this case, for wines that are made by the winery with winery_id=299, the winery_id is changed to winery_id=298.
The WHERE clause can be used similarly in a DELETE. Consider an example:

DELETE FROM wine WHERE winery_id = 299;

This removes only selected rows based on a condition; here the wines made by the winery with winery_id=299 are deleted.

3.6.3. Sorting and Grouping Output

We will now discuss techniques to manage the order and grouping of the output.

3.6.3.1. ORDER BY

The ORDER BY clause sorts the data after the query has been evaluated. Consider an example:

SELECT surname, firstname FROM customer
  WHERE title='Mr'
  AND city = 'Portsea' 
  ORDER by surname;

This query finds all customers who have a title Mr and live in Portsea. It then presents the results sorted alphabetically by ascending surname:

+-----------+-----------+
| surname   | firstname |
+-----------+-----------+
| Dalion    | Anthony   |
| Galti     | Jim       |
| Keisling  | Mark      |
| Leramonth | James     |
| Mellili   | Derryn    |
| Mockridge | James     |
| Nancarral | Joshua    |
| Ritterman | James     |
+-----------+-----------+
8 rows in set (0.01 sec)

Sorting can be on multiple attributes. For example:

SELECT surname, firstname, initial FROM customer 
  WHERE zipcode='3001' OR 
        zipcode='3000' 
  ORDER BY surname, firstname, initial;

This presents a list of customers in areas with zipcode='3000' or zipcode='3001', sorted first by ascending surname, then (for those customers with the same surname) by firstname, and (for those customers with the same surname and first name), by initial. So, for example, the output may be:

+-----------+-----------+---------+
| surname   | firstname | initial |
+-----------+-----------+---------+
| Keisling  | Belinda   | C       |
| Leramonth | Hugh      | D       |
| Leramonth | Joshua    | H       |
| Leramonth | Joshua    | R       |
| Young     | Bob       | A       |
+-----------+-----------+---------+
5 rows in set (0.11 sec)

By default, the ORDER BY clause sorts in ascending order, or ASC. To sort in reverse or descending order, DESC can be used. Consider an example:

SELECT * FROM customer 
  WHERE city='Melbourne'
  ORDER BY surname DESC;

3.6.3.2. GROUP BY

The GROUP BY clause is different from ORDER BY because it doesn't sort the data for output. Instead, it sorts the data early in the query process, for the purpose of grouping or aggregation. An example shows the difference:

SELECT city, COUNT(*) FROM customer
  GROUP BY city;

This query outputs a sorted list of cities and, for each city, the COUNT of the number of customers who live in that city. The effect of COUNT(*) is to count the number of rows per group. In this example, it doesn't matter what is counted; COUNT(surname) has exactly the same result.
Here are the first few lines output by the query:

+--------------+----------+
| city         | COUNT(*) |
+--------------+----------+
| Alexandra    |       14 |
| Armidale     |        7 |
| Athlone      |        9 |
| Bauple       |        6 |
| Belmont      |       11 |
| Bentley      |       10 |
| Berala       |        9 |
| Broadmeadows |       11 |

The query aggregates or groups all the rows for each city into sets, and the COUNT(*) operation counts the number in each set. So, for example, there are 14 customers who live in Alexandra.
The GROUP BY clause can find different properties of the aggregated rows. Here's an example:

SELECT city, MAX(salary) FROM customer 
  GROUP BY city;

This query first groups the rows by city and then shows the maximum salary in each city. The first few rows of the output are as follows:

+-----------+-------------+
| city      | MAX(salary) |
+-----------+-------------+
| Alexandra |      109000 |
| Armidale  |       75000 |
| Athlone   |       84000 |
| Bauple    |       33000 |

TIP: The GROUP BY clause should be used only when the query is designed to find a characteristic of a group of rows, not the details of individual rows.

There are several functions that can be used in aggregation with the GROUP BY clause. Five particularly useful functions are:

AVG( ): Finds the average value of a numeric attribute in a set
MIN( ): Finds a minimum value of a string or numeric attribute in a set
MAX( ): Finds a maximum value of a string or numeric attribute in a set
SUM( ): Finds the sum total of a numeric attribute
COUNT( ): Counts the number of rows in a set

The SQL standard places a constraint on the GROUP BY clause that MySQL doesn't enforce. In the standard, all attributes that are selected (i.e., appear after the SELECT statement) must appear in the GROUPBY clause. Most examples in this chapter don't meet this unnecessary constraint of the SQL standard.

3.6.3.3. HAVING

The HAVING clause permits conditional aggregation of data into groups. For example, consider the following query:

SELECT city, count(*), max(salary)
  FROM customer
  GROUP BY city
  HAVING count(*) > 10;

The query groups rows by city, but only for cities that have more than 10 resident customers. For those groups, the city, count( ) of customers, and maximum salary of a customer in that city is output. Cities with less than 10 customers are omitted from the result set. The first few rows of the output are as follows:

+--------------+----------+-------------+
| city         | count(*) | max(salary) |
+--------------+----------+-------------+
| Alexandra    |       14 |      109000 |
| Belmont      |       11 |       71000 |
| Broadmeadows |       11 |       51000 |
| Doveton      |       13 |       77000 |
| Eleker       |       11 |       97000 |
| Gray         |       12 |       77000 |

The HAVING clause must contain an attribute or expression from the SELECT clause.

TIP: The HAVING clause is used exclusively with the GROUP BY clause. It is slow and should never be used instead of a WHERE clause.

3.6.3.4. DISTINCT

The DISTINCT operator presents only one example of each row from a query. Consider an example:

SELECT DISTINCT surname FROM customer;

This shows one example of each different customer surname in the customer table. This example has exactly the same effect as:

SELECT surname FROM customer GROUP BY surname;

The DISTINCT clause is usually slow to run, much like the GROUP BY clause. We discuss how indexes and query optimization can speed queries later in this chapter.

3.6.4. Limiting Result Sets in MySQL

An additional operator is available in MySQL that limits the size of the result sets. For example, the following query returns only the first five rows from the wine table:

SELECT * FROM wine LIMIT 5;

This saves query evaluation time and reduces the size of the result set that must be buffered by the DBMS. The LIMIT operator is MySQL-specific.

Posted by Unknown - 0 comments

http://coursesweb.net/php-mysql/common-php-errors-solution_t

Posted by Unknown - 0 comments

Simple PHP web based address book using MySql

Screen shot of application

Using the $_GET variable to do the work.

Today we will be creating a web based address book using PHP and MySql, it will be using the $_GET superglobal, as a means of reviewing the database contents.

Before we start here are the source files:

http://diadde.com/dl/addressBook.zip

Working demo:

http://diadde.com/test/addressTest.php

First, create the data base using this sql snippet:

CREATE TABLE IF NOT EXISTS `address` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(30) default NULL,
`phone` varchar(30) default NULL,
`email` varchar(30) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Then insert some data into our database using this snippet:

INSERT INTO `address` (`id`, `name`, `phone`, `email`) VALUES
(1, 'Laika Clay', '430-555-2252', 'laika@doggie.com'),
(2, 'Tiger Clay', '658-555-5985', 'tiger@kittie.us'),
(4, 'A Clay', '555-777-0000', 'clay@php.com'),
(5, 'Santa Clause', '888-888-7777', 'santa@np.net');

Now that we have that in place, we can create the form that be used for adding and editing the entry:

<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>

Simple enough, we will use this form in our all in one page address book, it will be used in adding and editing the database contents (addresses).

First thing we want to do is to setup the variable that will be used to run this thing:

$mode = $_GET['mode'];

The above will be called throughout the app to control the functions of it.

Before we can use the $mode var we setup a switch, this will be used in different sections of the app to do the actions:

switch($mode){

//cases in bewteen

}

If you are used to using ASP or Vb Script switch is the same as select case.

The cases are as follows:

add
added
edit
edited
remove

ADD:

This Case will add new contacts to our database, and is called by a hyperlink which will use the word 'add', look in the address bar after you click this, and will see this after the ? mark.

case 'add':
?>
<h2>Add Contact</h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>
</form>
<?php
break;

ADDED:

This will called the case added, but unlike add it will use the querystring (address bar) to function and then redirect the page after we are done with adding the contact.

//added a record
case 'added':
//first setup the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
//then lets use'em
$sql = "INSERT INTO address (name, phone, email) VALUES ('" . $name . "','" . $phone . "','" . $email . "')";
//echo $sql;
//return;
mysql_query($sql);
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

EDIT:

Calls the address using the address bar vars with name , email , phone number , but moore importantly the id of the record. We need this to perform the edit on that record, we have to be specific on the updating of the records, other wise we could update all the records with same information (no go)

?>
<h2>Editing: <?=$_GET['name'];?></h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=edited" method="post">
<table width="399" class="tableStyleClassTwo">
<tr><td width="87">Name:</td>
<td width="551"><div align="left">
<input type="text" value="<?=$_GET['name'];?>" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" value="<?=$_GET['phone'];?>" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" value="<?=$_GET['email'];?>" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> |<input name="Submit" type="submit" value="Save Changes" /></td></tr>
<input type="hidden" name="mode" value="edited">
<input type="hidden" name="id" value="<?=$_GET['id'];?>">
</table>
</form>
<?php
break;

EDITED:

This uses the id we planted inside of the form, in a text field named id, to show this click on a record to edit, look at the code source, (view source), you shold see an input named "id". That was used in the update sql to update that record ONLY.

case 'edited':
//again clarify the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$id = $_POST['id'];
//do the query
$sql = "UPDATE address SET name = '" . $name ."', phone = '" . $phone . "', email = '" . $email . "' WHERE id = '" . $id . "'";
mysql_query($sql);
//echo $sql;
//return;
//below you can either redirect show a message or put a link, and if you think harder you can probably do alot more
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

REMOVE:

Does what is says deletes the record, once and for all, there is no coming back after this.

case 'remove':
$id = $_GET['id'];
//lets remove the record this one is easy
$sql ="delete from address where id= '" . $id ."'";
//run the query
mysql_query($sql);
//echo $sql;
//return;
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

DEFAULT:

//this will show the table scructure by default (ie, no actions)
default:
//opening query
$sql ="SELECT * FROM address ORDER BY name ASC";
$data = mysql_query($sql);
//you can put in an error statement if no records or just display, just do what makes sense to you, the rest will come
?>
<h2>Phone Book Example</h2>
<table class="tableStyleClass">
<tr>
<th width="100">Name</th>
<th width="100">Phone</th>
<th width="200">Email</th>
<th width="100" colspan="2">Admin</th>
</tr>
<td colspan="5" align="right"><?php if($disable!=1){?><div align="right"><a href="<?=$_SERVER['PHP_SELF'];?>?mode=add"?mode=add>Add Contact</a><?php }else{?>Contact Book is Full<?php } ?></div></td>
<?php
//lets set a variable for offest coloered rows
$rowColor = 0;
//here is the loop using the statement above
while($info = mysql_fetch_array( $data )){
if($rowColor==0){
?>
<tr class="oddClassStyle">
<?php
$rowColor =1;
}elseif($rowColor==1){
?>
<tr class="evenClassStyle">
<?php
$rowColor = 0;
}
?>
<td><?=$info['name'];?></td>
<td><?=$info['phone'];?></td>
<td><a href=mailto:"<?=$info['email'];?>"><?=$info['email'];?></a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&name=<?=$info['name'];?>&phone=<?=$info['phone'];?>&email=<?=$info['email'];?>&mode=edit" >Edit </a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&mode=remove">Remove</a></td>
</tr>
<?php
}
?>
</table>
<?php
break;

This is a special case that defaults and show what we want to display in case we are not trying to edit or add, etc.

The entire code follows:

<?php
ob_start();//this just buffers the header so that you dont recieve an error for returning to the same page
if(isset($_GET['id']) && $_GET['mode'] == 'edit'){
//lets get the details for the paage title
$title = "We are editing: " . $_GET['name'] . " are you sure!!!";
}
?>
<html>
<head>
<title><?php if(!$title){?>Address Book<?php }else{ echo $title; }//end if?></title>
<style>
body{font-family:Arial, Helvetica, sans-serif;font-size:10px;}
table.tableStyleClass{border-collapse:collapse;border:1px solid #cccccc;background-color:#f1f1f1;width:650px;font-family:Arial, Helvetica, sans-serif;font-size:11px;}
table.tableStyleClassTwo{border-collapse:collapse;border:1px solid #cccccc;background-color:#f1f1f1;width:350px;font-family:Arial, Helvetica, sans-serif;font-size:11px;}
th{background-color:#999999;color:#ffffff;margin:1px;}
td{border-right:1px solid #cccccc;padding:2px;text-align:center;}
.oddClassStyle{background-color:#ffffff;border-bottom:1px solid #cccccc;}
.evenClassStyle{background-color:#f1f1f1;border-bottom:1px solid #cccccc;}
</style>
</head>
<body>

<?php
// Connects to your Database
mysql_connect("#", "#", "#") or die(mysql_error());
mysql_select_db("address") or die(mysql_error());
//we will use a case switch to look for the variable to make the decisions on what to show
//this is the variable that will control the switch case
//first lets set it looking for a query string or a post version of it
/*if(isset($_GET['id'])){
$mode = $_GET['mode'];//address bar version
$id = $_GET['id'];
}else{
$mode = $_POST['mode'];//form based version
$id = $_POST['id'];
}// now we know yay*/
//begin the switch
$mode = $_GET['mode'];
//look to see if the book is full
$checkSql="select count(id) as eCount from address";
$result = mysql_query($checkSql);
$row = mysql_fetch_assoc($result);
if($row['eCount'] == 6){
$disable = 1;
}
switch($mode){
//add a record
case 'add':
?>
<h2>Add Contact</h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=added" method="post">
<table class="tableStyleClassTwo">
<tr><td>Name:</td><td><div align="left">
<input type="text" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> | <input name="Submit" type="submit" id="Submit" value="Add New Contact" <?php if($disable ==1){?>disabled<?php } ?>/></td></tr>
<input type="hidden" name="mode" value="added">
</table>
</form>
<?php
break;
//added a record
case 'added':
//first setup the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
//then lets use'em
$sql = "INSERT INTO address (name, phone, email) VALUES ('" . $name . "','" . $phone . "','" . $email . "')";
//echo $sql;
//return;
mysql_query($sql);
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

case 'edit':
?>
<h2>Editing: <?=$_GET['name'];?></h2>
<form name="form1" action="<?=$_SERVER['PHP_SELF'];?>?mode=edited" method="post">
<table width="399" class="tableStyleClassTwo">
<tr><td width="87">Name:</td>
<td width="551"><div align="left">
<input type="text" value="<?=$_GET['name'];?>" name="name" />
</div></td></tr>
<tr><td>Phone:</td><td><div align="left">
<input type="text" value="<?=$_GET['phone'];?>" name="phone" />
</div></td></tr>
<tr><td>Email:</td><td><div align="left">
<input type="text" value="<?=$_GET['email'];?>" name="email" />
</div></td></tr>
<tr><td colspan="2" align="center"><a href="javascript:history.go(-1);">Back</a> |<input name="Submit" type="submit" value="Save Changes" /></td></tr>
<input type="hidden" name="mode" value="edited">
<input type="hidden" name="id" value="<?=$_GET['id'];?>">
</table>
</form>
<?php
break;

case 'edited':
//again clarify the vars
$name = $_POST['name'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$id = $_POST['id'];
//do the query
$sql = "UPDATE address SET name = '" . $name ."', phone = '" . $phone . "', email = '" . $email . "' WHERE id = '" . $id . "'";
mysql_query($sql);
//echo $sql;
//return;
//below you can either redirect show a message or put a link, and if you think harder you can probably do alot more
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

case 'remove':
$id = $_GET['id'];
//lets remove the record this one is easy
$sql ="delete from address where id= '" . $id ."'";
//run the query
mysql_query($sql);
//echo $sql;
//return;
//done take me back to the main page
header('location: ' . $_SERVER['PHP_SELF']);
break;

//this will show the table scructure by default (ie, no actions)
default:
//opening query
$sql ="SELECT * FROM address ORDER BY name ASC";
$data = mysql_query($sql);
//you can put in an error statement if no records or just display, just do what makes sense to you, the rest will come
?>
<h2>Phone Book Example</h2>
<table class="tableStyleClass">
<tr>
<th width="100">Name</th>
<th width="100">Phone</th>
<th width="200">Email</th>
<th width="100" colspan="2">Admin</th>
</tr>
<td colspan="5" align="right"><?php if($disable!=1){?><div align="right"><a href="<?=$_SERVER['PHP_SELF'];?>?mode=add"?mode=add>Add Contact</a><?php }else{?>Contact Book is Full<?php } ?></div></td>
<?php
//lets set a variable for offest coloered rows
$rowColor = 0;
//here is the loop using the statement above
while($info = mysql_fetch_array( $data )){
if($rowColor==0){
?>
<tr class="oddClassStyle">
<?php
$rowColor =1;
}elseif($rowColor==1){
?>
<tr class="evenClassStyle">
<?php
$rowColor = 0;
}
?>
<td><?=$info['name'];?></td>
<td><?=$info['phone'];?></td>
<td><a href=mailto:"<?=$info['email'];?>"><?=$info['email'];?></a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&name=<?=$info['name'];?>&phone=<?=$info['phone'];?>&email=<?=$info['email'];?>&mode=edit" >Edit </a></td>
<td><a href="<?=$_SERVER['PHP_SELF'];?>?id=<?=$info['id'];?>&mode=remove">Remove</a></td>
</tr>
<?php
}
?>
</table>
<?php
break;

}//end the switch
?>
</body>
</html>
<?php ob_flush();?>

You may notice some other functions like ob_start(), etc that I have included in the tut, these I will be happy to explain if you have any comments or questions.

Recap: There are more elegant and secure ways to capture and display the data, but the point of the article is give new users something to build on and develop their own techniques and habits.

There are alot of features that could be used in this, we could archive the addresses, instead of deleting, use some nice images to redecorate the whole app, even incorporate this into something that could bulk imprt address, maybe some API, yahoo anyone, skies the limit. Anyway take it and run with it.


1.5. Our Case Study		2.2. Conditions and Branches

Chapter 2. PHP

Contents:

2.1. Introducing PHP

2.1.1. PHP Basics

Figure 2-1. The rendered output of Example 2-1 shown in the Netscape browser

Example 2-1. The ubiquitous Hello, world in PHP

Example 2-2. Embedding three scripts in a single document

2.1.1.1. Creating PHP scripts

2.1.1.2. Comments

2.1.1.3. Outputting data with echo and print

2.1.1.4. String literals

2.1.2. Variables

2.1.3. Types

2.1.4. Constants

2.1.5. Expressions, Operators, and Variable Assignment

2.1.5.1. Expressions

2.1.5.2. Operator precedence

2.1.6. Type Conversion

Table 2-1. Examples of type conversion in PHP using casting operators

2.1.6.1. Automatic type conversion

2.1.7. Examining Variable Type and Content

2.1.7.1. Debugging with print_r( ) and var_dump( )

2.1.7.2. Testing, setting, and unsetting variables

Table 2-2. Expression values

3.6. Querying with SQL SELECT

3.6.1. Basic Querying

3.6.2. WHERE Clauses

3.6.3. Sorting and Grouping Output

3.6.3.1. ORDER BY

3.6.3.2. GROUP BY

3.6.3.3. HAVING

3.6.3.4. DISTINCT

3.6.4. Limiting Result Sets in MySQL

Simple PHP web based address book using MySql

Screen shot of application

Using the $_GET variable to do the work.

Labels

My Blog List

Likes On Facebook

Blog Archive

About me

Flag counter

Feedjit