Home | Store | osCommerce Tutorials

Get Data from Multiple osCommerce MySQL Tables

osCommerce has total 47 tables. Usually each table contains different record. Therefore, sometimes it may requires to get or retrieve records from different tables. In other words, we need to get data from more than one table. In this case we need to "join" the tables.

We will use Table"categories" and Table"categories_description" as an example. In this example, we will try to get data from both tables. Firstly, take a look at the records in Table"categories" and Table"categories_description":

osCommerce table categories and table categories_description

Now you wish to get the following records from the above two table:

  • the categories_image data from table "categories"
  • the categories_name data from table "categories_description"

The situation is illustrated in the following picture:

osCommerce MySQL database table join

Now let's try to use PHP codes to do the query:

<?php

require('includes/application_top.php');

// This value should come from application_top.php in real shop
$current_category_id = 1; // Hardware
$languages_id = 1; // English Language

$categories_query = tep_db_query("select c.categories_id, c.categories_image, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");

// get the data
echo "<table border=1 cellpadding=8>";
echo "<tr><td>categories_id</td><td>categories_image</td><td>categories_name</td></tr>";

while ( $categories = tep_db_fetch_array($categories_query) ) {

$cPath_new = tep_get_path($categories['categories_id']);

echo "<tr><td>" . $categories ["categories_id"] . "</td><td>" . $categories["categories_image"] . "</td><td>" . $categories["categories_name"] . "</td></tr>";

}

echo "</table>";

?>

Actually we are very familiar with the above codes except that the codes in blue color seems a bit different. Before that we usually select fields from a single table. Now we learned a new thing. The codes in blue color is used to select fields from different tables. Same as previous MySQL database tutorials, the other codes are simply used to fetch data from the records and display on the webpage.

Therefore we will focus on the blue codes only.

$categories_query = tep_db_query("select c.categories_id, c.categories_image, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");

The complete codes is better to illustrate with the following diagram:

osCommerce MySQL database table join

Let's break down in codes in two parts.

The select part of the codes can be illustrated with the following diagram:

osCommerce MySQL database table join

While the query criteria can be explained with the following diagram:

osCommerce MySQL database table join

Now we know how the codes work.

Save the codes as get-data-from-multiple-mysql-tables-example-1.php

PHP example fileget-data-from-multiple-mysql-tables-example-1.zip

Upload the file to osCommerce home directory.

Access the file with a browser.

The output of the query should look like:

categories_idcategories_imagecategories_name
4subcategory_graphic_cards.gifGraphics Cards
5subcategory_printers.gifPrinters
6subcategory_monitors.gifMonitors
7subcategory_speakers.gifSpeakers
8subcategory_keyboards.gifKeyboards
9subcategory_mice.gifMice
16subcategory_memory.gifMemory
17subcategory_cdrom_drives.gifCDROM Drives

Query Result:

This is very clear that the above 8 records meet the critera.

Now, the data from different tables have been joined successfully.

Sometimes, this kind of join is called "Inner Join".

NOTE:

If you wish to display the data of language_id, you also need to select the language_id. Please see the following example:

<?php

require('includes/application_top.php');

// This value should come from application_top.php in real shop
$current_category_id = 1; // Hardware
$languages_id = 1; // English Language

$categories_query = tep_db_query("select c.categories_id, c.categories_image, cd.categories_name, cd.language_id, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");

// get the data
echo "<table border=1 cellpadding=8>";
echo "<tr><td>categories_id</td><td>categories_image</td><td>categories_name</td>
<td>language_id</td></tr>";

while ( $categories = tep_db_fetch_array($categories_query) ) {

$cPath_new = tep_get_path($categories['categories_id']);

echo "<tr><td>" . $categories ["categories_id"] . "</td><td>" . $categories["categories_image"] . "</td><td>" . $categories["categories_name"] . "</td><td>" . $categories["language_id"] . "</td></tr>";

}

echo "</table>";

?>

Then the output will then be:

categories_id categories_image categories_name language_id
4 subcategory_graphic_cards.gif Graphics Cards 1
5 subcategory_printers.gif Printers 1
6 subcategory_monitors.gif Monitors 1
7 subcategory_speakers.gif Speakers 1
8 subcategory_keyboards.gif Keyboards 1
9 subcategory_mice.gif Mice 1
16 subcategory_memory.gif Memory 1
17 subcategory_cdrom_drives.gif CDROM Drives 1

This PHP MySQL tutorial how to get data from more than one MySQL database table. Now we should gather enough information or knowledge to explore the file structure of osCommerce shop. From next tutorials, we will try to study the code structure of osCommerce in details.