Zen Cart Database Abstraction Layer

[Abraham]

Basic Example

A sample database inquiry to retrieve the model number of a specified product number would occur like this:

$theProductId = 25;
global $db;
$sql = "select products_model from " . TABLE_PRODUCTS . " where products_id = :productID:";
$sql = $db->bindVars($sql, ':productID:', $theProductId, 'integer');
$result = $db->Execute($sql);

if ($result->RecordCount() > 0) {
  echo 'Model number = ' . $result->fields['products_model'];
} else {
  echo 'Sorry, no record found for product number ' . $theProductId;
}

Understanding the basic example

global $db;

This makes the $db object (which is used to communicate to the database) available for use in the scope of your code.

$sql = "select products_model from " . TABLE_PRODUCTS . " where products_id = :productID:";

This specifies the SQL query which you intend to run. You can determine specific fields and tables by referencing the schema documentation or looking at the raw database structure directly.

The TABLE_PRODUCTS constant is used in order to support table-prefixes, since the constant will automatically contain the prefix, according to the logic in the /includes/filenames.php script and the DB_PREFIX value in your /includes/configure.php file.

Note the roductID: is a placeholder which is handled by the next line:

$sql = $db->bindVars($sql, ':productID:', $theProductId, 'integer');

This essentially takes the $sql variable contents from the previous line (the query to be run) and replaces all occurrences of 'roductID: with the value of $theProductId AFTER first ensuring that the $theProductId is an 'integer' value. (This is for security reasons so nobody can do an SQL injection hack against your script.) Additional bindVars datatypes besides 'integer' are listed below.

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

This runs the actual query, whose results are stored in the database object named $result.

if ($result->RecordCount() > 0) {
<pre>  echo 'Model number = ' . $result->fields['products_model'];
} else {
  echo 'Sorry, no record found for product number ' . $theProductId;
}

This checks to see whether there was more than 0 records returned from the query. If yes, the model number is echoed to the screen. If not, a message to that effect is displayed



Example of looping through multiple records

A simple example to list all the currencies in your database, along with the currently-configured exchange rates:

global $db;
$sql = "select title, code, value, last_updated from " . TABLE_CURRENCIES;
$result = $db->Execute($sql);

if ($result->RecordCount() > 0) {
  while (!$result->EOF) {
    echo '<p>Currency name: ' . $result->fields['title'];
    echo ', code: ' . $result->fields['code'];
    echo ', Exchange Rate: ' . $result->fields['value'];
    echo '</p>';
    $result->MoveNext();
  }
} else {
  echo '<p>Sorry, no currencies found.</p>';
}

Understanding the loop example

global $db;

See description in previous example.

$sql = "select title, code, value, last_updated from " . TABLE_CURRENCIES;

SQL query to be run. See explanation in previous example.

NOTE: There is no use of bindVars() here, because there is no parameter supplied as selection criteria for the query.

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

Execute query - same as previous example.

if ($result->RecordCount() > 0) {

Check to see if we have any results.

while (!$result->EOF) {

This starts a loop through the results returned from the database.

    echo '<p>Currency name: ' . $result->fields['title'];
    echo ', code: ' . $result->fields['code'];
    echo ', Exchange Rate: ' . $result->fields['value'];
    echo '</p>';

This simply echoes the retrieved data to the screen. Note the reference to the individual fields via $result->fields['field-name']

 $result->MoveNext();
  }

This tells it to jump to the next record in the set of results, and go back through the output again ... repeatedly until the "while" condition meets EOF (end of file, aka end of returned results).

} else {
  echo '<p>Sorry, no currencies found.</p>';
}

If the IF statement above (for RecordCount) failed ... meaning we have 0 records returned ... then we display a message to that effect.


Finding out what record number was inserted

When you run a query with an INSERT statement in it, you can find out the record number assigned to the new line you added by referencing $db->Insert_ID(), like this:

global $db;
$sql = "insert into " . TABLE_SOMETHING . " (fieldname1, fieldname2) values (:value1:, :value2:)";
$sql = $db->bindVars($sql, ':value1:', $valueOne, 'integer');
$sql = $db->bindVars($sql, ':value2:', $valueTwo, 'string');
$result = $db->Execute($sql);
$newRecordId = $db->Insert_ID();
echo 'The new record added was number: ' . $newRecordId;

BindVars Datatypes

Available datatypes for use in bindVars() parameters include the following. In practice, the first 4 are used the most:

integer - ensures that the supplied data is an integer
string - ensures that the supplied data is a string, and escapes any quotes, and adds quotes around it
noquotestring - ensures that the supplied data is a string, and escapes any quotes in it, but doesn't add quotes around it
float - ensures that the supplied data is numeric, including ensuring that a blank string is converted to a 0
currency - similar to 'string'
date - similar to 'string'
enum - similar to mysql enum type: forces data to be within available list of choices
regexp - checks that the supplied parameter is formatted as a regular expression


Escaping Content

Many people are tempted to directly use "mysql_escape_string()" function calls, or merely use "add_slashes()". But the BEST way to do it is with BindVars as described above.

However, if you have a strong objection to using the safeties and protections that BindVars offers, and you know why you're not using it, then you could use $db->prepare_input($value).

For optimal future-proofing of your code, ALWAYS USE ZEN CART FUNCTIONS FOR ACCESSING THE DATABASE, and don't make direct calls to mysql_xxxxx() or mysqli_xxxxx() functions.