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:
rule | meaning |
---|---|
integer |
casts to integer (ignores all fractions, and treats it as 0 if the value is not numeric) |
float |
casts to a float, or an empty/zero/falsey value is cast to 0 |
string |
returns a quote-wrapped string after sanitizing by MySQL’s escaping rules (note: string NULL is treated as a literal null response). |
stringIgnoreNull |
quote-wrapped string which ignores if the string is NULL , sanitized using MySQL escaping rules |
noquotestring |
no quote-wrapping version of stringIgnoreNull |
currency |
alias of stringIgnoreNull |
date |
same as string but treats both upper-and-lowercase NULL and null as null |
passthru |
no treatment; this should be avoided; only suitable for data already sanitized by a different means |
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 essentially runs mysqli_real_escape_string()
behind the scenes).
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();
}