Dynamic Cloned storepickup Shipping Module

[dlm50]

Zen Cart 1.5.4
Fresh install – no upgrades
No plugins/addons
PHP Version 5.4.40
MySQL 5.5.42-37.1
One-click install from Bluehost where the cart is hosted
Researched FAQ and searched forum

In the past few years I have created several custom farm applications using Zen Cart where I clone the “storepickup” shipping module to display multiple Buying Club location options during checkout. With as many as 50 locations per farm with frequent changes to addresses, I made the pages dynamic and linked to a table named “location” so that I wouldn't have to hand code all the changes.

This has worked quite well until a few months ago when I found that I have to make a direct connection to the database from within the cloned page code or would get a failure to connect database error within the Zen Cart Admin shipping page. I am guessing this change is related to some security upgrades in recent versions of Zen Cart.

Anyway I added the connection code and it works, but takes extra time to configure and probably unnecessary. Of course I want to be sure to avoid sql injection.

Here is the page code for one of the clones that worked well previously:

$query = "SELECT title FROM location WHERE id=11";
$mysql_result = @ mysql_query ($query)
or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');

$row = @ mysql_fetch_row ($mysql_result)
or die ("Try a different query - this one did not return any rows.");
$title=$row[0];

$query2 = "SELECT text FROM location WHERE id=11";
$mysql_result2 = @ mysql_query ($query2)
or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');

$row2 = @ mysql_fetch_row ($mysql_result2)
or die ("Try a different query - this one did not return any rows.");
$text=$row2[0];

define('MODULE_SHIPPING_LOCATION11PICKUP_TEXT_TITLE', $title);
define('MODULE_SHIPPING_LOCATION11PICKUP_TEXT_DESCRIPTION', 'Delivery Location');
define('MODULE_SHIPPING_LOCATION11PICKUP_TEXT_WAY', $text); 

Now, to avoid coding a special mysql connection, I am trying things like:

$query = $db->Execute("SELECT title FROM location WHERE id=10");

No error, but no data displayed on the admin shipping page.

Any thoughts or suggestions on how to get this to work and make sure it is secure would be appreciated. Thanks in advance for any help provided. Dave

[DrByte]

Can you share the entire module file?

I suspect you simply need to global $db; in the top of the function where you're using $db. But there are probably some additional things too, hence the request to see the whole file.


(Also, since v1.5.2 Zen Cart now uses mysqli_xxxxx() functions, not the obsolete mysql_xxxxx() functions, since modern versions of PHP no longer support the old mysql_xxxx() functions. You're correct to switch to using the $db->Execute() calls, since that will keep you more current/compatible and probably more secure.)

[dlm50]

Thanks for your prompt reply. Here is the corresponding module code for the cloned file.

<?php
/**
 * @package shippingMethod
 * @copyright Copyright 2003-2006 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: annapolispickup.php 4821 2006-10-23 10:54:15Z drbyte $
 */
/**
 * Store-Pickup / Will-Call shipping method
 *
 */
class location11pickup extends base {
  /**
   * $code determines the internal 'code' name used to designate "this" payment module
   *
   * @var string
   */
  var $code;
  /**
   * $title is the displayed name for this payment method
   *
   * @var string
   */
  var $title;
  /**
   * $description is a soft name for this payment method
   *
   * @var string
   */
  var $description;
  /**
   * module's icon
   *
   * @var string
   */
  var $icon;
  /**
   * $enabled determines whether this module shows or not... during checkout.
   *
   * @var boolean
   */
  var $enabled;
  /**
   * constructor
   *
   * @return location11pickup
   */
  function location11pickup() {
    global $order, $db;

    $this->code = 'location11pickup';
    $this->title = MODULE_SHIPPING_LOCATION11PICKUP_TEXT_TITLE;
    $this->description = MODULE_SHIPPING_LOCATION11PICKUP_TEXT_DESCRIPTION;
    $this->sort_order = MODULE_SHIPPING_LOCATION11PICKUP_SORT_ORDER;
    $this->icon = '';
    $this->tax_class = MODULE_SHIPPING_LOCATION11PICKUP_TAX_CLASS;
    $this->tax_basis = MODULE_SHIPPING_LOCATION11PICKUP_TAX_BASIS;
    $this->enabled = ((MODULE_SHIPPING_LOCATION11PICKUP_STATUS == 'True') ? true : false);

    if ( ($this->enabled == true) && ((int)MODULE_SHIPPING_LOCATION11PICKUP_ZONE > 0) ) {
      $check_flag = false;
      $check = $db->Execute("select zone_id from " . TABLE_ZONES_TO_GEO_ZONES . "
                             where geo_zone_id = '" . MODULE_SHIPPING_LOCATION11PICKUP_ZONE . "'
                             and zone_country_id = '" . $order->delivery['country']['id'] . "'
                             order by zone_id");
      while (!$check->EOF) {
        if ($check->fields['zone_id'] < 1) {
          $check_flag = true;
          break;
        } elseif ($check->fields['zone_id'] == $order->delivery['zone_id']) {
          $check_flag = true;
          break;
        }
        $check->MoveNext();
      }

      if ($check_flag == false) {
        $this->enabled = false;
      }
    }
  }
  /**
   * Obtain quote from shipping system/calculations
   *
   * @param string $method
   * @return array
   */
  function quote($method = '') {
    global $order;

    $this->quotes = array('id' => $this->code,
                          'module' => MODULE_SHIPPING_LOCATION11PICKUP_TEXT_TITLE,
                          'methods' => array(array('id' => $this->code,
                                                   'title' => MODULE_SHIPPING_LOCATION11PICKUP_TEXT_WAY,
                                                   'cost' => MODULE_SHIPPING_LOCATION11PICKUP_COST)));

    if ($this->tax_class > 0) {
      $this->quotes['tax'] = zen_get_tax_rate($this->tax_class, $order->delivery['country']['id'], $order->delivery['zone_id']);
    }

    if (zen_not_null($this->icon)) $this->quotes['icon'] = zen_image($this->icon, $this->title);

    return $this->quotes;
  }
  /**
   * Check to see whether module is installed
   *
   * @return boolean
   */
  function check() {
    global $db;
    if (!isset($this->_check)) {
      $check_query = $db->Execute("select configuration_value from " . TABLE_CONFIGURATION . " where configuration_key = 'MODULE_SHIPPING_LOCATION11PICKUP_STATUS'");
      $this->_check = $check_query->RecordCount();
    }
    return $this->_check;
  }
  /**
   * Install the shipping module and its configuration settings
   *
   */
  function install() {
    global $db;
    $db->Execute("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) values ('Enable Store Pickup Shipping', 'MODULE_SHIPPING_LOCATION11PICKUP_STATUS', 'True', 'Do you want to offer In Store rate shipping?', '6', '0', 'zen_cfg_select_option(array(\'True\', \'False\'), ', now())");
    $db->Execute("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Shipping Cost', 'MODULE_SHIPPING_LOCATION11PICKUP_COST', '0.00', 'The shipping cost for all orders using this shipping method.', '6', '0', now())");
    $db->Execute("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, set_function, date_added) values ('Tax Class', 'MODULE_SHIPPING_LOCATION11PICKUP_TAX_CLASS', '0', 'Use the following tax class on the shipping fee.', '6', '0', 'zen_get_tax_class_title', 'zen_cfg_pull_down_tax_classes(', now())");
    $db->Execute("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) values ('Tax Basis', 'MODULE_SHIPPING_LOCATION11PICKUP_TAX_BASIS', 'Shipping', 'On what basis is Shipping Tax calculated. Options are<br />Shipping - Based on customers Shipping Address<br />Billing Based on customers Billing address<br />Store - Based on Store address if Billing/Shipping Zone equals Store zone', '6', '0', 'zen_cfg_select_option(array(\'Shipping\', \'Billing\'), ', now())");
    $db->Execute("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, use_function, set_function, date_added) values ('Shipping Zone', 'MODULE_SHIPPING_LOCATION11PICKUP_ZONE', '0', 'If a zone is selected, only enable this shipping method for that zone.', '6', '0', 'zen_get_zone_class_title', 'zen_cfg_pull_down_zone_classes(', now())");
    $db->Execute("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Sort Order', 'MODULE_SHIPPING_LOCATION11PICKUP_SORT_ORDER', '0', 'Sort order of display.', '6', '0', now())");
  }
  /**
   * Remove the module and all its settings
   *
   */
  function remove() {
    global $db;
    $db->Execute("delete from " . TABLE_CONFIGURATION . " where configuration_key in ('" . implode("', '", $this->keys()) . "')");
  }
  /**
   * Internal list of configuration keys used for configuration of the module
   *
   * @return array
   */
  function keys() {
    return array('MODULE_SHIPPING_LOCATION11PICKUP_STATUS', 'MODULE_SHIPPING_LOCATION11PICKUP_COST', 'MODULE_SHIPPING_LOCATION11PICKUP_TAX_CLASS', 'MODULE_SHIPPING_LOCATION11PICKUP_TAX_BASIS', 'MODULE_SHIPPING_LOCATION11PICKUP_ZONE', 'MODULE_SHIPPING_LOCATION11PICKUP_SORT_ORDER');
  }
}
?>

[DrByte]

Hmmmm ... I don't see those queries from your first post anywhere in your 2nd post.

[dlm50]

When I put the queries in the function file, I get no results. That is part of the problem I am having where I have to put the db connection and query in the language file. There I can get it to work, but is a nuisance and I know is not a best practice (separation of business logic from presentation logic).

In other words, when I put this into the shipping module function file I get no error but no data in the admin:

$query = $db->Execute("SELECT text FROM location WHERE id=10");
$mysql_result = @ mysql_query ($query)
    or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');
$row = @ mysql_fetch_row ($mysql_result)
    or die ("Try a different query - this one did not return any rows.");
$title=$row[0];

$query2 = $db->Execute("SELECT title FROM location WHERE id=10");
$mysql_result2 = @ mysql_query ($query2)
    or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');
$row2 = @ mysql_fetch_row ($mysql_result2)
    or die ("Try a different query - this one did not return any rows.");
$text=$row2[0]; 

I know I am probably missing something basic here, but don't know what it is.

[lat9]

I'm not clear on exactly what you're trying to do, but (echoing DrByte) you're on the right path using the built-in $db functions to do your database access.

To access and echo the text field from the location table for an id of 10, you'd code:

$query = $db->Execute ("SELECT text FROM location WHERE id = 10 LIMIT 1");
if ($query->EOF) {
  echo 'Did not find an entry with an id of 10';
} else {
  echo $query->fields['text'];
}

The EOF flag is set on an access if no match was found (as above) or when a multi-record returning query's loop has finished:

$query = $db->Execute ("SELECT `text`, `id` FROM location WHERE id > 10");
if ($query->EOF) {
  echo 'There are no items with an id greater than 10.';
} else {
  while (!$query->EOF) {
    echo 'The text for item ' . $query->fields['id'] . ' is: ' . $query->fields['text'];
    $query->MoveNext ();
  }
}

The MoveNext () function iterates to the next record returned by the query. If you forget this, you'll receive a server 500 error and a debug-log that indicates either a time-out or an out-of-memory condition since the while-loop will execute an infinite number of times.

[dlm50]

lat9: Thanks for the feedback and info.

What I am trying to do is make my code better. It does work as is, but with the changes in Zen Cart (as noted by DrByte), it has become increasing messy and inefficient -- it feels like an improvisation/hack. So I want to learn how to bring it up to standard, so to speak.

I continue to try variations, by putting my new code in the functions file in a mysqli format, but as stated -- no errors but no data showing up in the shipping admin page. As much as anything, I guess I am trying to learn more about how Zen Cart works so that I can do it better.

I am also working on a simple plugin that will work in association with this so that the user can edit the db table (location) from the tool menu in the admin. But have a similar situation where I can make it work if I simply write all the code into a free-standing file, but is inefficient since I have to make a special db call to display and edit the table contents (which is unique for each db and website).

I have tried making includes and variations on what I have shared in previous posts, but not quite there yet. Obviously I don't understand how the parts of Zen Cart work together, but will keep trying to figure it out.

[lat9]

When you use the $db object in a function (or in a class function), you need to declare the $db as a global variable prior to its first use:

function myfunction () {
  global $db;

  ...
}

[dlm50]

I have been including my code within this function, which I assumed would make the call to the db:

function install() {
global $db;

Following your advice I made it its own function:

function myshipping () {
global $db;
$query = $db->Execute("SELECT text FROM location WHERE id=10");
$mysqli_result = @ mysqli_query ($query)
or die ("Query '$query' failed with error message: \"" . mysqli_error () . '"');
$row = @ mysqli_fetch_row ($mysqli_result)
or die ("Try a different query - this one did not return any rows.");
$title=$row[0];

$query2 = $db->Execute("SELECT title FROM location WHERE id=10");
$mysqli_result2 = @ mysqli_query ($query2)
or die ("Query '$query' failed with error message: \"" . mysql_error () . '"');
$row2 = @ mysqli_fetch_row ($mysqli_result2)
or die ("Try a different query - this one did not return any rows.");
$text=$row2[0];
}

Still, no error, but no data showing up. I wonder if it is simply my attempt at moving from mysql to mysqli in the way I fetch the data. I will keep working on it from that angle to see if I can get it to work.

[lat9]

Lose the direct mysqli_* calls and return the text field value to the caller:

function myshipping () {
  global $db;
  $query = $db->Execute("SELECT text FROM location WHERE id=10");
  if ($query->EOF) {
    die ("Try a different query - this one did not return any rows.");
  }
  $text = $query->fields['text'];
  return $text;
}