Database Management

PHP is a server-side language that can be used in conjunction with a database to create dynamic web pages. By the word "dynamic" it allows you to input or query data from a web page. These aren't the only things PHP allows you to do interaction with databases are a key feature of server-side languages.

PHP pages end with the .php extension, as opposed to the usual .html or .htm file normally used for websites. PHP website files are still HTML files (though editing a page's header can change a PHP file to many other file types), with the exeption that the server processes any PHP code before serving the file to you. In contrast, any HTML on the page is sent as code and rendered locally by a user's internet browser.

PHP code is nested within other files always surrounded by a PHP tag:

<?php  //Enter PHP code here  ?>

The code syntax in PHP has some similarities to .NET languages, especially C#. PHP variables are defined without explicitly defining their type, however, and dollar signs ($) are used to denote use of a variable. There are many other distinct differences in PHP syntax, as well.

Downloading PHP and MySQL[]

Follow the following links below to install PHP or MySQL on your local machine or server. Instructions for installation are shown on these pages depending on the platform you are installing on.

1. MySQL database - 2. PHP -

After installing PHP and MySQL, remember your username and password, along with the name of the database you want to connect to.

To test your PHP configuration, enter this code into a new PHP page:

<?php phpinfo(); ?>

The phpinfo() function allows you to determine certain settings and properties of your PHP server. It is often a good way to test a PHP server to ensure that it is working correctly.

Establishing a connection to a MySQL database using PHP[]

MySQL is the largest free open-source database offered. Using PHP and mySQL together allows you to create dynamic, content-driven websites.

Any information that requires a manual update to display, such as news, events, user accounts, and more, are much better than static methods. For the latter, database interaction is required. Information retrieved from a database through PHP can be outputted in any manner that could be done with static HTML.

PHP offers a mysql_connect() function to connect to MySQL databases. This function takes three values: the name of the database you wish to connect to, your username, and your password.

$con = mysql_connect('test_database', 'test_user', 'test_pass')
    or die ('Error connecting to database.');

This function tries to connect to test_database with the username of test_user and a password of test_pass. If the connection fails, the die() function is called, which stops any more code from being processed, and gives the end user an error message.

It is important to note that data can be passed through functions directly or referenced through variables. For the previous example, variables could have been defined prior to using the function and passed into the function as variables instead.

Extracting data from a database[]

In order to extract data from the database, there must tables and fields that are populated within it. For our example, we have a table named PLAYERS. Within this table, have 4 columns (first_name, last_name, jersery_number, and position).

first_name last_name jersey_number position
Example PLAYERS table
Carmelo Anthony 15 SF
Kenyon Martin 6 PF
Marcus Camby 23 C
Andre Miller 24 PG
Voshon Leonard 1 SG

To query all of the fields from the PLAYERS table we need an active connection, as outlined in the previous section. Once that connection has been made, you can store SQL SELECT queries into a variable.

$result = mysql_query("SELECT first_name, last_name, jersey_number, position FROM players");

while($row = mysql_fetch_array($result))
    echo "First Name: " . $row['first_name'] . "
" . 
         "Last Name: " . $row['last_name'] . "
" .
         "Jersey Number: " . $row['jersey_number'] . "
" .
         "Position: " . $row['position'] . "


The mysql_query() function executes the SQL and puts the query in a variable called $result. The mysql_fetch_array() function takes the query in $result and stores it in an array The PHP while() function loops through the newly-created array and, for each iteration of the loop, stores the current tuple of the result in a variable called $row. Within the function, the echo() function outputs the data using the $row variable to dynamically insert the database values into the text.

Similar code is used to insert or update tuples in a database. Additionally, PHP can dynamically insert variables into any SQL statement. This is incredibly useful, but it can cause concerns such as SQL injection attacks.

Closing a MySQL Connection in PHP[]

In PHP, if you do not close a connection, it will be closed automatically when the script ends. The php mysql_close() function closes a database connection early if necessary. The variable name used to connect to the database is normally required to close it.

Just as the following code connects to a database:

  $con = mysql_connect('test_database', 'test_user', 'test_pass')
    or die ('Error connecting to database.');

The following code closes it: