PHP Guide 6: How to Connect to a MySQL Database

A guide to connecting to a MySQL database in PHP and displaying results in an efficient manner.

Index of Contents

The code

Here is the basic PHP code you will need to know to connect to a database and query a table.

<?php

// Connect to MySQL Server
// You will need the server location, username, and password for the server connection
mysql_connect('server', 'username', 'password');

// Connect to MySQL Database
// Once the server connection is established, connect to a specific database
mysql_select_db('database');

// A simple SELECT query on the database
// Here we pull the name, type and year columns from the "mytable" table
mysql_query('SELECT name, type, year FROM mytable');

// We could also select all columns by using *
mysql_query('SELECT * FROM mytable');

// In order to use the data from the mysql_query, we should assign it to a variable
$query = mysql_query('SELECT * FROM mytable');

?>

Real example

Here is a simple web page that connects to a database, queries it, and displays the results in a table.

<?php
mysql_connect('server', 'username', 'password');
mysql_select_db('database');

// Here I select the name, type and year columns again
// This time I am going to order the results by name ascending (use DESC for descending)
$query = mysql_query('SELECT name, type, year FROM mytable ORDER BY name ASC');
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>My Database Connection</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="description" content="My Database Connection">
<meta name="keywords" content="mysql, database, connect, php">
</head>
<body>

<table>
<tr>
<th>Name</th>
<th>Type</th>
<th>Year</th>
</tr>

<?php

// We cycle through the database
// Use the mysql_num_rows function to return the number of rows found in the query
for($i=1;$i<=mysql_num_rows($query);$i++)
{
      echo '<tr>';

      // We assign each row in the results to the $row variable.
      // Every time the mysql_fetch_array function is called, it moves down to the next row.
      // So each time we loop through this bit of code and call the mysql_fetch_array function,
      // we move down a row in the results automatically!
      $row = mysql_fetch_array($query);

      // To pull the data from the row, we use $row[data], where data is the name of the column
      // in the MySQL database (do not use quotes around the column names)
      echo '<td>' . $row[name] . '</td>';
      echo '<td>' . $row[type] . '</td>';
      echo '<td>' . $row[year] . '</td>';

      echo '</tr>';
}
?>

</table>

</body>
</html>

In a function

For a cleaner HTML and code separation, we could create a function.

<?php
function drawresultstable()
{
      mysql_connect('server', 'username', 'password');
      mysql_select_db('database');
      $query = mysql_query('SELECT name, type, year FROM mytable ORDER BY name ASC');
      echo '<table><tr><th>Name</th><th>Type</th><th>Year</th></tr>';
      for($i=1;$i<=mysql_num_rows($query);$i++)
      {
            echo '<tr>';
            $row = mysql_fetch_array($query);
            echo '<td>' . $row[name] . '</td>';
            echo '<td>' . $row[type] . '</td>';
            echo '<td>' . $row[year] . '</td>';
            echo '</tr>';
      }
      echo '</table>';
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>My Database Connection</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="description" content="My Database Connection">
<meta name="keywords" content="mysql, database, connect, php">
</head>
<body>

<?php drawresultstable(); ?>

</body>
</html>

This function could then be placed in an external PHP file and used on many different pages.

<?php require('inc_functions.php'); ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>My Database Connection</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="description" content="My Database Connection">
<meta name="keywords" content="mysql, database, connect, php">
</head>
<body>

<?php drawresultstable(); ?>

</body>
</html>

Guides, Tutorials and How-To's