Home | Store | osCommerce Tutorials

PHP Select osCommerce MySQL Database

Once the osCommerce MySQL server has been connected with PHP in step 1, the next step is to select the database you are going to work with. The default installation of osCommerce shop has only one database.

PHP use mysql_select_db function to select a MySQL database as shown below:

<?php

// select database
mysql_select_db(Database Name, $link) or die ("Couldn't select database.");

?>

The $link which is obtained when connect successfully with MySQL server is now act as a handle (Resource Link Identifier) in the process.

Let's continue with step 1 and select the osCommerce database:

PHP Select MySQL Database Example 1:

<?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 "<br /><br />";

// select database
$db = mysql_select_db("oscommerce", $link) or die ("Couldn't select database.");

//Let's see what is the value of $db
echo 'The value of db is ' . $db;

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-select-mysql-database-example-1.zip

Run the PHP codes. If the connection is successful, and the database has been selected successfully, the following output should appear on the screen:

The connection to MySQL server is successfully!

The value of $connection is: Resource id #1

The value of db is 1

If you study the PHP codes of includes/functions/database.php tep_db_connect() function, you should noticed that the mysql_select_db() function do not require the Resource ID (line 22). Let's see if we can do the same:

<?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;

echo "<br /><br />";

// select database
$db = mysql_select_db("oscommerce") or die ("Couldn't select database.");

//Let's see what is the value of $db
echo 'The value of db is also ' . $db;

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-select-mysql-database-example-2.zip

Run the codes. If the connection is successful, and the database has been selected successfully, the following output should also appear on the screen:

The connection to MySQL server is successfully!

The value of $connection is: Resource id #1

The value of db is also 1

In other words, with the latest version of PHP (may be from PHP 4.xx) the Resource ID is not required for mysql_select_db() function to select MySQL database.

Up to now, the connection to MySQL server is success and the database is also selected successfully. The rest of the process is simply select the tables and working with the records and data.

Since we are studying the codes of osCommerce, we need to modify our codes a bit to match with the code structures of osCommerce. Let's open includes/functions/database.php and review the the codes of osCommerce how to connect with MySQL server and select database. Clearly the tep_db_connect() function is used to connect with MySQL server and select database. Here's the codes:

<?php

function tep_db_connect ($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {

global $$link;

if (USE_PCONNECT == 'true') {
$$link = mysql_pconnect($server, $username, $password);
} else {
$$link = mysql_connect($server, $username, $password);
}

if ($$link) mysql_select_db($database);

return $$link;

}

?>

The USE_PCONNECT is defined in includes/configure.php line 44. It simply define whether you use persistent connections or not. To simplify our codes study and code structure, we will use mysql_connect() function in this tutorial series. Therefore the above codes becomes:

<?php

function tep_db_connect ($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {

global $$link;

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

if ($$link) mysql_select_db($database);

return $$link;

}

?>

Look! The codes become much easier to read. Actually this is a good way to study codes by eliminating something.

Let's break down the above codes one by one.

  1. The following four parameters were defined in the catalog/configure.php (line 40 - 43):

    - DB_SERVER
    - DB_SERVER_USERNAME
    - DB_SERVER_PASSWORD
    - DB_DATABASE

    Open catalog/configure.php and navigate to line 40 - 43, you should see the following lines:

    // define our database connection
    define('DB_SERVER', 'localhost'); // eg, localhost - should not be empty for productive servers
    define('DB_SERVER_USERNAME', 'osc_admin');
    define('DB_SERVER_PASSWORD', 'Aswjhyf49lrY');
    define('DB_DATABASE', 'osCommerce');

    Of course, the password of DB_SERVER_PASSWORD is encrypted.

  2. The default value of $link is 'db_link'.
  3. Instead of declare global $link, the osCommerce programmer use global $$link. The $$link is called PHP variable variables or dynamic variables which I myself never use for easy codes maintenance and understanding by other colleagues.

    The basic concept of PHP variable variables is:

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

    It is same as:

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

    The PHP variable variables may be the hardest part to understand or may be the hardest part to explain. Don't worry, the above concept is enough to study the codes.

Prepare Your Own PHP Codes

Step 1: Prepare the index.php homepage

Prepare the following lines and save as index.php.

<?php

require('includes/application_top.php');

?>

Step 2: Prepare application_top.php file

The homepage need to include the includes/application.php file. Let's prepare the following lines and save as includes/application_top.php

<?php

// include server parameters
require('includes/configure.php');

// include the database functions
require(DIR_WS_FUNCTIONS . 'database.php');

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

?>

Step 3: Prepare configure.php file

Similarly the application_top.php need to include the includes/configure.php file. Prepare the following lines and save as includes/configure.php

<?php

define('DIR_WS_INCLUDES', 'includes/');
define('DIR_WS_FUNCTIONS', DIR_WS_INCLUDES . 'functions/');

// define our database connection
define('DB_SERVER', 'localhost'); // eg, localhost - should not be empty for productive servers
define('DB_SERVER_USERNAME', 'osc_admin');
define('DB_SERVER_PASSWORD', 'osc1234');
define('DB_DATABASE', 'oscommerce');

?>

Step 4: Prepare the database.php file

The application_top.php also includes the includes/functions/database.php. Prepare the following lines and save as includes/functions/database.php.

<?php

function tep_db_connect ($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') {

global $$link;

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

//Let's do an error checking here (Optional)
if (!$$link) {
die("Couldn't connect to the server.");
}

//Print out something you may be interested to know (Optional)
echo "<p>The connection to MySQL server is also successfully!</p>";
echo 'The value of $connection is: ' . $$link;
echo "<br />";
echo 'The value of $db_link is: ' . $db_link;
echo "<br /><br />";

if ($$link) mysql_select_db($database);

// Print out something you may be interested to know (Optional)
echo 'The value of db is now ' . $db;

return $$link;

}

?>

Step 5 : Open index.php homepage with browser

PHP example filephp-select-mysql-database-example-3.zip

PHP example filetest-on-osc-website.zip

If the connection is successful, and the path of all files are correct, the following output should appear on the screen:

The connection to MySQL server is also successfully!

The value of $connection is: Resource id #4
The value of $db_link is: Resource id #4

The value of db is now 1

Now, you have prepared your own PHP codes same as osCommerce that can connect to MySQL server and select the osCommerce table.