Home | Store | osCommerce Tutorials

Use PHP Peform Query on MySQL Database Tables

Up to now, we learned how to us PHP connect with MySQL server, select a MySQL database and close the connection with MySQL server.

  • Step 1: Connect with MySQL server (OK)
  • Step 2: Select a MySQL database to work with (OK)
  • Step 3: Peform query on MySQL database tables, e.g. read data, update data, insert data or delete data.
  • Step 4: Display results on the screen, if required.
  • Step 5: Close the connection with MySQL server (OK)

This PHP tutorial will discuss both Step 3 (Perform query on MySQL database), and Step 4 (Display results on screen).

Once the MySQL server has been connected and a database has also been selected to work with. This is very clear that the next step is playing around (query) with the data stored in the database table. In other words we need to send query in order to manipulate the data in the database table.

Take a look at all the tables in osCommerce MySQL database:

osCommerce MySQL database table

Let's open the categories_description table:

osCommerce MySQL database table

We are going to do some queries on the categories_description table:

The procedures of connect MySQL server and select database will not be shown here since you should be very familar with that.

Example 1: Read Single Row of Data from MySQL Database Table

Step 1: Select Data From Database Table

Use the SELECT statement to select data from a database.

// create SQL
$sql =
"SELECT categories_id, categories_name FROM categories_description where categories_id ='19' ";

Look! the syntax is very simple and the meaning is quite self-explantory. There are two key points in this query:

  • In this query, we are looking for the field "categories_id" and "categories_name" of table "categories_description".
  • There may be many records found in table "categories_description". However we need the record with categories_id equal to 19 only.

The situation is same as the picture below:

osCommerce select MySQL database table

Note: To simplify the query, we only assume that there is only one language in the osCommerce shop.

After the data are selected from the database, the query need to be executed in order to get the result.

Step 2: Execute SQL Query and Get Result

PHP use mysql_query function to execute SQL query as shown below:

// execute SQL query and get result
$sql_result = mysql_query($sql, $link) or die ("Couldn't execute SQL query.");

If everything is correct, the query to MySQL database table is finished. For some MySQL query, the process is end here. Isn't it very simple?

For most MySQL query, the results will usually be displayed on the screen. In this example, we need to read the data from categories_description table, and the results need to be displayed on screen.

Step 3: Fetch Data from Record

//Fetch data from the query result
$row = mysql_fetch_array($sql_result);

//Get the data from each result column
$categories_id = $row["categories_id"];
$categories_name = $row["categories_name"];

The situation is similar to the picture below:

osCommerce select MySQL database table

Step 4: Display the Data on Screen

//Print the data
echo "The categories id is: " . $categories_id;
echo "<br />";
echo "The categories id is: " . $categories_name;

Step 5: Free Resources

This step is usually not required. As mentioned in PHP manual that mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.

// free resources
mysql_free_result($sql_result);

Step 6: Run the codes

If everything is correct, the following output should appear on the screen:

The categories id is: 19
The categories id is: Action

PHP example filephp-mysql-database-query-example-1.zip

Now, we know how to get data from a specific row of a database table.

Example 2: Read All Rows of Data from MySQL Database Table

The procedures are almost same as Example 1.

Step 1: Select Data From Database Table

Use the SELECT statement to select data from a database.

// create SQL
$sql = "SELECT categories_id, categories_name FROM categories_description";

Again! The syntax is very simple and the meaning is quite self-explantory.

  • In this query, we are also looking for the field "categories_id" and "categories_name" of table "categories_description".
  • This time we need all records of categories_id and categories found from table "categories_description".

After the data are selected from the database, the query need to be executed in order to get the result.

Step 2: Execute SQL Query and Get Result

PHP use mysql_query function to execute SQL query as shown below:

// execute SQL query and get result
$sql_result = mysql_query($sql, $link) or die ("Couldn't execute SQL query.");

If everything is correct, the query to MySQL database table is finished. For some MySQL query, the process is end here. Isn't it very simple?

For most MySQL query, the results will usually be displayed on the screen. In this example, we need to read the data from categories_description table, the results need to be displayed on screen.

Step 3: Display Data on Screen

// start results formatting
echo "<table border=2 align=center cellspacing=3 cellpadding=3>";
echo "<tr><th>CATEGORIES ID</th><th>CATEGORIES NAME</th></tr>";

// format results by row
while ($row = mysql_fetch_array($sql_result)) {
$categories_id = $row["categories_id"];
$categories_name = $row["categories_name"];

echo "<tr><td>$categories_id</td><td>$categories_name</td></tr>";
}

echo "</table>";

// free resources
mysql_free_result($sql_result);

Same as Example 1, the result of SQL query ($sql_result) is stored in an array. We also use the mysql_fetch_array() function to return each row of the records.

This time we need to use the while loop to loops through all the records one by one, then enable us to get the values of each row with the $row variable.

Step 4: Run the codes

Run the codes again. If everything is correct, the following output should appear on the screen. This time, we assume that there are three languages installed in the default osCommerce shop.

CATEGORIES IDCATEGORIES NAME
1Hardware
2Software
3DVD Movies
4Graphics Cards
5Printers
6Monitors
7Speakers
8Keyboards
9Mice
10Action
11Science Fiction
12Comedy
13Cartoons
14Thriller
15Drama
16Memory
17CDROM Drives
18Simulation
19Action
20Strategy
1Hardware
2Software
3DVD Filme
4Grafikkarten
5Drucker
6Bildschirme
7Lautsprecher
8Tastaturen
9Mäuse
10Action
11Science Fiction
12Komödie
13Zeichentrick
14Thriller
15Drama
16Speicher
17CDROM Laufwerke
18Simulation
19Action
20Strategie
1Hardware
2Software
3Peliculas DVD
4Tarjetas Graficas
5Impresoras
6Monitores
7Altavoces
8Teclados
9Ratones
10Accion
11Ciencia Ficcion
12Comedia
13Dibujos Animados
14Suspense
15Drama
16Memoria
17Unidades CDROM
18Simulacion
19Accion
20Estrategia

PHP Query Using osCommerce MySQL Database Functions

Now, we get some basic knowledge using PHP to perform query on MySQL database. It's time to see how osCommerce programmer using PHP to perform query on MySQL database.

If you opened the homepage (catalog/index.php) of osCommerce, reading from the top, you will very soon find the something like the following codes (line 18 - 19):

$categories_products_query = tep_db_query ("select count(*) as total from " . TABLE . " where ....
$cateqories_products = tep_db_fetch_array ($categories_products_query);

You should also find similar codes in line 23 - 24:

$category_parent_query = tep_db_query ("select count(*) as total from " . TABLE . " where .....
$category_parent = tep_db_fetch_array ($category_parent_query);

You already learned PHP function and some MySQL database query syntax, you should know that the tep_db_query() and tep_db_fetch_array() are custom PHP functions. Moreover you should know that this two functions are used to:

  • Select Data From Database Table
  • Fetch Data from Record

If you open the database.php in the includes/function folder, you should find the tep_db_query() and tep_db_fetch_array() function functions:

tep_db_query() Function:

function tep_db_query($query, $link = 'db_link') {
global $$link;

if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
error_log('QUERY ' . $query . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
}

$result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error());

if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
$result_error = mysql_error();
error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
}

return $result;
}

tep_db_fetch_array() Function:

function tep_db_fetch_array($db_query) {
return mysql_fetch_array($db_query, MYSQL_ASSOC);
}

Okay! Let's see how the functions work.

osCommerce MySQL Database Query Function example 1:

Open osCommerce homepage (catalog/index.php), find the the following codes (line 18 - 19):

$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);

In order to test the functions, the above codes have been modified as below.

<?php

require('includes/application_top.php');

$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);

echo 'The total categories products is: ' . $cateqories_products['total'];

?>

The above query simply count the total number of records from table "products_to_categories" where the table field "categories_id" is equal to $current_category_id.

Note: The value of $current_category_id variable comes from application_top.php

Let's check the osCommerce database table "products_to_categories" so that you have a better idea the records in the table.

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.

osCommerce table products_to_categories

In the original osCommerce homepage (catalog/index.php), the value of $current_category_id reference will come from application_top.php. In order to simulate the osCommerce MySQL database query, we will assign the value of $current_category_id as following.

Case 1: $current_category_id = 1 (Hardware)

<?php

require('includes/application_top.php');

// simulate the following $current_category_id references come from application_top.php
$current_category_id = 1;

$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);

echo 'The total categories products is: ' . $cateqories_products['total'];


?>

Save the file as osc-mysql-database-query-function-example-1.php in the home directory (catalog) of your osCommerce website.

PHP example file osc-mysql-database-query-function-example-1.zip

Open a browser and access the URL of the file. The following output should appear on the screen:

The total categories products is: 0

Why the number of record is zero?

This is better to explain with the following diagram:

PHP query on osCommerce database table

Let's change the value of $current_category_id

Case 2: $current_category_id =4 (Graphics Cards)

<?php

require('includes/application_top.php');

// simulate the following $current_category_id references come from application_top.php
$current_category_id = 4;

$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);

echo 'The total categories products is: ' . $cateqories_products['total'];

?>

Save the file as osc-mysql-database-query-function-example-2.php in the home directory (catalog) of your osCommerce website.

PHP example file osc-mysql-database-query-function-example-2.zip

Open a browser and access the URL of the file. The following output should appear on the screen:

The total categories products is: 2

The following diagram explains why the total number of records are 2.

PHP query on osCommerce database table

Let's do one more case.

Case 2: $current_category_id =10 (Action)

<?php

require('includes/application_top.php');

// simulate the following $current_category_id references come from application_top.php
$current_category_id = 10;

$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);

echo 'The total categories products is: ' . $cateqories_products['total'];


?>

Save the file as osc-mysql-database-query-function-example-3.php in the home directory (catalog) of your osCommerce website.

PHP example file osc-mysql-database-query-function-example-3.zip

Open a browser and access the URL of the file. The following output should appear on the screen:

The total categories products is: 9

The following diagram explains why the total number of records are 9.

PHP query on osCommerce database table

Let's do one more example.

osCommerce MySQL Database Query Function example 2:

Open osCommerce homepage (catalog/index.php), find the the following codes (line 23 - 24):

$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);

Same as example 1, the above codes have been modified as below in order to test the functions:

<?php

require('includes/application_top.php');

$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);

echo 'The total categories parent is: ' . $cateqories_parent ['total'];

?>

You should know the above query very well now. The above query simply count the total number of records from table "categories" where the table field "parent_id" is equal to $current_category_id.

Note: The value of $current_category_id variable comes from application_top.php

Let's check the osCommerce database table "categories" so that you have a better idea the records in the table.

Table categories

osCommerce database table categories

As mentioned in example 1, the value of $current_category_id reference will come from application_top.php. In order to simulate the osCommerce MySQL database query, we will assign the value of $current_category_id as following.

Case 1: $current_category_id = 1 (Hardware)

<?php

require('includes/application_top.php');

// simulate the following $current_category_id references come from application_top.php
$current_category_id = 10;

$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);

echo 'The total category parent is: ' . $category_parent ['total'];

?>

Save the file as osc-mysql-database-query-function-example-4.php in the home directory (catalog) of your osCommerce website.

PHP example fileosc-mysql-database-query-function-example-4.zip

Open a browser and access the URL of the file. The following output should appear on the screen:

The total category parent is: 8

The following diagram explains why the total number of records are 8.

osCommerce database table categories

This is the end of osCommerce tutorial PHP perform query on MySQL Database.