Does this Generate too many Database Queries?

[lukeryan]

Hello all,

 

I have installed the "Local Deliver Option" module version 1.4 - http://addons.oscommerce.com/info/1993 - onto our site so we can offer free local delivery to nearby postcodes/zip codes. The way this contribution works is in the admin side you enter all the postcodes where you want this option to be enabled for so it only displays to customers during the checkout process who live in those postcodes.

 

My real concern is if I have several hundred postcodes entered does this mean the database queries all of them every time any customer checks out, and would this lead to excessive drain on the database with that number of queries?

 

Thanks to anyone who can help!

 

Regards,

Luke

[♥mdtaylorlrim]

Hello all,

 

I have installed the "Local Deliver Option" module version 1.4 - http://addons.oscommerce.com/info/1993 - onto our site so we can offer free local delivery to nearby postcodes/zip codes. The way this contribution works is in the admin side you enter all the postcodes where you want this option to be enabled for so it only displays to customers during the checkout process who live in those postcodes.

 

My real concern is if I have several hundred postcodes entered does this mean the database queries all of them every time any customer checks out, and would this lead to excessive drain on the database with that number of queries?

 

Thanks to anyone who can help!

 

Regards,

Luke

Depends on how it's written. If correct, it will only do a single query.

[lukeryan]

Depends on how it's written. If correct, it will only do a single query.

 

Thanks for that Mark. I wonder, this is the code for the local delivery shipping module does this look like just the single query to you? Sorry, my mysql skills are not that great just yet!

 

Much appreciated, Luke

 

<?php

/*

$Id: dly.php,v 1.2 2004/11/18 16:29:56 ChBu Exp $

Dr. Bill Bailey, http://www.lowcarbnexus.com

Zoe Lozada Almodovar, [email protected]

 

VERY slight hack of dly.php by Dr. Bill Bailey, http://www.lowcarbnexus.com

VERY slight hack of spu.php by dion made from original code by M. Halvorsen (http://www.arachnia-web.com)

to allow local delivery from warehouse. Dr. Bill Bailey, http://www.lowcarbnexus.com

 

Made to work with latest check-out procedure by Matthijs (Mattice)

>> e-mail: [email protected]

>> site: http://www.matthijs.org

 

CHANGES (v1.4):

- added code to convert admin supplied postal codes to upper case.

- added code to remove spaces from both admin & user supplied postal codes while comparing them.

CHANGES (v1.3):

- added code to convert user supplied postal codes to upper case.

CHANGES (v1.2):

- added selection of post codes (city codes) where this delivery is possible, it will not show up if the delivery is not

in a selected city of the selected zone.

CHANGES (v1.1):

- added Minimum Total Order Value to configuration

- updated code

- added icon references

CHANGES (v1.0):

- formatted to work with latest checkout procedure

- removed icon references

- updated the db queries

 

Released under the GNU General Public License

 

 

*/

 

class dly {

var $code, $title, $description, $icon, $enabled;

 

// class constructor

function dly() {

global $order;

 

$this->code = 'dly';

$this->title = MODULE_SHIPPING_DLY_TEXT_TITLE;

$this->description = MODULE_SHIPPING_DLY_TEXT_DESCRIPTION;

$this->sort_order = MODULE_SHIPPING_DLY_SORT_ORDER;

$this->icon = ''; // To remove icon change this line to: $this->icon = '';

$this->enabled = MODULE_SHIPPING_DLY_STATUS;

// Beg Minimum Order Total required to activate module

$this->min_order = MODULE_SHIPPING_DLY_MINIMUM_ORDER_TOTAL;

 

if ( ($order->info['total']) < ($this->min_order) ) {

$this->enabled = false;

}

// End Minimum Order Total required to activate module

 

if ( ($this->enabled == true) && ((int)MODULE_SHIPPING_DLY_ZONE > 0) ) {

$check_flag = false;

$check_query = tep_db_query("select zone_id from " . TABLE_ZONES_TO_GEO_ZONES . " where geo_zone_id = '" . MODULE_SHIPPING_DLY_ZONE . "' and zone_country_id = '" . $order->delivery['country']['id'] . "' order by zone_id");

//$zipcodes=split (',',MODULE_SHIPPING_DLY_ZIPCODE);

$zip_up = MODULE_SHIPPING_DLY_ZIPCODE;

$zip_up=strtoupper($zip_up);

$zip_up=str_replace (" ", "", $zip_up);

$zipcodes=split (',',$zip_up);

$order->delivery['postcode']=strtoupper($order->delivery['postcode']);

$order->delivery['postcode']=str_replace (" ", "", $order->delivery['postcode']);

while ($check = tep_db_fetch_array($check_query)) {

if ($check['zone_id'] < 1) {

if ((in_array($order->delivery['postcode'],$zipcodes )) or (MODULE_SHIPPING_DLY_ZIPCODE==''))

$check_flag = true;

break;

} elseif ($check['zone_id'] == $order->delivery['zone_id']) {

if ((in_array($order->delivery['postcode'],$zipcodes )or (MODULE_SHIPPING_DLY_ZIPCODE=='')))

$check_flag = true;

break;

}

}

 

if ($check_flag == false) {

$this->enabled = false;

}

}

}

 

// class methods

 

function quote($method = '') {

global $order;

 

$this->quotes = array('id' => $this->code,

'module' => MODULE_SHIPPING_DLY_TEXT_TITLE,

'methods' => array(array('id' => $this->code,

'title' => MODULE_SHIPPING_DLY_TEXT_WAY,

'cost' => MODULE_SHIPPING_DLY_COST)));

 

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

 

return $this->quotes;

}

 

function check() {

if (!isset($this->_check)) {

$check_query = tep_db_query("select configuration_value from " . TABLE_CONFIGURATION . " where configuration_key = 'MODULE_SHIPPING_DLY_STATUS'");

$this->_check = tep_db_num_rows($check_query);

}

return $this->_check;

}

 

function install() {

tep_db_query("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Enable Local Delivery', 'MODULE_SHIPPING_DLY_STATUS', '1', 'Do you want to offer Local Delivery?', '6', '5', now())");

tep_db_query("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Local Delivery Cost', 'MODULE_SHIPPING_DLY_COST', '0.00', 'What is the Local Delivery cost? (The Handling fee will NOT be added.)', '6', '6', now())");

// Beg Minimum Order Total required to activate module

tep_db_query("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Minimum Order Total', 'MODULE_SHIPPING_DLY_MINIMUM_ORDER_TOTAL', '0.00', 'What is the Minimum Order Total required for this option to be activated.', '6', '7', now())");

// End Minimum Order Total required to activate module

// Begin ZipCode

tep_db_query("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Zip codes', 'MODULE_SHIPPING_DLY_ZIPCODE', '', 'Only enable this shipping method for these zip codes. Separate with comma if several, empty if all.', '6', '7', now())");

// End ZipCode

tep_db_query("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_DLY_ZONE', '0', 'If a zone is selected, only enable this shipping method for that zone.', '6', '0', 'tep_get_zone_class_title', 'tep_cfg_pull_down_zone_classes(', now())");

tep_db_query("insert into " . TABLE_CONFIGURATION . " (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) values ('Sort Order', 'MODULE_SHIPPING_DLY_SORT_ORDER', '3', 'Sort order of display.', '6', '0', now())");

}

 

 

function remove() {

$keys = '';

$keys_array = $this->keys();

for ($i=0; $i<sizeof($keys_array); $i++) {

$keys .= "'" . $keys_array[$i] . "',";

}

$keys = substr($keys, 0, -1);

 

tep_db_query("delete from " . TABLE_CONFIGURATION . " where configuration_key in (" . $keys . ")");

}

 

function keys() {

return array('MODULE_SHIPPING_DLY_STATUS',

'MODULE_SHIPPING_DLY_COST',

// Beg Minimum Order Total required to activate module

'MODULE_SHIPPING_DLY_MINIMUM_ORDER_TOTAL',

// End Minimum Order Total required to activate module

// Begin ZipCode

'MODULE_SHIPPING_DLY_ZIPCODE',

// End ZipCode

'MODULE_SHIPPING_DLY_SORT_ORDER',

'MODULE_SHIPPING_DLY_ZONE');

}

}

?>

[♥mdtaylorlrim]

Yeah, that's a pretty simple query.

[lukeryan]

Yeah, that's a pretty simple query.

 

Thank you Mark!

[lukeryan]

Just had a thought about this contribution - eventually I'd like to be able to offer local delivery to a larger area, is there any way to alter the code to make it work so it searches for just the start of a postcode e.g. rather than CF35 1AA, CF35 1AB, CF35 1AC etc.... using just CF35 so any delivery address beginning with that would be eligible for local delivery.

 

Any ideas please?

 

Thanks in advance,

Luke

[♥mdtaylorlrim]

Just had a thought about this contribution - eventually I'd like to be able to offer local delivery to a larger area, is there any way to alter the code to make it work so it searches for just the start of a postcode e.g. rather than CF35 1AA, CF35 1AB, CF35 1AC etc.... using just CF35 so any delivery address beginning with that would be eligible for local delivery.

 

Any ideas please?

 

Thanks in advance,

Luke

Probably. I don't know the code but if you locate the sql that determines if a postal code can utilize the local delivery module you will find a sql that determines this. If you can post the sql here someone can alter it for you I'm sure.

[lukeryan]

Hi again Mark,

 

I'm not sure where the sql for this would be as the only files for this contribution was the one posted above and this one below located in catalog/includes/languages/english/modules/shipping:

 

<?php

/*

$Id: dly.php,v 1.0 2004/03/30 16:15:42 drbill Exp $

 

Slightly (very slightly!) hacked by Zoe Lozada Almodovar, [email protected]

Slightly (very slightly!) hacked by Dr. Bill Bailey, http://www.lowcarbnexus.com

Modified (slight hack) of spu by: Michael Halvorsen

http://www.arachnia-web.com

 

Copyright © 2002 Arachnia-web Development.

Released under the GNU General Public License.

May be used and modified without permission.

*/

 

define('MODULE_SHIPPING_DLY_TEXT_TITLE', 'Local Delivery Service');

define('MODULE_SHIPPING_DLY_TEXT_DESCRIPTION', 'Delivered within a 10 mile radius of our warehouse location.');

define('MODULE_SHIPPING_DLY_TEXT_WAY', 'Delivery the same day or if ordered after 5pm the next working day.');

?>

 

Anyone else out there who could lend a hand to see if we can get this working. Thanks!

 

Luke

[♥mdtaylorlrim]

I think the code you want to alter is going to be in the dly3.php file. You will want to take the order zip code and reduce it to the first three characters in the code. Probably in this part...

 

		$order->delivery['postcode'] = strtoupper($order->delivery['postcode']);
	$order->delivery['postcode'] = str_replace (' ', '', $order->delivery['postcode']);

Like this:

		$order->delivery['postcode'] = strtoupper($order->delivery['postcode']);
	$order->delivery['postcode'] = str_replace (' ', '', $order->delivery['postcode']);
               $order->delivery['postcode'] = substr(order->delivery['postcode'],0,3);  //for using the zip-3

 

Then when creating your tables of zip codes you can then only use the first three digits of the zip codes.

 

Looks like there are about 22 occurrences of that code in the file. Also, this may not be all that is required, and may not even be the right code to change. If you want to try this be sure and use a test store and keep backups.