Home | Store | osCommerce Tutorials

How to Use PHP Connect to osCommerce MySQL Server

The first step to use osCommerce database is to connect with MySQL server. This is quite similar to a phone call. If you wish to talk with your friend on the phone, the first step is to pick up your phone, dial the number and connect with the phone of your friend.

PHP use mysql_connect() function to open or create a connection to a MySQL server as shown below:

<?php

// create connection
$link = mysql_connect (DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);

?>

Actually I used to use $connection instead of $link. Since we are talking about osCommerce, therefore we try to use the variables and names using in osCommerce.

You should noticed that three parameters are required for the mysql_connect function, they are:

  • DB_SERVER

    DB_SERVER is the name of MySQL server. In most cases "localhost" is used for DB_SERVER since the MySQL server is usually installed with the web server. Some hosting provider like GoDaddy is using something like "p50mysqlxxx.secureserver.net" for the name of MySQL server. Ask your hosting provider if the server name is not using "localhost".

  • DB_SERVER_USERNAME

    DB_SERVER_USERNAME is the username to connect with the MySQL server. Create a new username and password if you forgot or do not know the username. With phpMyAdmin, this is very easy to create a new username with password.

  • DB_SERVER_PASSWORD

    DB_SERVER_PASSWORD is the password to connect with the MySQL server. Create a new username and password if you forgot or do not know the username. With phpMyAdmin, this is very easy to create a new username with password.

Now you should get some basic knowledge of PHP and MySQL. It's time to try to use PHP to connect with the MySQL server.

With mysql_connect() function, this is very easy and simple to connect to a MySQL server. However there are some ways to use mysql_connect() function to connect with MySQL server. Let's discuss one by one:

PHP Connect with MySQL Server Method 1:

<?php

// create connection
$link = mysql_connect("localhost", "osc_admin", "osc1234") or die ("Couldn't connect to the server.");

// Other codes here

// free resources and close connection
mysql_close($link);

?>

In this example, if the MySQL server name, username and password is correct, the connection will be made successfully. Then the connection is close at the end. Although the connection will be closed at the end of execution of the script, this is a good practice to close the MySQL server connection at the end.

However if the information is not connect, the connection will be closed immediately and the following warning may appear on the screen:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'osc_admin'@'localhost' (using password: YES) in /home/oscommerce/catalog/dbconnect.php on line 4
Couldn't connect to the server.

If you are interested in the value of connection ($link) and would like to see some welcome message, the codes can be modified as below:

<?php

// create connection
$link = mysql_connect("localhost", "osc_admin", "osc1234") or die ("Couldn't connect to the server.");

echo "<p>The connection to MySQL server is successfully!</p>";
echo 'The value of $connection is: ' . $connection;

// Other codes here

// free resources and close connection
mysql_close($link);

?>

If the connection is successful, the following output should appear on the screen:

The connection to MySQL server is successfully!

The value of $connection is: Resource id #1

The Resource id #1 is called the MySQL resource variable.

Let's see another method to connect to MySQL server.

PHP Connect with MySQL Server Method 2:

<?php

// create connection
$link = mysql_connect("localhost", "osc_admin", "osc1234");

if (!$link) {
die ("Couldn't connect to the server.");
}

echo "<p>The connection to MySQL server is successfully!</p>";
echo 'The value of $connection is: ' . $connection;

// Other codes here

// free resources and close connection
mysql_close($link);

?>

In this method, a connection is made to MySQL server with mysql_connect() function. If the value return is empty (i.e. connection fail) then the connection will be closed immediately and the following warning may appear on the screen:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'osc_admin'@'localhost' (using password: YES) in /home/oscommerce/catalog/dbconnect.php on line 4
Couldn't connect to the server.

Otherwisethe connection should be successful and the following output will be shown on the screen:

The connection to MySQL server is successfully!

The value of $connection is: Resource id #1

PHP Connect with MySQL erver Method 3:

<?php

$server = "localhost";
$username = "osc_admin";
$password = "osc1234;

// create connection
$link = mysql_connect($server, $username, $password);

echo "<p>The connection to MySQL server is successfully!</p>";
echo 'The value of $connection is: ' . $connection;

// Other codes here

// free resources and close connection
mysql_close($link);

?>

The output result should be same as the above example.

PHP Connect with MySQL Server Method 4:

In this method, we use PHP function connect with MySQL server similar to that using in osCommerce shop.

If you ever opened the application_top.php osCommerce PHP file in the includes folder and check the PHP codes of osCommerce, you should came across the following lines (line 66 - 67):

// make a connection to the database... now
tep_db_connect() or die('Unable to connect to database server!');

You already learned the PHP function in previous PHP tutorial, you should know that the tep_db_connect() is a custom PHP function. Moreover you should know that this function is used to connect to MySQL database. If you open the database.php in the includes/function folder, you should find the tep_db_connect() function.

Let's use a similar method to connect to MySQL server:

<?php

function tep_db_connect($server = "localhost", $username = "osc_admin", $password = "osc1234") {

$link = mysql_connect($server, $username, $password);

if (!$link) {
die("Couldn't connect to the server.");
}

echo "<p>The connection to MySQL server is also successfully!</p>";
echo 'The value of $connection is: ' . $link;

// free resources and close connection
mysql_close($link);

}

// make a connection to the database... now
// The arguments have default values. So no values are required to send into the function when called
tep_db_connect() or die('Unable to connect to database server!');

?>

Note:

  • The values assigned to the function parameters (e.g. $server="localhost", $username="osc_admin" and $password="osc1234") is called default values. If no values are sent into the function when called, the defualt values will be used.

Run the above PHP codes. The connection to MySQL server should be successful if the information is correct.

Although the connection to MySQL server is successfully, the "Unable to connect to database server!" will also be printed out as below:

The connection to MySQL server is successfully!

The value of $connection is: Resource id #1Unable to connect to database server!

Why?

Because the $link variable is a local variable inside the tep_db_connect() function, hence the value of $link (local scope) cannot be passed to the main code (global scope).

To solve the problem, declare the $link variable inside the tep_db_connect() function with the global keyword and return the value back to the main code. The final codes are now:

<?php

function tep_db_connect($server = "localhost", $username = "osc_admin", $password = "osc1234") {

global $link;

$link = mysql_connect($server, $username, $password);

if (!$link) {
die("Couldn't connect to the server.");
}

echo "<p>The connection to MySQL server is also successfully!</p>";
echo 'The value of $connection is: ' . $link;

// free resources and close connection
mysql_close($link);

return $link

}

// make a connection to the database... now
// The arguments have default values. So no values are required to send into the function when called
tep_db_connect() or die('Unable to connect to database server!');

?>

PHP example filephp-connect-mysql-server-example-4-1.zip

Run the codes again. If the connection is successful, the following output should appear on the screen:

The connection to MySQL server is successfully!

The value of $connection is: Resource id #1

Since the $link variable is global, the mysql_close($link) can be moved to the main codes. This can make the codes of function shorter. The codes becomes:

<?php

function tep_db_connect($server = "localhost", $username = "osc_admin", $password = "osc1234") {

global $link;

$link = mysql_connect($server, $username, $password);

if (!$link) {
die("Couldn't connect to the server.");
}

echo "<p>The connection to MySQL server is also successfully!</p>";

return $link

}

// make a connection to the database... now
// The arguments have default values. So no values are required to send into the function when called
tep_db_connect() or die('Unable to connect to database server!');

echo 'The value of $connection is: ' . $link;

// free resources and close connection
mysql_close($link);

?>

In fact using the mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. Actually I noticed that osCommerce do not using mysql_close() to close the connection although the tep_db_close() custom function do exist in the incluse/functions/database.php.

The PHP codes may then becomes:

<?php

function tep_db_connect($server = "localhost", $username = "osc_admin", $password = "osc1234") {

global $link;

$link = mysql_connect($server, $username, $password);

if (!$link) {
die("Couldn't connect to the server.");
}

echo "<p>The connection to MySQL server is also successfully!</p>";

return $link

}

// make a connection to the database... now
// The arguments have default values. So no values are required to send into the function when called
tep_db_connect() or die('Unable to connect to database server!');

?>

PHP example filephp-connect-mysql-server-example-4-2.zip

Now you know how to use PHP connect to osCommerce MySQL Server.