Within PHP there will be a time when you want to access a MySQL database to store or retrieve data. MySQL is the most commonly used Data Source for PHP, so we will take a look at how we connect to the MySQL database and how to use the data.

First we need to make the initial connection. PHP has pre-defined functions to do this. We first call the MySQL Connection function.

 
<?php
 
$connection = mysql_connect( "localhost", "username", "password" );
 
?>
 

In the code above we have started the initial connection to the MySQL database and provided it with 3 values.

1. Hostname/IP of the MySQL server
2. Username of the Database
3. Password of the Database

Next we must specify which database we want to use. We do this as follows:

 
<?php
 
$connection = mysql_connect( "localhost", "username", "password" ); // Our code from before.
 
$database = mysql_select_db( "database", $connection );
 
?>
 

The code above is selecting the database and including the first connection. After this we should be connected to our MySQL database.

However we should do some checking before we try and use it. For this we can just use a simple if statement.

 
<?php
 
$connection = mysql_connect( "localhost", "username", "password" ); // Our code from before.
 
$database = mysql_select_db( "database", $connection ); // Our code from before.
 
if( !$database )
{
      // There is no connection
}
else
{
      // We have a connection.
}
 
?>
 

So from the whole code above, we can see if we have a connection or not. So we can then use our database connection if we have one or do some error reporting if not.

Now we have a connection we should try and get some data from the database. Lets see how we start a query.

 
$query = mysql_query( "SELECT * FROM myTable" );
 

Above is a query that PHP will run against our MySQL connection. We are storing the output into the $query variable. We then use a function called mysql_query and in this we put our query string.
the $query variable will now hold all of the returned data from MySQL. Now we need to extract the data from this variable.

If you are only returning 1 row, you can simply fetch the data with one function:

 
$row = mysql_fetch_array( $query );
 

The above code will only retrieve one row, if there is multiple rows in the result, it will only return the first. To do this we use the mysql_fetch_array() function and provide it with our query variable.

The $row variable will now be an array of the data returned by column name. For example:

 
$firstName = $row['firstname'];
$lastName = $row['lastname'];
 

Put our whole code together and you have:

 
<?php
 
# Our Connection.
$connection = mysql_connect( "localhost", "username", "password" );
$database = mysql_select_db( "database", $connection );
 
# Check our connection.
if( !$database )
{
      echo "No Connection!";
}
else
{
      echo "We have a Connection!";
}
 
# Our query.
$query = mysql_query( "SELECT * FROM myTable" );
 
# Get our results.
$row = mysql_fetch_array( $query );
 
# Use our results.
echo $row['firstname'];
 
?>
 

Many queries will return more than one row of data. To read this we use a while loop. This is a normal while up but using the mysql_fetch_array().

 
# Our query.
$query = mysql_query( "SELECT * FROM myTable" );
 
while( $row = mysql_fetch_array( $query ) )
{
     $firstName = $row['firstname'];
}
 

So we can see our while loop, and it will run while there are more rows to read. It will stop when it reaches the last row. For every time it is a new row it sets the $row variable as the data for that row and you can use the data as before.

How you now use your data is up to you.

For putting data into the database or updating it we simply use a standard query, we don’t need to read it.

 
$query = mysql_query( "INSERT INTO myTable VALUES( '1', '2' )" );
 

When running queries that return no results it is good practice to store it in a variable anyway to do error checking to see if the query executed successfully or not.

After you have used your database and no longer need it (mainly at the end of your php page) you should close the MySQL connection using mysql_close() using your connection variable you set, in this instance it is $connection.

 
mysql_close($connection)
 

That’s how to use MySQL databases in PHP!