PHP databases

One of the greatest things about PHP is its ability to communicate with databases.

This lesson focuses on:

A short introduction to SQL
An introduction to MySQL
MySQL database commands and data types
Connecting to a MySQL database
Creating a query to and getting data from a database
Closing the connection

A short introduction to SQL

Before beginning a discussion of how to use PHP to communicate with databases, it is imperative to first know a little bit about the language used for database access - that language is SQL.

SQL stands for Structured Query Language, it is the standardized language used to request information from a database, and it is the language we will be using for database access. The original version of SQL was SEQUEL (Structured English Query Language), and it was designed by IBM in 1974 and 1975. In 1979, Oracle introduced SQL as a commercial database system.

An introduction to MySQL

The database system we will be working with is MySQL. The MySQL database is an open source Rational Database Management System that relies on SQL for data processing.

Known for its speed and reliability, MySQL has gained much popularity over proprietary database systems. MySQL is commonly used for web and embedded applications, and it can run on several operating systems including UNIX, Windows, and Mac OS. MySQL was developed by the MySQL AB company.

Database commands and data types

There are various SQL commands that can be used with the MySQL database system to perform various actions such as creating tables, deleting tables, inserting data into tables, creating new databases, and more.

MySQL database commands:

use
The use command is used to select a database to use.

Syntax:
```
USE name_Of_Database_To_Use;
```
Example:
```
USE database1;
```
In the above example, the database named database1 is selected to be used.
create database
The create database command is used to create a database.

Syntax:
```
create database name_Of_Database_To_Be_Created;
```
Example:
```
create database database1;
```
In the above example, the database named database1 will be created.
create table
The create table command is used to create a table in a database.

Syntax:
create table name_Of_Table_To_Be_Created();
Example:
```
create table table1();
```
In the above example, a table named table1 will be created. The stuff that goes in the parentheses is the content of the table.
select
The select command is used to select particular data from a database in conjunction with the from command.

Syntax:
```
select data_To_Be_Selected from

table_From_Which_To_Select_Data
```
Example:
```
select * from table1
```
In the above example, all data will be selected from the table named table1.
describe
The describe command is used to describe the structure of a table.

Syntax:
```
describe name_Of_Table_To_Describe;
```
Example:
```
describe table1;
```
In the above example, the structure of the table named table1 will be described.
drop
The drop command is used to delete a table.

Syntax:
```
drop name_Of_Table_To_Delete;
```
Example:
```
drop table1;
```
In the above example, the table named table1 will be deleted.
insert
The insert command together with the into keyword is used to add a record to a table.

Syntax:
```
insert into name_Of_Table_To_Insert_Record_Into 

values(place values to be inserted into table here);
```
Example:
```
insert into table1 

values(4, 'text string', 'another text string', 6);
```
In the above example, a new record will be added to the table named table1. The values that will be added to this record are 4, 'text string', 'another text string', and 6.

MySQL is specific about data, you can not just insert any type of data anywhere. Different data types exist for different types of data.

MySQL data types:

char
This data type holds a string of fixed length from 0 to 255 characters.

Syntax:
```
char(n)
```
n = the number of characters in the string. If there are less characters in the string than specified by n, then the string will be padded with additional spaces.

Example:
```
char(20)
```
The above example specifies that there will be a string with 20 characters.
varchar
This data type holds a string of variable length from 0 to 255 characters.

Syntax:
```
varchar(n)
```
n = the maximum number of characters in the string.

Example:
```
varchar(50)
```
The above example specifies that there will be a string with a maximum of 50 characters.
text
This data type holds a string with a maximum length of 65535 characters.
int
This data type holds a numeric value between -214783648 and 214783647. When used in conjunction with the unsigned keyword, this data type holds a numeric value between 0 and 4294967295.

Connecting to a MySQL database

A connection to a MySQL database is established through PHP's mysql_connect() function which is then stored in a connection handle.

Syntax:

$connectionHandle = mysql_connect("server", 
                                  "username", "password");

Example:

<?php

$connection = mysql_connect("server1", "user", "pass");

?>

Once a connection to a MySQL database is established, you have to select a database from which to get data. This is achieved with the mysql_select_db() function.

Syntax:

mysql_select_db("name_Of_Data_Base_To_Select", 
                 $connectionHandleToUse);

Example:

<?php

$connection = mysql_connect("server1", "user", "pass");
mysql_select_db("database1", $connection);

?>

Creating a query to and getting data from a database

Once you establish a connection and select a database, the next thing to do is create a query to the selected database. Queries are created by PHP through the mysql_query() function.

Syntax:

$string_Which_Will_Return_Data_From_Query = 
mysql_query($the_Query, $the_Connection_Handle_To_Use);

Example:

<?php

$connection = mysql_connect("server1", "user", "pass");
mysql_select_db("database1", $connection);
$theResult = mysql_query("select * from table1", $connection);

?>

In the above example, a variable named $connection is used as the connection handle to a MySQL database. The parameters in the mysql_connect() function indicate to connect to a server named "server1" with the user name "user" and the password "pass". The database selected is "database1", and the connection handle used is $connection. The variable named $theResult is a string which will return the data from the query. The query is specified as the first parameter in the mysql_query() function as "SELECT * from table1". This query will return all the data from the table named table1. The second parameter in the mysql_query() function is $connection - it specifies which connection handle to use for the query.

Closing the connection

After the connection is established, the database selected, the database queried, and the data received, the connection to the MySQL database needs to be closed. This is achieved through the mysql_close() function.

Syntax:

mysql_close($connectionHandle);

Example:

<?php

$connection = mysql_connect("server1", "user", "pass");
mysql_close($connection);

?>

In the above example, a connection to a database specified by the connection handle $connection is closed.