Home | Store | osCommerce Tutorials

osCommerce New Products Module - New Products Display (2)

Part 2 - Code structure of osCommerce New Products Display in homepage


In previous osCommerce tutorial, we learned that which codes will be executed when browsing osCommerce homepage and osCommerce Parent Categories. Let's recall the codes structure again:

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

// Codes here will be executed
// when browsing osCommerce homepage

} else {

// Codes here will be executed
//when browsing osCommerce Parent Categories (e.g. Hardware, Softwares and DVD Movies)

}

In this osCommerce tutorial, we will study how the codes display the New Products when browsing the osCommerce homepage.

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image,
p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price,
p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s
on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" .
(int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {

// Codes here will be executed
//when browsing osCommerce Parent Categories (e.g. Hardware, Softwares and DVD Movies)

}

 

$row = 0;
$col = 0;
$info_box_contents = array();
while ($new_products = tep_db_fetch_array($new_products_query)) {

$info_box_contents[$row][$col] = array(

'align' => 'center',

'params' => 'class="smallText" width="33%" valign="top"',

'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' .
$new_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES .
$new_products['products_image'], $new_products['products_name'], SMALL_IMAGE_WIDTH,
SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO,
'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a><br>' .
$currencies->display_price($new_products['products_price'],
tep_get_tax_rate($new_products['products_tax_class_id'])));

$col ++;
if ($col > 2) {
$col = 0;
$row ++;
}
}

 

new contentBox($info_box_contents);

 

New Products Display in osCommerce Homepage

Firstly, we will focus on the MySQL select database codes only (following codes in blue color).

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select p.products_id, p.products_image,
p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price,
p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s
on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd
where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" .
(int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

} else {

// Codes here will be executed
//when browsing osCommerce Parent Categories (e.g. Hardware, Softwares and DVD Movies)

}

This line of codes simply select fields from database tables. We have been discussing similar codes in details in previous tutorials. Therefore we will go through this codes quickly.

As before, this is better to take a look at the tables that involves in this line of code:

osCommerce Table products:

osCommerce table products

osCommerce Table products_description:

osCommerce table products_description

osCommerce Table specials:

osCommerce table specials

Okay! Let's study the codes quickly:

select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price
from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd

  • It simply selects product_id, products_image and products_tax_class_id from Table products, and
  • Select products_name from Table products_description

Note the if condition inside the MySQL select statement. It simply means:

  • If the product is offering special (i.e. status is 1), then using the specials_new_products_price in the Table specials.
  • If the product is not offering special, then using the products_price in the Table products.
  • In both cases, use "products_price" for the field name.

It also uses the MySQL left join here. This is hard to explain and hard to understand how the left join works. In brief, it starts the query from the left table (TABLE_PRODUCTS). From each row of record of table TABLE_PRODUCTS, it also check the table TABLE_SPECIALS, find if the record match the criterias (i.e. if the products_id has offering specials).

where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'

This line of code is simply the query criterias.

order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

The matching records will be ordered by products_date in descending order so that the newest added products will be displayed first.

The value of MAX_DISPLAY_NEW_PRODUCTS will limit the number of new products display. The default value of MAX_DISPLAY_NEW_PRODUCTS is 9 and can be modified in the osCommerce Admin area.

osCommerce new products module maximum value

If you play around with the codes like our previous tutorials.

$new_products = tep_db_fetch_array($new_products_query)

The first record in $new_products array will be:

Array
(
[products_id] => 27
[products_image] => hewlett_packard/lj1100xi.gif
[products_tax_class_id] => 1
[products_name] => Hewlett Packard LaserJet 1100Xi
[products_price] => 499.9900
)

After then if you use a while loop to display all new products:

while ($new_products = tep_db_fetch_array($new_products_query)) {
echo '<pre>';
print_r($new_products);
echo '</pre>';
echo '<br />';
}

It will then loops through all the records one by one. The output of the above codes will be:

Array
(
[products_id] => 27
[products_image] => hewlett_packard/lj1100xi.gif
[products_tax_class_id] => 1
[products_name] => Hewlett Packard LaserJet 1100Xi
[products_price] => 499.9900
)

Array
(
[products_id] => 26
[products_image] => microsoft/imexplorer.gif
[products_tax_class_id] => 1
[products_name] => Microsoft IntelliMouse Explorer
[products_price] => 64.9500
)

Array
(
[products_id] => 25
[products_image] => microsoft/intkeyboardps2.gif
[products_tax_class_id] => 1
[products_name] => Microsoft Internet Keyboard PS/2
[products_price] => 69.9900
)

Array
(
[products_id] => 24
[products_image] => gt_interactive/disciples.gif
[products_tax_class_id] => 1
[products_name] => Disciples: Sacred Lands
[products_price] => 90.0000
)

Array
(
[products_id] => 23
[products_image] => gt_interactive/wheel_of_time.gif
[products_tax_class_id] => 1
[products_name] => The Wheel Of Time
[products_price] => 99.9900
)

Array
(
[products_id] => 22
[products_image] => gt_interactive/unreal_tournament.gif
[products_tax_class_id] => 1
[products_name] => Unreal Tournament
[products_price] => 89.9900
)

Array
(
[products_id] => 21
[products_image] => sierra/swat_3.gif
[products_tax_class_id] => 1
[products_name] => SWAT 3: Close Quarters Battle
[products_price] => 79.9900
)

Array
(
[products_id] => 20
[products_image] => dvd/beloved.gif
[products_tax_class_id] => 1
[products_name] => Beloved
[products_price] => 54.9900
)

Array
(
[products_id] => 19
[products_image] => dvd/theres_something_about_mary.gif
[products_tax_class_id] => 1
[products_name] => There's Something About Mary
[products_price] => 49.9900
)

After selecting the MySQL database tables, the next step is usually to fetch the records in an array. Then using the while loop to loop through all the records one by one. The codes are shown as below:

$row = 0;
$col = 0;
$info_box_contents = array();
while ($new_products = tep_db_fetch_array($new_products_query)) {

$info_box_contents[$row][$col] = array(

'align' => 'center',

'params' => 'class="smallText" width="33%" valign="top"',

'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' .
$new_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES .
$new_products['products_image'], $new_products['products_name'], SMALL_IMAGE_WIDTH,
SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO,
'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a><br>' .
$currencies->display_price($new_products['products_price'],
tep_get_tax_rate($new_products['products_tax_class_id'])));

$col ++;
if ($col > 2) {
$col = 0;
$row ++;
}
}

The above codes are a bit difference from what we learned before. In stead of displaying the data, the codes create a new array with the $info_box_contents variable. Then stored all data in the $info_box_contents variable while looping through the records.

Obviously, the $info_box_contents array will be used by other codes to display the data.

Note that the $info_box_contents array is a three-dimensional array. We already learned two-dimensional array in previous tutorial. The three-dimensional array is almost the same as two-dimensional array except three indexes are required.Let's recall something about array.

  • To access value in one-dimensional array, we need an index or index name.
    For example, $info_box_contents[0], $info_box_contents ["text"]
  • To access value in two-dimensional array, we need a row index and a column index.
    For example $info_box_contents [row][col]
  • To access value in three-dimensional array, we need a row index, a column index and a index name.
    For example $info_box_contents [row][col][index name]

The default number of columns of the new products is 3. If the column number is greater than 2 (if ($col > 2)), then the $col is reset to zero. Therefore it starts a new row ($row ++;).

What is the contents of the $info_box_contents array?

If you are interested, here's the contents of the $info_box_contents variable of default osCommerce shop:

Array
(
[0] => Array
(
[0] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=27">
<img src="images/hewlett_packard/lj1100xi.gif" border="0"
alt="Hewlett Packard LaserJet 1100Xi" title=" Hewlett Packard LaserJet 1100Xi "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=27">
Hewlett Packard LaserJet 1100Xi</a><br>$272.85
)

[1] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=26">
<img src="images/microsoft/imexplorer.gif" border="0"
alt="Microsoft IntelliMouse Explorer" title=" Microsoft IntelliMouse Explorer "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=26">
Microsoft IntelliMouse Explorer</a><br>$69.50
)

[2] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=25">
<img src="images/microsoft/intkeyboardps2.gif" border="0"
alt="Microsoft Internet Keyboard PS/2" title=" Microsoft Internet Keyboard PS/2 "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=25">
Microsoft Internet Keyboard PS/2</a><br>$74.89
)

)

[1] => Array
(
[0] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=24">
<img src="images/gt_interactive/disciples.gif" border="0"
alt="Disciples: Sacred Lands" title=" Disciples: Sacred Lands "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=24">
Disciples: Sacred Lands</a><br>$96.30
)

[1] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=23">
<img src="images/gt_interactive/wheel_of_time.gif" border="0"
alt="The Wheel Of Time" title=" The Wheel Of Time "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=23">
The Wheel Of Time</a><br>$106.99
)

[2] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=22">
<img src="images/gt_interactive/unreal_tournament.gif" border="0"
alt="Unreal Tournament" title=" Unreal Tournament "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=22">
Unreal Tournament</a><br>$96.29
)

)

[2] => Array
(
[0] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=21">
<img src="images/sierra/swat_3.gif" border="0"
alt="SWAT 3: Close Quarters Battle" title=" SWAT 3: Close Quarters Battle "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=21">
SWAT 3: Close Quarters Battle</a><br>$85.59
)

[1] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=20">
<img src="images/dvd/beloved.gif" border="0"
alt="Beloved" title=" Beloved " width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=20">
Beloved</a><br>$58.84
)

[2] => Array
(
[align] => center
[params] => class="smallText" width="33%" valign="top"
[text] => <a href="http://osc.cz.cc/product_info.php?products_id=19">
<img src="images/dvd/theres_something_about_mary.gif" border="0"
alt="There's Something About Mary" title=" There's Something About Mary "
width="100" height="80"></a><br>
<a href="http://osc.cz.cc/product_info.php?products_id=19">
There's Something About Mary</a><br>$53.49
)

)

)

As you can see that the $info_box_contents array contains 3 arrays, then each array in turns contains another 3 arrays.

If you study the contents of the $info_box_contents carefully, you should noticed that we have enough information to show the New products Display. Like previous tutorials, this is a good idea to try to write the codes on our own to get the same result.

In the next osCommerce tutorial, we will try to write our own codes to display the new products. We will also study the original osCommerce code structure. Finally we will compare our own codes with the original osCommerce codes.