Querying the Database

Making a database query involves preparing a SQL statement, sanitizing any parameters to be passed to it, and submitting it for a result. The result can then be inspected for the kind of response received.

Preparing a Query

A typical query is built like this:

$sql = "SELECT p.products_id, p.products_model, pd.products_name
        FROM " . TABLE_PRODUCTS . " p
        LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd USING (products_id)
        WHERE pd.languages_id = :lang_id
        LIMIT 10";

Note the inline inclusion of the TABLE_PRODUCTS and TABLE_PRODUCTS_DESCRIPTION constants. These are used so that the appropriate DB_PREFIX is automatically included if set.

Also note the use of tablename aliases p and pd. These are placed after the tablename to avoid having to use the full tablename constants repeatedly every time a table needs to be mentioned.

The :lang_id placeholder is used for sanitization, as described next:

Sanitizing Inputs

A query should never just blindly use any received data. It should always sanitize the input variable before using it in a query.

We use bindVars for this.

Following the code example above, the next line of code would be:

global $db;
$sql = $db->bindVars($sql, ':lang_id', $_SESSION['languages_id'], 'integer');

Here, the $sql variable is reset to the parsed response of the bindVars call.

The first parameter is $sql which is the query from the code example above.

The second parameter is the placeholder. Notice the use of the : prefix. The string here must be unique and only appear in the query once, as bindVars does a search-and-replace (str_replace) of this value using the next parameter:

The third parameter is the value to be substituted in place of the second parameter.

The fourth parameter is the kind of sanitization rule to apply while substituting.

Valid sanitization rules are:

Alternative to BindVars

For very simple queries where you want to handle the casting manually, or just treat the value as a string, you may use zen_db_input() or $db->prepare_input():

$sql = "SELECT p.products_id, p.products_model, pd.products_name
        FROM " . TABLE_PRODUCTS . " p
        LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd USING (products_id)
        WHERE pd.languages_id = " . (int)zen_db_input($_POST['user_language_id']) . "
        LIMIT 10";

NOTE: zen_db_input() is an alias for $db->prepare_input(), which runs mysqli_real_escape_string() on its argument.

Running the Query

The prepared query can now be executed using:

$results = $db->Execute($sql);

Parsing the Query Response

Checking for Empty Result Set

If there are no results found, then $results->EOF will be true.

The RecordCount will also be 0. See below.

Checking for Number of Records Found

The number of records retrieved from a SELECT query can be determined with:

$records = $results->RecordCount();

Checking Number of Affected Rows

If the query was an INSERT, UPDATE, REPLACE or DELETE then the number of affected rows can be determined using:

$affected_rows = $db->affectedRows();

Reading the New Record ID

If the query was an INSERT statement on an auto-incrementing table, then the inserted record ID number can be determined using:

$inserted_record_id = $db->insert_ID();

Reading the Retrieved Records

If the query was a SELECT statement, then the results will be in an Iterable queryFactoryResult object.

These results can be read in two ways:

Single Row Responses

If the result is a single record, simply read it from the ->fields['column_name'] array:

$prod_name = $results->fields['products_name'];

Multiple Row Responses

If there are multiple rows returned, the most efficient way to iterate through the results is using a foreach() loop:

foreach($results as $result) {
  echo 'Product ID: ' . $result['products_id'] . ': ' . $result['products_name'] . "<br>\n";
}

This has worked since Zen Cart 1.5.5.

An older more verbose syntax exists in legacy code. The following accomplishes the same as the foreach above:

while (!$results->EOF) {
  echo 'Product ID: ' . $result->fields['products_id'] . ': ' . $result->fields['products_name'] . "<br>\n";
  $results->MoveNext();
}

Advanced

Randomized Query Responses

If you wish to have the query_factory pick a random record from the query result, two steps are required:

a) instead of $db->Execute(), call $db->ExecuteRandomMulti() when running the query

b) instead of $db->MoveNext(), call $db->MoveNextRandom() when iterating through the results

In core code this is typically done in sideboxes and centerboxes when showing things like Specials and Featured Products, so that the customer doesn’t always see only the most recently edited items. It’s also helpful for search engines to not always see exactly the same content on every visit.

Also note that randomizing the data like this means the page cannot be cached, so if you’re using external caching systems to index your page (like Cloudflare), you may not see the randomized results change on every page hit due to stale cache.

SQL Injection

Your best defense against potential SQL injection attacks is to sanitize inputs as noted above. Simply using a POSTed variable in a query is not safe!

$query = $db->Execute("UPDATE " . TABLE_CUSTOMERS . " SET customers_firstname = '" . $_POST['name'] . "'";    // DO NOT DO THIS! 

Instead, use bindVars as described above.

$query = $db->Execute("UPDATE " . TABLE_CUSTOMERS . " SET customers_firstname = :name:";    // DO THIS INSTEAD! 
$query = $db->bindVars($query, ':name:', $_POST['name'], 'string');