Search  
Resources & Services: Computing

Connecting to a MySQL Database

Here are some basic instructions on using PHP to connect to a MySQL database.

There are two techniques for adding the connection information to a MySQL database in to a php page. The first is to include the following information in a external file - such as connections_doc.php. This technique is not only more efficient (allows you to use the same script in each page as needed) but is also allows you to make the file more secure (as it contains a MySQL username and password. The other technique is to simply include this script in each page that must connect to MySQL.

Include the following either in a separate php file or at the top of your page:

 
<?php
# Type="MYSQL"
# HTTP="true"
$hostname_var = "localhost";
$database_var = "Your Database Name";
$username_var = "username";
$password_var = "password";
$YOURdb = mysql_pconnect($hostname_var, $username_var, $password_var) or die(mysql_error());
?>

Notes

  • Your Database Name must be set to the name of the database you wish to connect to.
  • username: place your MySQL login name here.
  • passoword: place your MySQL login password here.
  • The $YOURdb variable identifies which database connection you are going to use in a given query. Make this variable name meaningful to help identify the connection you are using.

If you have chosen to place the preceding script in a separate file you will need to make sure this file is included in the main page. Add this php statement at the top of the page

<?php require_once("path/to/your/connection_doc.php"); ?>

You have now loaded the necessary information into variables to allow you to connect to the database. The next step is to send a query to the database. Here is a sample of a query request:

 
<?php mysql_select_db($database_var, $YOURdb);
$query_YourRecordset = "SELECT * FROM yourtable";
$YourRecordset = mysql_query($query_YourRecordset, $DBName) or die(mysql_error());
$row_YourRecordset = mysql_fetch_assoc($YourRecordset);
$totalRows_YourRecordset = mysql_num_rows($YourRecordset); ?>

Notes:

  • The $YOURdb variable must be the same as in your connection document section above
  • The YourRecordset labeling with in the variables above must be unique for every query you perform within a page. Make this label meaningful to help remember the data set.
  • $DBName is the name of the database you are performing the query on.
  • SELECT * FROM yourtable is the place where you place your SQL query statement.

 

This query will bring back all the records requested from the SQL query statement and store them in the array $row_yourrecordset [ ]

Now that the query has been performed, you may wish to display some of the data you have retrieved. If your query returned only one row of records you could display the results by including:

<?php echo $row_YourRecordset['fieldname']; ?>

where ever you wish the data to appear.

If your query returns more than one row of records then you will need to perform a do/while loop to get all the records to display.

 
<?php do { ?>
<p>
<?php echo $row_YourRecordset['field1']; ?>
<?php echo $row_YourRecordset['field2']; ?>
<?php echo $row_YourRecordset['field3']; ?>
<?php echo $row_YourRecordset['field4']; ?>
</p>
<?php } while ($row_YourRecordset = mysql_fetch_assoc($YourRecordset)); ?>

Notes:

This piece of code tells the page to display fields 1-4 from row 1, then to create a new paragraph and display fields 1-4 of row 2, and so on until all records have been displayed. You can add additional html markup with in the do {} while section to provide more formatting of the text. Remember every item in the do {} while loop will be repeated for every record retrieved.

Search