MySQL is the most popular open source database server.

 

What is MySQL

MySQL is a database. A database is a data storage area.

In a database, there are tables. Just like HTML tables, database tables contain rows, columns, and cells.

Databases are useful when storing information categorically. A company may have a database with the following tables: "Employees", "Products", "Customers" and "Orders".

 

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

Below is an example of a table called "Persons":

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger

The table above contains three records (one for each person) and four columns (LastName, FirstName, Address, and City).

 

Queries

A query is a question or a request.

With MySQL, we can query a database for a specific part and have a result-set returned.

A query like this:

SELECT LastName FROM Persons

Gives a result set like this:

LastName

Hansen

Svendson

Pettersen

 

MySQL is the most popular open source database server and is very often used with PHP.

 

Connect to a MySQL Database

Before you can access your database, you must establish a connection to it.

In PHP, this is done with the mysql_connect() function.

 

Opening a Connection

The mysql_connect() function is used to establish a MySQL connection.

Syntax

mysql_connect(servername,username,password);

 

Parameter	Description
servername	Optional. Specifies the server to connect to (can also include a port number. e.g. "hostname:port" or a path to a local socket for the localhost). Default value is "localhost:3306"
username	Optional. Specifies the username to log in with. Default value is the name of the user that owns the server process
password	Optional. Specifies the password to log in with. Default is ""

There are more available parameters, but the ones listed above are the most important. 

Example

In the following example we store the connection in a variable ($con) for later use in the script. The "die" part will be executed if a connection could not be established:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } // some code ?>

 

Closing a Connection

The connection will be closed as soon as the script ends. To close the connection before, use mysql_close().

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } // some code mysql_close($con); ?>

 

Tables are the main part of a database. A database can hold one or many tables.

 

MySQL CREATE

In this chapter we will show you how to create a database and a table.

This is done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.

 

Create a Database

The CREATE DATABASE statement is used to create a database in MySQL.

MySQL Syntax

CREATE DATABASE database_name

We must add the CREATE DATABASE statement to the mysql_query() function to execute the command.

Example

In the following example we create a database called "my_db":

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } $sql = "CREATE DATABASE my_db"; if (mysql_query($sql,$con))   {   echo "Database my_db created";   } else   {   echo "Error creating database: " . mysql_error();   } mysql_close($con); ?>

 

Create a Table

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

MySQL Syntax

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )

We must add the CREATE TABLE statement to the mysql_query() function to execute the command.

Example

The following example shows how you can create a table named "Person", with three columns. The column names will be "FirstName", "LastName" and "Age":

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } // Create database $sql = "CREATE DATABASE my_db"; if (mysql_query($sql,$con))   {   echo "Database my_db created";   } else   {   echo "Error creating database: " . mysql_error();   } // Create table in the my_db database mysql_select_db("my_db", $con); $sql = "CREATE TABLE Person  ( FirstName varchar(15), LastName varchar(15), Age int )"; mysql_query($sql,$con); mysql_close($con); ?>

Note: A database must be selected before a table can be created. This is done with the mysql_select_db() function.

Note: When you create database fields of type varchar, you must specify the max length of the fileds, e.g. varchar(15).

Here is the different MySQL data types that can be used:

Numbers	Description
int(size) smallint(size) tinyint(size) mediumint(size) bigint(size)	Hold integers only. The maximum number of digits can be specified in the size parameter
decimal(size,d) double(size,d) float(size,d)	Hold numbers with fractions. The maximum number of digits can be specified in the size parameter. The maximum number of digits to the right of the decimal is specified in the d parameter

 

Text	Description
char(size)	Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis
varchar(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis
tinytext	Holds a variable string with a maximum length of 255 characters
text blob	Holds a variable string with a maximum length of 65535 characters
mediumtext mediumblob	Holds a variable string with a maximum length of 16777215 characters
longtext longblob	Holds a variable string with a maximum length of 4294967295 characters

 

Date	Description
date(yyyy-mm-dd) datetime(yyyy-mm-dd hh:mm:ss) timestamp(yyyymmddhhmmss) time(hh:mm:ss)	Holds date and/or time

 

Misc	Description
enum(value1,value2,ect)	ENUM is short for ENUMERATED list. Can store one of up to 65535 values listed within the ( ) brackets. If a value is inserted that is not in the list, a blank value will be inserted
set	SET is similar to ENUM. However, SET can have up to 64 list items and can store more than one choice

 

Primary Key and Auto increment

Each table should have an unique identifier field. This field is called a primary key.

The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting.

AUTO_INCREMENT adds 1 to the value of the field each time a new record is added.

To make sure that no primary key fields can be NULL, we can add the NOT NULL setting to the field.

Example

$sql = "CREATE TABLE Person  ( personID int NOT NULL AUTO_INCREMENT,  PRIMARY KEY(personID), FirstName varchar(15), LastName varchar(15), Age int )"; mysql_query($sql,$con);

 

The INSERT INTO statement is used to insert new records to a database table.

 

The MySQL INSERT INTO Statement

In this chapter you will learn how to insert data in a database table.

We will also show you how to insert data to a database table from an HTML form.

This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.

 

Insert Data Into a Database Table

The INSERT INTO statement is used to insert data in a database table.

MySQL Syntax

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

You can also specify the columns where you want to insert the data:

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

We must add the INSERT INTO statement to the mysql_query() function to execute the command.

Example

In the previous chapter we created a table named "Person", with three columns; "Firstname", "Lastname" and "Age". We will use the same database in this example. The following example inserts two new records to the "Person" table:

mysql_select_db("my_db", $con); mysql_query("INSERT INTO person  (firstname, lastname, age)  VALUES ('Peter', 'Griffin', '35')"); mysql_query("INSERT INTO person  (firstname, lastname, age)  VALUES ('Glenn', 'Quagmire', '33')");

 

Insert Data from a Form Into a Database

Now we will create an HTML form that can be used to insert data in the "Person" table.

Here is the HTML form:

<html> <body> <form action="insert.php" method="post"> Firstname: <input type="text" name="firstname" /> Lastname: <input type="text" name="lastname" /> Age: <input type="text" name="age" /> <input type="submit" /> </form> </body> </html>

Then, here is the code in the "insert.php" page:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } mysql_select_db("my_db", $con); $sql="INSERT INTO person (firstname,lastname,age) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')"; if (!mysql_query($sql,$con))   {   die('Error: ' . mysql_error());   } echo "1 record added"; mysql_close($con) ?>

How does it work?

·        When a user clicks the submit button in the HTML form, the form data is sent to "insert.php"

·        "insert.php" connects to a database

·        The PHP $_POST variable is used to retrieve the values from the form

·        The mysql_query() function inserts the data in the database table

The SELECT statement is used to select data from a database.

 

The MySQL SELECT Statement

In this chapter you will learn how to select data from a database.

This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.

The SELECT statement is used to select data from a database.

MySQL Syntax

SELECT column_name(s) FROM table_name

Note: MySQL statements are not case sensitive. SELECT is the same as select.

Example

The following example selects the data stored in the "Person" table. We use the * character to select all of the data in the table:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } mysql_select_db("my_db", $con); $result = mysql_query("SELECT * FROM person"); while($row = mysql_fetch_array($result))   {   echo $row['FirstName'] . " " . $row['LastName'];   echo "<br />";   } mysql_close($con); ?>

The output of the code above will be:

Peter Griffin Glenn Quagmire

How does it work?

·        The data returned by the mysql_query() function is stored in the $result variable

·        The mysql_fetch_array() function returns a row from a recordset as an array. Each subsequent call to mysql_fetch_array() returns the next row in the recordset

·        The while loop loops through all the records in the result-set

·        To print the value of certain rows, we use the PHP $row variable ($row['FirstName'] and $row['LastName'])

To select only data that matches a specified criteria, add a WHERE clause to the SELECT statement.

 

The MySQL WHERE clause

In this chapter you will learn how to select only data from a database that matches a specified criteria.

This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.

To select only data that matches a specified criteria, we add a WHERE clause to the SELECT statement.

MySQL Syntax

SELECT column FROM table WHERE column operator value

The following operators can be used with the WHERE clause:

Operator	Description
=	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

Example

The following example will select all rows from the "Person" table, where FirstName='Peter':

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   }  mysql_select_db("my_db", $con);  $result = mysql_query("SELECT * FROM person WHERE FirstName='Peter'");  while($row = mysql_fetch_array($result))   {   echo $row['FirstName'] . " " . $row['LastName'];   echo "<br />";   }  ?>

The output of the code above will be:

Peter Griffin

 

The ORDER BY keyword is used to sort the data in a result-set.

 

The MySQL ORDER BY Keyword

The ORDER BY keyword is used to sort the data in a result-set.

MySQL Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name

Example

The following example selects the data stored in the "Person" table and sort the result by age:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   }  mysql_select_db("my_db", $con);  $result = mysql_query("SELECT * FROM person ORDER BY age");  while($row = mysql_fetch_array($result))   {   echo $row['FirstName']   echo " " . $row['LastName'];   echo " " . $row['Age'];   echo "<br />";   } mysql_close($con); ?>

The output of the code above will be:

Glenn Quagmire 33 Peter Griffin 35

 

Ascending or Descending

It is possible to order by more than one column.

You can also specify if the sort-order should be ascending (this is default. 1 before 9 and "a" before "p") or descending (9 before 1 and "p" before "a").

Order by two columns

When ordering by more than one column, the second column is only used if the values in the first column are identical:

SELECT column_name(s) FROM table_name ORDER BY column_name, column_name

Descending Order

SELECT column_name(s) FROM table_name ORDER BY column_name DESC

 

Display Result in an HTML Table

This code gets the same result as the example above, but displays the data in an HTML table:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   }  mysql_select_db("my_db", $con);  $result = mysql_query("SELECT * FROM person ORDER BY age");  echo "<table border='1'> <tr> <th>Firstname</th> <th>Lastname</th> <th>Age</th> </tr>"; while($row = mysql_fetch_array($result))   {   echo "<tr><td>";   echo $row['FirstName'];   echo "</td><td>";   echo $row['LastName'];   echo "</td><td>";   echo $row['Age'];   echo "</td></tr>";   } echo "</table>"; mysql_close($con); ?>

The output of the code above will be:

Firstname	Lastname	Age
Glenn	Quagmire	33
Peter	Griffin	35

 

The UPDATE statement is used to modify data in a database table.

 

The MySQL UPDATE Statement

In this chapter you will learn how to modify data in a database table.

This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.

 

Update Data In a Database

The UPDATE statement is used to modify data in a database table.

MySQL Syntax

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

We must add the UPDATE statement to the mysql_query() function to execute the command.

Earlier in the tutorial we created a table named "Person". Here is how it looks:

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

Example

The following example updates some data in the "Person" table:

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } mysql_select_db("my_db", $con);  mysql_query("UPDATE Person SET Age = '36' WHERE FirstName = 'Peter' AND LastName = 'Griffin'"); mysql_close($con); ?>

After the update, the "Person" table will look like this:

FirstName	LastName	Age
Peter	Griffin	36
Glenn	Quagmire	33

 

The DELETE FROM statement is used delete rows from a database table

 

The MySQL DELETE FROM Statement

In this chapter you will learn how to delete records from a database table.

This is also done by using the mysql_query() function. This function is used to send a query or command to a MySQL connection.

 

Delete Data In a Database

The DELETE FROM statement is used to delete records from a database table.

MySQL Syntax

DELETE FROM table_name WHERE column_name = some_value

We must add the DELETE FROM statement to the mysql_query() function to execute the command.

Earlier in the tutorial we created a table named "Person". Here is how it looks:

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

Example

The following example deletes all records in the "Person" table where LastName='Griffin':

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con)   {   die('Could not connect: ' . mysql_error());   } mysql_select_db("my_db", $con);  mysql_query("DELETE FROM Person WHERE LastName = 'Griffin'"); mysql_close($con); ?>

After the delete, the table will look like this:

FirstName	LastName	Age
Glenn	Quagmire	33

 

ODBC is an Application Programming Interface (API) that allows you to connect to a data source (e.g. an MS Access database).

 

Create an ODBC Connection

With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.

Here is how to create an ODBC connection to a MS Access Database:

Open the Administrative Tools icon in your Control Panel.

Double-click on the Data Sources (ODBC) icon inside.

Choose the System DSN tab.

Click on Add in the System DSN tab.

Select the Microsoft Access Driver. Click Finish.

In the next screen, click Select to locate the database.

Give the database a Data Source Name (DSN).

Click OK.

Note that this configuration has to be done on the computer where your web site is located. If you are running Internet Information Server (IIS) on your own computer, the instructions above will work, but if your web site is located on a remote server, you have to have physical access to that server, or ask your web host to to set up a DSN for you to use.

 

Connecting to an ODBC

The odbc_connect() function is used to connect to an ODBC data source. The function takes four parameters: the data source name, username, password, and an optional cursor type.

The odbc_exec() function is used to execute an SQL statement.

Example

The following example creates a connection to a DSN called northwind, with no username and no password. It then creates an SQL and executes it:

$conn=odbc_connect('northwind','',''); $sql="SELECT * FROM customers";  $rs=odbc_exec($conn,$sql);

 

Retrieving Records

The odbc_fetch_rows() function is used to return records from the result-set. This function returns true if it is able to return rows, otherwise false.

The function takes two parameters: the ODBC result identifier and an optional row number:

odbc_fetch_row($rs)

 

Retrieving Fields from a Record

The odbc_result() function is used to read fields from a record. This function takes two parameters: the ODBC result identifier and a field number or name.

The code line below returns the value of the first field from the record:

$compname=odbc_result($rs,1);

The code line below returns the value of a field called "CompanyName":

$compname=odbc_result($rs,"CompanyName");

 

Closing an ODBC Connection

The odbc_close() function is used to close an ODBC connection.

odbc_close($conn);

 

An ODBC Example

The following example shows how to first create a database connection, then a result-set, and then display the data in an HTML table.

<html> <body> <?php $conn=odbc_connect('northwind','',''); if (!$conn)   {exit("Connection Failed: " . $conn);} $sql="SELECT * FROM customers"; $rs=odbc_exec($conn,$sql); if (!$rs)   {exit("Error in SQL");} echo "<table><tr>"; echo "<th>Companyname</th>"; echo "<th>Contactname</th></tr>"; while (odbc_fetch_row($rs)) {   $compname=odbc_result($rs,"CompanyName");   $conname=odbc_result($rs,"ContactName");   echo "<tr><td>$compname</td>";   echo "<td>$conname</td></tr>"; } odbc_close($conn); echo "</table>"; ?> </body> </html>