How to Connect to a MySQL Database from PHP (version lower than PHP 5.0.0)

Update:

PHP's MySQL related code (a.k.a. PHP MySQL extension) on this page only works for PHP version lower than version 5. If your PHP is version 5 or later, please use MySQLi extension which was introduced with PHP version 5.0.0. To see how to connect to MySQL by using PHP version 5 or later, click here.

To build a MySQL database-driven web application in PHP, the very first thing you need to do is connect to a MySQL database. With only 6 lines of code, you can easily kick start your dynamic website development.

Code snippet 1: northwind-categories.php


	
<?

$db_host = "localhost";

$db_name = "northwind";

$db_user = "root";

$db_pass = "your_password";



$db_link = mysql_connect($db_host, $db_user, $db_pass) or die("Could not connect to database server"); 

mysql_select_db($db_name, $db_link) or die("Could not select database");

?>

As you see above, the code is extremely simple.

$db_host is the name of your MySQL server.

Normally, it's called localhost at the default TCP/IP port 3306. If you have installed MySQL with a different port number (only when you install more than one MySQL Server on the same machine), you need to add that information to $db_host like this:

$db_host = "localhost:3307";

$db_name is the database name we want to connect to. In our code, we connect to MySQL Northwind database.

$db_user is the database user for the connection. Root user comes with MySQL installation and is the top-level user. It's like sa user in SQL Server. In a production environment, you should create a new user to connect to your MySQL database.

$db_pass is the password for user root.

The second last line in the code opens the connection to MySQL server and return MySQL link identifier.

The last line in the code selects the database Northwind.

Display categories table

Next, we are going to select some data from our Northwind database and display the data on a web page.

Code snippet 2: northwind-categories.php


	
<?

// Here we select all records from categories table.

$sql = "select CategoryID, CategoryName from categories";

$result = mysql_query($sql, $db_link);



// Loop thru the record set and concatenate a TR string for HTML table.

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

    $cat_id = $row["CategoryID"];

    $cat_name = $row["CategoryName"];

    

    $tr .= "<tr><td>". $cat_id. "</td>".

        "<td>". $cat_name. "</td></tr>";

}

?>



<p>

<table cellpadding=2 cellspacing=0 border=1>

<tr>

    <th><strong>Category ID</strong></th>

    <th><strong>Category Name</strong></th>

</tr>

<?=$tr?>

</table></p>

Now create a new PHP page and name it as northwind-categories.php. Copy and paste the above two code snippets into it. View the page in your web browser. If you can see the categories table displayed as shown below, you have successfully connected to MySQL and selected data from the table.

northwind-categories.php displayed as:
Use PHP and MySQL to display Northwind categories table on a web page

Here is the complete PHP source code for northwind-categories.php page.

Two common problems when connecting to MySQL Server

Problem #1: Only a blank page shows

When you view the page from web browser and a blank page is displayed, this is because there are errors that are caused by either PHP or MySQL but your PHP setup does not allow errors to be displayed. To display all errors, add the following line to the top of your PHP page.

ini_set("display_errors", true);

ini_set is a PHP function that re-sets a PHP configuration option on the fly. The page will only keep this new configuration value during the page's execution, and will be restored to the default value when the page is finished loading. That means, it does not affect any other PHP pages on your site.

Here the ini_set forces this PHP page to display all errors and warnings. You should pay attention to errors and ignore any warnings.

Problem #2: MySQL error - Client does not support authentication protocol

If you see the error such as below, it is an incompatibility problem for old MySQL client.

Warning: mysql_connect() [function.mysql-connect]: Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:\test\char-set.php on line 30 Could not connect to database server

MySQL documentation says:

MySQL 4.1+ uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.0, attempts to connect to it with an older client may fail with the following message:

The easiest way to fix this problem is reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement with the OLD_PASSWORD() function.

Run the following SQL in SQLyog:

USE mysql;
SET PASSWORD FOR 'root'@'localhost' = OLD_PASSWORD('your_password');

Change your_password to your MySQL password for user root. After you run this command, view your PHP page again, the error should be gone.

Four steps to fine-tune your PHP code to connect to MySQL

We can define constants, functions, classes, and use include files to increase code reusability and portability. Here are 4 steps to fine-tune our code.

Step #1: Use PHP named constants for MySQL connection configuration data.

The first code snippet shown above can be recoded by using PHP named constants. Note that, it's a good idea to use upper-cases for PHP constants so that they can be easily identified when we work thru our code.

config.php


	
<?

define('DB_HOST', 'localhost');

define('DB_NAME', 'northwind');

define('DB_USER', 'root');

define('DB_PASS', 'your_password');

?>

Step #2: Create an include file (e.g. config.php) and add the named constants to it.

In the include file, add the named constants.

Step #3: Create a function to connect to MySQL and put this function in an include file (e.g. functions.php).

functions.php


	
<?

// This connection allows your application to be used for multi-lingual websites.

function _connect_to_mysql($is_utf8 = null)

{

    $mysql_link = mysql_connect(DB_HOST, DB_USER, DB_PASS) 

              or die("Could not connect to database server"); 

    mysql_select_db(DB_NAME, $mysql_link) 

              or die("Could not select database"); 

        

    if (is_null($is_utf8))

    {

        /* This sets collation for the connection to utf8_general_ci 

        because it is the default collation for utf8. 

        This enables multi-lingual capability in database.

        */

        mysql_query("SET NAMES 'utf8'");

    }

    return $mysql_link;

}

?>

Step #4: Modify the code in northwind-categories.php. Your code should look like this:

northwind-categories.php


	
<?

include("config.php");

include("functions.php");



$db_link = _connect_to_mysql();



// Here we select all records from categories table.

$sql = "select CategoryID, CategoryName from categories";

$result = mysql_query($sql, $db_link);



// Loop thru the record set and concatenate a TR string for HTML table.

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

    $cat_id = $row["CategoryID"];

    $cat_name = $row["CategoryName"];

    

    $tr .= "<tr><td>". $cat_id. "</td>".

        "<td>". $cat_name. "</td></tr>";

}

?>



<p>

<table cellpadding=2 cellspacing=0 border=1>

<tr>

    <th><strong>Category ID</strong></th>

    <th><strong>Category Name</strong></th>

</tr>

<?=$tr?>

</table></p>

Now, view northwind-categories.php again in your web browser and you should see the exact categories data as that displayed by our old northwind-categories.php page. Our new code is much more modularized and easily maintainable, even though no PHP classes are used so far.

Copyright© GeeksEngine.com

Related Articles:

1.	Connect to a MySQL Database from PHP version 5 or later versions
2.	How to set up DSN in ODBC Data Source Administrator on Windows

Other Recent Articles from the Web Development category:

1.	Connect to a MySQL Database from PHP version 5 or later versions
2.	Java / JSP lost session value on redirect - FIXED
3.	Fix the problem with PHP5 XML removeChild() method
4.	How to integrate PHP HTML Help .chm file with Crimson Editor
5.	Use MySQL String Functions to Build Printable ASCII Character Chart
6.	Five ways to create include path for PHP
7.	How to use Date and Time data as integer value in PHP and MySQL
8.	Absolute Path and Relative Path Explained

	Custom Search