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":
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:
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:
Let's break down in codes in two parts.
The select part of the codes can be illustrated with the following diagram:
While the query criteria can be explained with the following diagram:
Now we know how the codes work.
Save the codes as get-data-from-multiple-mysql-tables-example-1.php
get-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_id | categories_image | categories_name |
| 4 | subcategory_graphic_cards.gif | Graphics Cards |
| 5 | subcategory_printers.gif | Printers |
| 6 | subcategory_monitors.gif | Monitors |
| 7 | subcategory_speakers.gif | Speakers |
| 8 | subcategory_keyboards.gif | Keyboards |
| 9 | subcategory_mice.gif | Mice |
| 16 | subcategory_memory.gif | Memory |
| 17 | subcategory_cdrom_drives.gif | CDROM 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.