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-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-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.
- 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.
- The default value of $link is 'db_link'.
-
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-select-mysql-database-example-3.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.