Zen Cart Database Abstraction Layer

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 :productID: 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 ':productID: 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