Home | Store | osCommerce Tutorials

osCommerce Main Content Vs $category_depth and $cPath (2)

In previous tutorial, we discussed the value of $category_depth and $cPath when browsing the homepage. When browsing the osCommerce homepage, the value of $category_depth and $cPath are:

$category_depth = 'top';
$cPath = 'empty';

In this tutorial, we are going to browse the links under the Categories Box so that the cPath value is set and will not be null. Hence the block of codes inside the curly braces shown below will be executed:

// the following cPath references come from application_top.php
$category_depth = 'top';

if (isset($cPath) && tep_not_null($cPath)) {

// If $cPath is not null
// Codes here will be executed

}

Let's recall the following PHP block of codes (line 15 - 32) at the top of osCommerce homepage (index.php) again:

// the following cPath references come from application_top.php
$category_depth = 'top';

if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " .
TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
$cateqories_products = tep_db_fetch_array($categories_products_query);

if ($cateqories_products['total'] > 0) {
$category_depth = 'products'; // display products
} else {
$category_parent_query = tep_db_query("select count(*) as total from " .
TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");
$category_parent = tep_db_fetch_array($category_parent_query);

if ($category_parent['total'] > 0) {
$category_depth = 'nested'; // navigate through the categories
} else {
$category_depth = 'products'; // category has no products, but display the 'no products' msg
}
}
}

Let's study the PHP codes inside the black bolded curly braces one by one:

Firstly, pay attention to the following two lines in red color:

if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " .
TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

$cateqories_products = tep_db_fetch_array($categories_products_query);

if ($cateqories_products['total'] > 0) {
// Codes here
} else {
// Codes here
}

We already learn some PHP and MySQL database SQL syntax in previous tutorials, we should familiar with the above MySQL SQL query.

The two lines of codes simply count the total number of records from table products_to_categories where categories_id is equal to $current_category_id.

MySQL Database Table products_to_categories

Let's take a look at the table products_to_categories of default osCommerce shop as shown in the picture below. The table products and table categories_description are attached so that you have an idea what the products and what the categories are.

$current_category_id

The $current_category_id simply the value extract from value of $cPath. You can find the codes of getting the $current_category_id from the application_top.php (line 457 -463). The following tables shows the value of $current_category_id for all categories link of default osCommerce shop:

Source Link and cPath current_category_id
Home Page http://osc.cz.cc/ 0
Hardware http://osc.cz.cc/index.php?cPath=1 1
CDROM Drives http://osc.cz.cc/index.php?cPath=1_17 17
Graphics Cards http://osc.cz.cc/index.php?cPath=1_4 4
Keyboards http://osc.cz.cc/index.php?cPath=1_8 8
Memory http://osc.cz.cc/index.php?cPath=1_16 16
Mice http://osc.cz.cc/index.php?cPath=1_9 9
Monitors http://osc.cz.cc/index.php?cPath=1_6 6
Printers http://osc.cz.cc/index.php?cPath=1_5 5
Speakers http://osc.cz.cc/index.php?cPath=1_7 7
Software http://osc.cz.cc/index.php?cPath=2 2
Action http://osc.cz.cc/index.php?cPath=2_19 19
Simulation http://osc.cz.cc/index.php?cPath=2_18 18
Strategy http://osc.cz.cc/index.php?cPath=2_20 20
DVD Movies http://osc.cz.cc/index.php?cPath=3 3
Action http://osc.cz.cc/index.php?cPath=3_10 10
Cartoons  http://osc.cz.cc/index.php?cPath=3_13 13
Comedy http://osc.cz.cc/index.php?cPath=3_12 12
Drama http://osc.cz.cc/index.php?cPath=3_15 15
Science Fiction http://osc.cz.cc/index.php?cPath=3_11 11
Thriller  http://osc.cz.cc/index.php?cPath=3_14 14

Now you should be able to count the total number of records from table products_to_categories where categories_id is equal to $current_category_id.

What is the purpose of this query?

The table products_to_categories is simply a match of ALL osCommerce products to corresponding category id. The basic concept is that:

  • each osCommerce product must have a products_id
  • each osCommerce product must must belong (or match) to a categories_id

Take a look at the table products_to_categories again and read some records from the top:

  • products_id 1 (Matrox G200 MMS) -> categories_id 4 (Graphics Cards)
  • products_id 2 (Matrox G400 32MB) -> categories_id 4 (Graphics Cards)
  • products_id 3 (Microsoft IntelliMouse Pro) -> categories_id 9 (Mice)
  • products_id 4 (The Replacement Killers) -> categories_id 10 (Action)
  • products_id 5 (Blade Runner - Director's Cut) -> categories_id 11 (Science Fiction)
  • etc...

In other words. The query simply counts how many products are under the $current_category_id.

Still confused? Let's try to query each category_id one by one and everything should be clear. The categories_name is also include so that you have a better idea what the $current_categories are.

$current_category_id categories_name $cateqories_products['total']
1 Hardware 0
2 Software 0
3 DVD Movies 0
4 Graphics Cards 2
5 Printers 1
6 Monitors 0
7 Speakers 0
8 Keyboards 1
9 Mice 2
10 Action 9
11 Science Fiction 1
12 Comedy 2
13 Cartoons 1
14 Thriller 1
15 Drama 3
16 Memory 0
17 CDROM Drives 0
18 Simulation 1
19 Action 1
20 Strategy 2

Since there are total 27 products in default osCommerce shop (see table products above), therefore the total of the $cateqories_products['total'] column is also 27.

Ah... wait.... Did I see the above table before?

You sure did, but not in table format. Look at the picture below:

osCommerce categories overview

Query Result

Now, we have gathered all necessary information, it's time to study the codes again:

if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " .
TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

$cateqories_products = tep_db_fetch_array($categories_products_query);

if ($cateqories_products['total'] > 0) {
$category_depth = 'products'; // display products
} else {
// Codes here
}

Result:

All category_id that contain products will not be equal to zeo (i.e. $cateqories_products['total'] greater than zero), the value of $category_depth variable will be assigned to 'products'.

The following table shows category_id greater than zero (highlighted in yellow)

$current_category_id categories_name $cateqories_products['total']
1 Hardware 0
2 Software 0
3 DVD Movies 0
4 Graphics Cards 2
5 Printers 1
6 Monitors 0
7 Speakers 0
8 Keyboards 1
9 Mice 2
10 Action 9
11 Science Fiction 1
12 Comedy 2
13 Cartoons 1
14 Thriller 1
15 Drama 3
16 Memory 0
17 CDROM Drives 0
18 Simulation 1
19 Action 1
20 Strategy 2

How about categories contain no products (i.e. $cateqories_products['total'] equal to zero)?

There are two possible scenerios:

  1. Parent categories have no products directly, only sub-categories.
    For example: Hardware, Software and DVD Movies.
  2. Category (subcategories) with no products.
    For example: Monitors, Speakers, Memory and CDROM Drives.
    Although this scenerio will not be happened in a real shop, the osCommerce programmer include this possibilities.

The following table shows category_id equal to zero (highlighted in grey)

$current_category_id categories_name $cateqories_products['total']
1 Hardware 0
2 Software 0
3 DVD Movies 0
4 Graphics Cards 2
5 Printers 1
6 Monitors 0
7 Speakers 0
8 Keyboards 1
9 Mice 2
10 Action 9
11 Science Fiction 1
12 Comedy 2
13 Cartoons 1
14 Thriller 1
15 Drama 3
16 Memory 0
17 CDROM Drives 0
18 Simulation 1
19 Action 1
20 Strategy 2

Therefore the codes need to sort out the two scenerios:

  1. Parent categories (i.e. Hardware, Software and DVD Movies) - need to navigate through the categories.
  2. Category (sub-categories) with no products - need to display "Category has no products" to visitors.

Let's check the codes again:

// the following cPath references come from application_top.php
$category_depth = 'top';

if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " .
TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
$cateqories_products = tep_db_fetch_array($categories_products_query);

if ($cateqories_products['total'] > 0) {
$category_depth = 'products'; // display products
} else {
$category_parent_query = tep_db_query("select count(*) as total from " .
TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");

$category_parent = tep_db_fetch_array($category_parent_query);

if ($category_parent['total'] > 0) {
$category_depth = 'nested'; // navigate through the categories
} else {
$category_depth = 'products'; // category has no products, but display the 'no products' msg
}
}
}

Pay attention to the two lines of codes in red color. The query to osCommerce MySQL database is similar to example above.

This two lines of codes simply count the total number of records from table categories where parent_id is equal to $current_category_id.

Table categories

Let's take a look at osCommerce table products_categories.

osCommerce table categories

The procedures are the same as before. Again let's query the category_id with $cateqories_products['total'] equal to zero (rows with white background color). The categories_name is also include so that you have a better idea what the $current_categories are.

The query result is shown the table below under the $category_parent['total'] column in red font color.

$current_category_id categories_name $cateqories_products['total'] $category_parent['total']
1 Hardware 0 8
2 Software 0 3
3 DVD Movies 0 6
4 Graphics Cards 2 N/A
5 Printers 1 N/A
6 Monitors 0 0
7 Speakers 0 0
8 Keyboards 1 N/A
9 Mice 2 N/A
10 Action 9 N/A
11 Science Fiction 1 N/A
12 Comedy 2 N/A
13 Cartoons 1 N/A
14 Thriller 1 N/A
15 Drama 3 N/A
16 Memory 0 0
17 CDROM Drives 0 0
18 Simulation 1 N/A
19 Action 1 N/A
20 Strategy 2 N/A

I think that everything should be clear right now.

Query Result

Now, we have gathered all necessary information, it's time to study the codes again:

// the following cPath references come from application_top.php
$category_depth = 'top';

if (isset($cPath) && tep_not_null($cPath)) {
$categories_products_query = tep_db_query("select count(*) as total from " .
TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
$cateqories_products = tep_db_fetch_array($categories_products_query);

if ($cateqories_products['total'] > 0) {
$category_depth = 'products'; // display products
} else {
$category_parent_query = tep_db_query("select count(*) as total from " .
TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");

$category_parent = tep_db_fetch_array($category_parent_query);

if ($category_parent['total'] > 0) {
$category_depth = 'nested'; // navigate through the categories
} else {
$category_depth = 'products'; // category has no products, but display the 'no products' msg
}
}
}

Result:

  • All category_id that contain subcategories will be greater than zero (i.e. $category_parent['total'] > 0), the value of $category_depth variable will be assigned to 'nested'.
    Example: Hardware, Software, DVD Movies.
  • All subcategory_id that contain no product will not be greater than zero ($category_parent['total'] = 0), the value of $category_depth variable will be assigned to 'products'.