PHP databases

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

This tutorial focuses on:

SQL
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

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.

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.

MySQL 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 - Selects a database for usage.

Syntax:

USE name_Of_Database_To_Use;

Example:

USE database1;

create database - Used to create a database.

Syntax:

create database name_Of_Database_To_Be_Created;

Example:

create database database1;

create table - Used to create a table in a database.

Syntax:

create table name_Of_Table_To_Be_Created();

Example:

create table table1();

select - Used to select particular data from a database together with the from command.

Syntax:

select data_To_Be_Selected from table_From_Which_To_Select_Data

Example:

select * from table1

describe - Used to describe the structure of a table.

Syntax:

describe name_Of_Table_To_Describe;

Example:

describe table1;

drop - Used to delete a table.

Syntax:

drop name_Of_Table_To_Delete;

Example:

drop table1;

insert - 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 - Holds a string of fixed length from 0 to 255 characters.
varchar - Holds a string of variable length from 0 to 255 characters.
text - Holds a string with a maximum length of 65535 characters.
int - Holds a numeric value between -214783648 and 214783647.

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 & 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); ?>