Home | Store | osCommerce Tutorials

osCommerce Navigate Through Categories - Categories Content Code Study

Part 3.1 - Get first record of osCommerce Categories data


This osCommerce tutorial shows how to get the categories data from MySQL database.

In this part of osCommerce tutorial, we will try to write our own codes to display the the Categories Content. This is the best way to learn writting codes. We will start off with the following codes:

<?php

require('includes/application_top.php');

// The values of this two variables should come from application_top.php
$current_category_id = 1; // Hardware
$languages_id = 1; // English Language

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, 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 . "' order by sort_order, cd.categories_name");

$categories = tep_db_fetch_array($categories_query);

?>

Firstly, lets' take a look at osCommerce Table "categories" and Table "categories_description" to get better idea of the records in the tables.

osCommerce Table "categories" and Table "categories_description"

osCommerce database table categories

Look at the MySQL database query:

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, 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 . "' order by sort_order, cd.categories_name");

$categories = tep_db_fetch_array($categories_query);

Actually we already learned this line of code in MySQL database tutorial. It simply selects records from both table "categories" and table "categories_description".

The first line is quite self-explantory:

  • Select field"categories_id", field"categories_image" and field"parent_id" from Table"categories".
  • Select field"categories_name" from Table"categories_description".
  • The criteria is that:
    the field"parent_id" in Table"categories" equal $current_category_id AND field"categories_id" in Table"categories" equal field"categories_id" in Table"categories_description" AND field"language_id" in Table"categories_description" equal $language_id.
  • The result is oder by field"categories_name" in Table"categories_description".

The second line is to fetch the records in an array stored in the $categories variable.

We learned how to get the data from the MySQL query in MySQL Database Tutorial, it's time to practise.

Example 2-3

Let's start with simple. We will try to get the data of the first row of the array:

<?php

require('includes/application_top.php');

// The values of this two variables should come from application_top.php
$current_category_id = 1; // Hardware
$languages_id = 1; // English Language

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, 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 . "' order by sort_order, cd.categories_name");

$categories = tep_db_fetch_array($categories_query);

// get the data
echo 'The categories_id is: ' . $categories ["categories_id"];
echo "<br />";
echo 'The categories_name is: ' . $categories["categories_name"];
echo "<br />";
echo tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name']);

?>

We all learned that in the MySQL database tutorials therefore there is no need to explain the codes again.

osCommerce file structure example codesosc-navigate-through-categories-code-study-example-2-3.zip

Save the file as osc-navigate-through-categories-code-study-example-2-3.php.

Upload the file to the osCommerce homepage.

Access the file with browser.

The output of the file should look like:

oscommerce navigate through categories

Now we got the first record from the $categories array successfully. In the next osCommerce tutorial, we will use a table to display the records as the default osCommerce shop do. We will also try to get all record from the $categories array. Please read on...