# PHP and MySQL Programming/Print version

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/PHP_and_MySQL_Programming

# Introduction to PHP
## Welcome to PHP

PHP started off as a collection of Perl scripts used to aid in the maintaining of personal home pages. Hence, it originally stood for "Personal Home Page." However, the language has grown into a huge open source initiative, used by thousands of amateur and professional programmers world wide. It now is officially known as "PHP: Hypertext Preprocessor," which is a recursive acronym. (For some strange reason, open source programmers seem to love recursive acronyms...)
The reason it is known as a preprocessor and not merely as a processor, is due to the fact that the php files are sent by the webserver to the PHP preprocessor to be processed. the resulting HTML (Hyper Text Markup Language), is then sent across the internet to the web browser requesting the php page.
PHP has traditionally been used for adding dynamic content onto previously static webpages; however, due to the rapid development of the language, it is now used for writing command line tools, and now has GTK bindings allowing the programmer to write GUI applications!
Since the beginning of version 5 of PHP, with the inclusion of Zend Engine II as PHP's foundation, PHP now can be used to write Object Oriented applications.
You can download PHP free of charge (as it/is an Open Source programming language) from http://www.php.net

# Syntax Overview
## PHP tags:
### <?php [code] ?>

* Enclose PHP code
* Embedded in normal HTML code
* Within PHP tags, statements are separated by a ; (generally also followed by a new line).
Example:

## Commenting

Comments out one line
Example:
Example:
Same function as //

### /* (text) */

Comments out everything between the /* and the */
Example:

## Variables

Variables in PHP are denoted by the $ prefix.
Example:
PHP supports dynamic variables.
Example:
PHP variables do not have to be declared ahead of time, nor do they require a type definition. PHP handles all data type conversions.
* Example:
PHP supports Boolean variables, which can be assigned either a one or a zero, or a true or false.
Example:

#### Helpful Definitions:
##### Equal

    values are equal in value, but may be of differing types. Uses == comparison operator. E.g. false is equal to 0.

##### Identical

    values are equal in value and of the same type. Uses === comparison operator. E.g. false is identical only to false, it is not identical to 0.

## Operators
### Arithmetic Operators

Example:

### Assignment Operators

Example:

### Comparison Operators

Example:

## Concatenation

Example:

## Arrays

PHP supports both numerically indexed arrays as well as associative arrays.
Example:

## Decision and Loop Statements
### If ... else statement

Example:

### Switch statement

Example:

### For statement

Example:

### Foreach statement

Example:

### While statement

Example:

### Do ... while statement

Example:

## Functions

Example:

## Classes

Example:
As of PHP 5.3.3, functions with the same name as the class will no longer act as a constructor. Versions prior to 5.3.3 the
line could be replaced with
to achieve the same effect.

# File Handling

As with most programming languages, file handling is very important for storing and retrieving a wide variety of data.

## Opening and Closing Files

To open a file, we use the fopen function. This function takes in two parameters. Firstly, a String containing the name of the file, and secondly, the mode with which to open the file with. It returns a filehandler. e.g.:
The above code assigns the file name of "text.txt" to a variable called $filename. Then it uses the fopen function to open up "text.txt" with 'r' mode (meaning "read mode") and returns a filehandler to $f.
To close a file, we simply call the fclose function, and pass it the filehandler. e.g.:

## Including a File

It is very easy, in PHP, to include code (be it PHP or HTML) from one file into another. This is achieved by means of the include function.
In the above example, the PHP code will look within the current directory of the PHP file calling the include function, it will then open the objects.php file, and include its content at of that file at the position of the include function.

## Writing to a File

The procedure for writing to a file is:
* Create a file handler.
* Use the file handler to open up an input stream from a file.
* Write Data to the file handler.
* Close the file handler.
Here is an example of writing two lines of text to a file:
fputs is used to write data to the file. It takes 2 variables, firstly, the file handler, and secondly, a String to write to the file handler.

## Reading from a File

Reading from a file goes along much the same format as writing to one. The function used to read from a file is fgets function. Which takes in two variables. Firstly, the filehandler from which to read, and secondly, the amount of data to retrieve.
The above code opens up a file, reads the first line of the file, and then displays it. If we wanted to rather read the entire contents of the file into a variable, we could replace the $line=fgets line with:

# Database Connectivity

You can only do so much with storing information in files. When you need to store large amounts of data, and perform intensive number crunching on that data, there is nothing better than a good SQL database. In this section, we'll discuss connecting PHP to a MySQL database and perform queries and retrieve data back from the database.

## Opening a Connection to a MySQL Database

The first thing that we need to do before we can interact with any database, is to open up a connection to that database server. This is done by using the mysql_connect function, which returns a database handler, much like a file handler when dealing with files. The database handler is then used to select the active database to use.
Here is the code to setup a connection to the database server, and to select a database to use:
Or

## Creating a Query

Once a connection to a database has been made, you will inevitably want to perform a database query. To create a query on the selected database, we use the mysql_query function. If you use a SELECT query, then the data returned from that query will be passed to the mysql_query function, which will in turn return it to a variable which you can specify. In the following example, two queries are made, the first does not return any data, and the second does.

## Retrieving data from a SELECT Query

There are many methods for retrieving data from a SELECT query.
If we take, for example, the following code:
To now retrieve the data from the $result variable, we can use one of many methods. The recommended method, however, is to sequentially go through each row of the table, storing it into a one-dimensional array. We do this by using the mysql_fetch_row function, passing it the variable into which the result is stored. Here is a simple example:
This will simply output the result in table-like format.
Here is another example of using this method:
The above example, simply splits the results up into multiple one-dimensional arrays, for easy manipulation.

## Closing a Database Connection

It is not always necessary to close a connection when you are finished, but it is advised. It is, however, necessary to close the connection to the database if you want to open up a new connection to a different database.
To close a connection to a database, we use the mysql_close function, as follows:

## Error Handling

It is useful when debugging, and even when you just want to make sure that a database does not behave unexpectedly. Once a query has been created via the mysql_query function, any error messages generated will be stored in the mysql_error function. Here is a sample code snippet to display a error message. However, when there is no error messages, a blank string is returned.

# Session Handling

HTML and PHP are "stateless" languages. Meaning that they are incapable of retaining a state between pages. To get around this serious limitation, we use sessions. With sessions, session variables are stored in a file on the web server and so are accessible across multiple pages.

## Starting a Session

Before we can start using session variables, we need to start a session. This needs to be done on every page that makes use of session variables. It is important to note that a session must be started before anything is outputted. Here is the code to start a session:
Please make sure that there are NO SPACES before the "<?php" (php starting tag), as that will return errors!
It is worth noting, that the way in which the server distinguishes between multiple sessions being implemented on it simultaneously is by session ID's. When a unique session is created, it is assigned a Session ID, which the browser retains, and is used to let the web server know which session to use.

## Writing Session Variables

Once a session is created, variables may be stored in the $_SESSION array variable. Here is an example:

## Retrieving Session Variables

Once a session is created, and variables are stored, they may be retrieved from the $_SESSION array. Here is an example:
The above example starts a session, then checks if the session variable 'user_name' has been created (with the isset function), if it has, then it assigns its value to $user_name, and prints it to the screen.

## Destroying a Session

To destroy a session, we use the session_destroy function. This is useful if we want to, for example, log a user off of a web-application. Thus we would use the following code:
Note that we first need to start the session before we destroy it. This is because the session_destroy function destroys the currently active session.

# Command Line Programming

PHP was traditionally used to help web administrators automate various mundane tasks and to add dynamism to the web pages. However, PHP (in its modern incarnation) is capable of much more. It is now being used in ways traditional programming languages are used. It is now possible to remove PHP applications from the constraints of the Internet server. Now, PHP is being used to write GUI applications as well as command line applications. We shall now look at writing PHP applications for the command line.

## Getting the PHP-CLI

If you are running Linux, you should get the corresponding package for the PHP-CLI. In Debian, it is just a matter of typing:
In Windows, the CLI should be included with the PHP files. Refer to http://www.php-cli.com/ and http://www.php.net for more help.

## Initial Considerations

We start off our command line applications with the following line:
Note: You do not include this line in Windows!
This should be the first line of the file, as it tells Linux which interpreter to use for the file.
The next thing you should do, is to make the file executable. This is done by the following Unix command:

## Getting Keyboard Input

In order for us to retrieve keyboard input from the command line, we will need to use a little trick. Basically, we create a file handler to a special file called the Standard Input.
Here is the code to create this file handler:
We can now use $stdin just as if it were a normal file that we had opened for reading. (We obviously could not write to it, as it is Standard INPUT).
Here is a useful function to get user input:

## Output Formatting

The main consideration to make when formatting output in command line programming as opposed to generating HTML output is that a new line is not created by a tag, in fact, any HTML tags you put into the output, will simply be displayed as plain text.
In order to create a new line in the command line, we need to use a control character, \n . Another useful function when doing command line programming is this:

## External Resources and Links

PHP CLI - ALL about running PHP script from command line: tutorials, options, examples, PHP CLI and PHP CGI difference.

# Form Handling

In order to get user input without using the command line, then you will use HTML forms. PHP has various functions and techniques for handling the input from HTML forms. In specific, we will use the special array variables $_GET and $_POST.

## HTML Forms

Before we start using these special variables, lets have a look at how to set up the HTML forms to interact with PHP. Here is a simple example form:
The above example sets up a form with a method and an action attribute. The `method` defines how the data from the form is to be transferred to the page defined by `action`. The `method` can either be GET or POST. If GET is used, then all the form elements will be appended to the url of the target page (specified by the action attribute), in url encoded format (target?attrib1=val1&attrib2=val2&attrib3=val3 etc...) These attributes are easily accessed in PHP by using the $_GET array.
If the POST method is specified, then the data will be put into a special server variable, and accessed in PHP by using the built-in $_POST array.
Note that in the HTML code, the name attribute of the INPUT tags are used as the reference strings in the $_GET and $_POST arrays, thus it is very important to always use unique values for the name attribute.

## GET

To retrieve GET variables in the address url, we use the following method:
Please note that GET is not good to use for this example, because you will generally not want username and password details being displayed in the URL. Rather use POST for login details and authentication.

## POST

To retrieve POST variables, we use the following method:
Note that when POST is used, the posted data is completely invisible to the user.

## Further Reading

* HyperText Markup Language/Forms

# Introduction to MySQL
## Introduction to MySQL

MySQL, the most popular open source SQL database, is developed and provided by MySQL AB, a commercial company which gets its income from providing services around the MySQL database. The MySQL software delivers a very fast, multi-threaded, multi-user, and robust SQL database server, intended for mission-critical, heavy-load production systems as well as for embedding into mass-deployed software.
The official way to pronounce MySQL is "My Ess Que Ell" (though in practice many users pronounce it as "my sequel"). Michael ("Monty") Widenius, the creator of MySQL, is unsure where the name originally came from, though he says that it may have come from his daughter My. Two more of Widenius's children, Max and Maria, have also lent their names to MySQL products (MaxDB and the Maria table type respectively).

## MySQL Licenses

The MySQL software is released under the GNU General Public License (GPL), which is probably the best known open source license. The software is provided "as is" and is without any warranty.
The MySQL software has Dual Licensing, which means you can use the MySQL software free of charge under the GPL. You can also purchase commercial MySQL licenses from MySQL AB if you do not wish to be bound by the terms of the GPL.
You need to purchase commercial non-GPL MySQL licenses:
* If you distribute MySQL Software with your non open source software.
* If you want warranty from MySQL AB for the MySQL software.
* If you want to support MySQL development.

# Creating a Database
## Introduction

MySQL is a relational database. In a relational database, data is stored as a collection of fields called a row. Like fields, rows are in turn collectively stored as a table. Tables are stored collectively in a database. One MySQL server can store multiple databases, and have users who have various rights to each database.
Nowadays, MySQL is one of the most popular relational databases among others as it is simple, free (open source), easy to maintain, and cost-effective.
In order to store data into MySQL, we will need to prepare a space in MySQL for a database.

## SQL Code

The code for creating such a database in SQL (Structured Query Language) is a simple query:
(Without the ' ' single quotes around the database name)

## Using the Database

After a database has been created, you have to tell MySQL that to make it the active database before you can start running further queries on it. You do this as follows:

# Creating a Table

Before creating a table, please read the previous section on Creating a Database.
A Table resides inside a database. Tables contain rows, which are made up of a collection of common fields or columns. Here is a sample output for a SELECT * query:
As you can see, We have rows (horizontal collection of fields), as well as columns (the vertical attributes and values).

## Creating a Table

The SQL code for creating a table is as follows:

## Example

Here is an example of creating a table called `books`:

## Getting Information about Tables

To get a list of tables:
Which produces the following output:
To show the CREATE query used to create the table:
Which produces the following output:
And then to show the same information, in a tabulated format:
Which produces the following output:

# Basic Queries
## Introduction

Here are a few basic queries to get you going. For more detailed information, have a look at the online documentation on the MySQL website http://dev.mysql.com/doc/refman/5.1/en/index.html .

## SELECT

The SELECT query is the standard means by which to retrieve information from the database. The syntax for the SELECT query is as follows:
Example:

## INSERT

The INSERT statement is used to insert data into a particular table of a database. The Syntax is as follows:
Example:

## DELETE

The DELETE statement is used to remove row(s) from a table. The syntax is as follows:
Example:

## ALTER

ALTER is used to modify the structure of a table. Here is the syntax for ALTER:

## DROP

DROP is used to completely remove a table or database. The syntax is as follows:

## TRUNCATE

TRUNCATE is used when you want to remove the data, but not the structure of a table. The syntax is as follows:

# XML and PHP
## Introduction

XML (eXtensible Markup Language) is used in mainstream development. It might have started off as an attempt at a web standard, but is now used even in more traditional applications as a document standard. For example, the Open Document Format employed by Sun in their StarOffice and OpenOffice suites is based on XML.
Because of its wide-spread use in the IT industry, it is fitting that we as PHP developers know how to make use of XML files in our PHP applications.

## XML Structure

Since XML documents are extensible, there are no limits to the tags that you can create to define data with. Here is an example of a simple XML document:
The reason that it looks so simple, is because it is so simple! Just as in HTML, elements are enclosed by angled brackets: "<" and ">", where the start element differs from the end element by the exclusion of a forward slash: "/".

## Creating an XML Parser in PHP
### Defining the XML Parser

In PHP, you define an XML Parser by using the xml_parser_create function as shown below.
You can think of the $parser variable in terms of a parsing engine for the XML document. Note that the ENCODING can be either:
1. ISO-8859-1 (default)
2. US-ASCII
3. UTF-8

### Defining the Element Handlers

Element handlers are defined by means of the xml_set_element_handler function as follows:
The three arguments accepted by the xml_set_element_handler function are:
1. XML_PARSER - The variable that you created when you called the xml_parser_create function.
2. START_FUNCTION - The name of the function to call when the parser encounters a start element.
3. END_FUNCTION - The name of the function to call when the parser encounters an end element.
e.g.:

### Defining Character Handlers

Character handlers are created by means of the set_character_handler function as follows:
The two arguments accepted by the set_character_handler function are:
1. XML_PARSER - The variable that you created when you called the xml_parser_create function.
2. CHARACTER_FUNCTION - The name of the function to call when the parser encounters character data.

### Starting the Parser

To finally start the parser, we call the xml_parse function as follows:
The two arguments accepted by the xml_parse function are:
1. The variable that you created when you called the xml_parser_create function.
2. The XML that is to be parsed.
e.g.:

### Cleaning Up

After parsing an XML document, it is considered good practice to free up the memory that is holding the parser. This is done by calling the xml_parser_free function as follows:

### Example
## Parsing XML Documents

We have seen the steps needed to successfully parse a XML document with PHP. Lets take a moment to reflect on how these steps are interconnected.
When a XML parser is initialized, php will go through the XML file. When a starting tag is found, a predefined function created by you, the programmer, is called. The same thing happens when php encounters the text between tags, and the end tags.
Here is a complete example of parsing XML documents. This example is a RSS reader which can be used to display News Articles from any RSS feed which conforms to RSS 1.0 standards.

### Example
## Dumping Database Contents into an XML File

* ↑ http://php.net/manual/en/language.oop5.decon.php