Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Also Purchased optimization (alternative)


boxtel

Recommended Posts

Normally, back in the old days with db2, we would optimize the databases according to the strict datamodelling principles. Then we would discover that optimizing for runtime became a must.

 

Therefore, I am a fan of taking as much processor/database intensive processes off-line. I have done that with the emailQueue and I thought why not also do the same for the also puchased products.

 

So here is what I was experimenting with :

 

1) create a separate table which holds the products id and the also purchased products id.

 

2) populate this table with a scheduled job which runs the normal also purchased query.

 

3) use the new table to do the displaying online.

 

 

The table :

 

#

# Table structure for table 'ap_preselection'

#

 

DROP TABLE IF EXISTS ap_preselection;

CREATE TABLE ap_preselection (

products_id int(11) unsigned NOT NULL default '0',

ap_id int(11) unsigned NOT NULL default '0',

PRIMARY KEY (ap_id,products_id)

) TYPE=MyISAM;

 

 

 

the job :

 

<?php

require('includes/configure.php');

require ('includes/configuration_cache_read.php');

require(DIR_WS_INCLUDES . 'filenames.php');

require(DIR_WS_INCLUDES . 'database_tables.php');

require(DIR_WS_FUNCTIONS . 'database.php');

tep_db_connect() or die('We are currently unavailable due to Maintenance..');

require(DIR_WS_FUNCTIONS . 'general.php');

define('TABLE_AP_PRESELECTION', 'ap_preselection');

$result = tep_db_query("delete from ap_preselection");

$product_query = tep_db_query("select products_id from products");

while ($product = tep_db_fetch_array($product_query)) {

$orders_query = tep_db_query("select distinct p.products_id

from " . TABLE_ORDERS_PRODUCTS . " opa, "

. TABLE_ORDERS_PRODUCTS . " opb, "

. TABLE_ORDERS . " o, "

. TABLE_PRODUCTS . " p

where opa.products_id = '" . $product['products_id'] . "' and

opa.orders_id = opb.orders_id and

opb.products_id != '" . $product['products_id'] . "' and

opb.products_id = p.products_id and

opb.orders_id = o.orders_id and

p.products_status = '1'");

 

while ($orders = tep_db_fetch_array($orders_query)) {

$sql_data_array = array('products_id' => $product['products_id'],

'ap_id' => $orders['products_id']);

tep_db_perform(TABLE_AP_PRESELECTION, $sql_data_array);

}

}

?>

Treasurer MFC

Link to comment
Share on other sites

Save this file as includes/modules/also_purchased_cache.php:

/*=======================================================================*\
|| #################### //-- SCRIPT INFO --// ########################## ||
|| #  Script name: Also Purchased module cache 
|| #  Contribution: N/A Custom Solution
|| #  Version: 1.2
|| #  Date: 20 November 2004
|| # ------------------------------------------------------------------ # ||
|| #################### //-- COPYRIGHT INFO --// ######################## ||
|| #  Copyright (C) 2005 Bobby Easland            # ||
|| #  Internet moniker: Chemo         	 # ||	
|| #  Contact: [email protected]             # ||
|| #  Commercial Site: http://gigabyte-hosting.com/         # ||
|| #  GPL Dev Server: http://mesoimpact.com/     	 # ||
|| #                       # ||
|| #  Script is intended to be used with:        # ||
|| #  osCommerce, Open Source E-Commerce Solutions         # ||
|| #  http://www.oscommerce.com              # ||
|| #  Copyright (c) 2003 osCommerce             # ||
|| ###################################################################### ||
\*========================================================================*/

$sd = mktime( date("H"), date("i"), date("s"), date("m"), date("d")-30, date("Y") );
$cutoff = date("Y-m-d H:i:s", $sd);
// Define SQL
   $sql = "SELECT p.products_id, p.products_model, p.products_image, pd.products_name 
	 FROM " . TABLE_ORDERS . " o, " .
	 TABLE_ORDERS_PRODUCTS . " opa, " . 
	 TABLE_ORDERS_PRODUCTS . " opb, " . 
	 TABLE_PRODUCTS . " p, " .
	 TABLE_PRODUCTS_DESCRIPTION . " pd 
	 WHERE o.date_purchased > '".$cutoff."'  
	 AND opa.products_id = '" . (int)$_REQUEST['products_id'] . "' 
	 AND opa.orders_id = opb.orders_id 
	 AND opb.products_id != '" . (int)$_REQUEST['products_id'] . "' 
	 AND opb.products_id = p.products_id 
	 AND opb.orders_id = o.orders_id 
	 AND p.products_status = '1' 
	 AND pd.products_id = p.products_id 
	 AND pd.language_id = '" . (int)$languages_id . "' 
	 GROUP BY p.products_id 
	 ORDER BY p.products_ordered DESC 
	 LIMIT " . MAX_DISPLAY_ALSO_PURCHASED;

// Define the cache filename
$filename = 'also_purchased-' . $language . '.cache' . (int)$_REQUEST['products_id'];

// Get the cached data
get_db_cache($sql, $orders, $filename, 86400);
   
// Set the number of elements
$num_products_ordered = sizeof($orders);

// Only output the data if there are more than the minimum
   if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
?>
<!-- also_purchased_products //-->
<?php
     $info_box_contents = array();
     $info_box_contents[] = array('text' => TEXT_ALSO_PURCHASED_PRODUCTS);

     new infoBoxHeading($info_box_contents, true, false);

     $row = 0;
     $col = 0;
     $info_box_contents = array();
     foreach ($orders as $order) {
       $info_box_contents[$row][$col] = array('align' => 'center',
                                              'params' => 'class="smallText" width="33%" valign="top"',
                                              'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $order['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $order['products_image'], $order['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $order['products_id']) . '">' . $order['products_name'] . '</a>');

       $col ++;
       if ($col > 2) {
         $col = 0;
         $row ++;
       } # end if $co > 2
     } # end while loop

     new contentBox($info_box_contents);
?>
<!-- also_purchased_products_eof //-->
<?php
   } # end if $numb_products_ordered

Change the cooresponding functions in includes/functions/cache.php like so:

////
//! Read in seralized data.
//  read_cache reads the serialized data in $filename and
//  fills $var using unserialize().
//  $var      -  The variable to be filled.
//  $filename -  The name of the file to read.
 function read_cache(&$var, $filename, $auto_expire = false){
$filename = DIR_FS_CACHE . $filename;
$success = false;

   if (($auto_expire == true) && file_exists($filename)) {
     $now = time();
     $filetime = filemtime($filename);
     $difference = $now - $filetime;

     if ($difference >= $auto_expire) {
       return false;
     }
   }

// try to open file
   if ($fp = @fopen($filename, 'r')) {
// read in serialized data
     $szdata = fread($fp, filesize($filename));
     fclose($fp);
// unserialze the data
     $var = unserialize($szdata);

     $success = true;
   }

   return $success;
 }

////
//! Get data from the cache or the database.
//  get_db_cache checks the cache for cached SQL data in $filename
//  or retreives it from the database is the cache is not present.
//  $SQL      -  The SQL query to exectue if needed.
//  $filename -  The name of the cache file.
//  $var      -  The variable to be filled.
//  $refresh  -  Optional.  If true, do not read from the cache.
 function get_db_cache($sql, &$var, $filename, $refresh = false){
   $var = array();

// check for the refresh flag and try to the data
   #if (($refresh == true)|| !read_cache($var, $filename)) {
   if (!read_cache($var, $filename, $refresh)) {
// Didn' get cache so go to the database.
//      $conn = mysql_connect("localhost", "apachecon", "apachecon");
     $res = tep_db_query($sql);
//      if ($err = mysql_error()) trigger_error($err, E_USER_ERROR);
// loop through the results and add them to an array
     while ($rec = tep_db_fetch_array($res)) {
       $var[] = $rec;
     }
// write the data to the file
     write_cache($var, $filename);
   }
 }

Next, change the code in product_info.php that calls the cache/file to this code:

/*
   if ((USE_CACHE == 'true') && empty($SID)) {
     echo tep_cache_also_purchased(3600);
   } else {
     include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS);
   }
*/
     include(DIR_WS_MODULES . 'also_purchased_cache.php');

Notice, this is the commented version so the original code is still there.

 

The last step is to add proper indexes on the appropriate tables.

 

TABLE orders

1. index => date_purchased

 

TABLE orders_products

1. index => orders_id (single column)

2. index => products_id (single column)

3. index => orders_id (single column)

 

TABLE products

1. index => products_status

 

All done.

 

This was developed on a store with about 82,000 orders and was having massive issues with the also_puchased module. The benefit is that it will cache the module at the data level and thus will be used for everyone regardless of being logged in or cookie acceptance. In addition, it is a slightly more optimized query and will usually execute in under .5 seconds versus the old 2.5 minute time of the original code. Of course, once cached it will render in under .001 seconds.

 

This has been tested on several high volume sites with each having greater than 50K orders. Even while generating the cache data the query will execute in roughly 1/1000th the normal time.

 

Bobby

Link to comment
Share on other sites

  • 1 month later...
Normally, back in the old days with db2, we would optimize the databases according to the strict datamodelling principles. Then we would discover that optimizing for runtime became a must.

 

Therefore, I am a fan of taking as much processor/database intensive processes off-line. I have done that with the emailQueue and I thought why not also do the same for the also puchased products.

 

So here is what I was experimenting with :

 

1) create a separate table which holds the products id and the also purchased products id.

 

2) populate this table with a scheduled job which runs the normal also purchased query.

 

3) use the new table to do the displaying online.

The table :

 

#

# Table structure for table 'ap_preselection'

#

 

DROP TABLE IF EXISTS ap_preselection;

CREATE TABLE ap_preselection (

  products_id int(11) unsigned NOT NULL default '0',

  ap_id int(11) unsigned NOT NULL default '0',

  PRIMARY KEY  (ap_id,products_id)

) TYPE=MyISAM;

the job :

 

<?php

require('includes/configure.php');

require ('includes/configuration_cache_read.php');

require(DIR_WS_INCLUDES . 'filenames.php');

require(DIR_WS_INCLUDES . 'database_tables.php');

require(DIR_WS_FUNCTIONS . 'database.php');

tep_db_connect() or die('We are currently unavailable due to Maintenance..');

require(DIR_WS_FUNCTIONS . 'general.php');

define('TABLE_AP_PRESELECTION', 'ap_preselection');

$result = tep_db_query("delete from ap_preselection");

$product_query = tep_db_query("select products_id from products");

while ($product = tep_db_fetch_array($product_query)) {

  $orders_query = tep_db_query("select distinct p.products_id

                                from " . TABLE_ORDERS_PRODUCTS . " opa, " 

                                      . TABLE_ORDERS_PRODUCTS . " opb, " 

                                      . TABLE_ORDERS . " o, " 

                                      . TABLE_PRODUCTS . " p

                where opa.products_id = '" . $product['products_id'] . "' and

                      opa.orders_id = opb.orders_id and

                      opb.products_id != '" . $product['products_id'] . "' and

                      opb.products_id = p.products_id and

                      opb.orders_id = o.orders_id and

                      p.products_status = '1'");

 

  while ($orders = tep_db_fetch_array($orders_query)) {

    $sql_data_array = array('products_id' => $product['products_id'],

                            'ap_id'      => $orders['products_id']);

    tep_db_perform(TABLE_AP_PRESELECTION, $sql_data_array);

  }

}

?>

 

most will be aware that the normal osc also purchased schema is somewhat limited as it only counts products as also purchased when they are purchased in the same order as the one you are looking at. If that product was also purchased by a customer which purchased this one but did that in a separate order, the product will not be selected.

 

Ofcourse there is a very good performance reason for this.

 

So now with this scheme of taking that query off-line in a scheduled job and putting the result in the ap_preselection table for fast online access we can engage in actually determining the real also purchased information.

 

So I have changed the ap_preselection job to do just that.

It selects products which customers also purchased regardless of whether that purchase was done in the same order as long as it was done by the same customer.

 

the code for the ap_preselection table filling job:

 

 

 

<?php

require('includes/configure.php');

require ('includes/configuration_cache_read.php');

require(DIR_WS_INCLUDES . 'filenames.php');

require(DIR_WS_INCLUDES . 'database_tables.php');

require(DIR_WS_FUNCTIONS . 'database.php');

tep_db_connect() or die('We are currently unavailable due to Maintenance..');

require(DIR_WS_FUNCTIONS . 'general.php');

define('TABLE_AP_PRESELECTION', 'ap_preselection');

$result = tep_db_query("delete from ap_preselection");

 

$product_query = tep_db_query("select distinct products_id

from products

order by products_id desc

");

 

while ($product = tep_db_fetch_array($product_query)) {

echo $product['products_id'];

// error_log ('pr: ' . $product['products_id']);

 

$customers_query = tep_db_query("select distinct o.customers_id

from orders_products op,

orders o

where op.products_id = '" . $product['products_id'] . "'

and op.orders_id = o.orders_id

and o.orders_status = '3'

");

 

while ($customers = tep_db_fetch_array($customers_query)) {

echo $customers['customers_id'];

// error_log (' cu: ' . $customers['customers_id']);

 

$ap_products_query = tep_db_query("select distinct op.products_id

from orders_products op,

orders o

where op.products_id != '" . $product['products_id'] . "'

and op.orders_id = o.orders_id

and o.orders_status = '3'

and o.customers_id = '" . $customers['customers_id'] . "'");

 

while ($ap_product = tep_db_fetch_array($ap_products_query)) {

echo $ap_product['products_id'];

// error_log (' ap: ' . $ap_product['products_id']);

 

$ap_check_query = tep_db_query("select products_id

from ap_preselection

where products_id = '" . $product['products_id'] . "'

and ap_id = '" . $ap_product['products_id'] . "'

");

 

if (!$ap_check = tep_db_fetch_array($ap_check_query)) {

$sql_data_array = array('products_id' => $product['products_id'],

'ap_id' => $ap_product['products_id']);

tep_db_perform(TABLE_AP_PRESELECTION, $sql_data_array);

}

}

}

}

?>

 

and the simple query you can use online in your boxes :

 

$ap_query = tep_db_query("select ap.ap_id,

p.products_image,

pd.products_name

from ap_preselection ap,

products_description pd,

products p

where ap.products_id = '" . (int)$_GET['products_id'] . "' and

p.products_id = ap.ap_id and

pd.products_id = ap.ap_id and

pd.language_id = '" . $languages_id . "' and

p.products_status = '1'

limit " . $max_search);

Treasurer MFC

Link to comment
Share on other sites

Please, for all those not much good, more clear!

Assemble your mind, and post a contribution, so we can made change!

Thanks Amanda and Bobby

Skype: centoasa

Skype: remigioruberto

Link to comment
Share on other sites

Hi,

 

I've installed this but the cron wouldn't run. I then opened appu.php in a browser window and received the error

 

Warning: main(includes/configuration_cache_read.php): failed to open stream: No such file or directory in /home/****/public_html/appu.php on line 3

 

Fatal error: main(): Failed opening required 'includes/configuration_cache_read.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/***/public_html/appu.php on line 3

 

ok, so that file doesn't exist on our server...let's try it after removing line 3 and see if any other problems appear...

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '/ shipped status' at line 6

 

select distinct o.customers_id from orders_products op, orders o where op.products_id = '4445' and op.orders_id = o.orders_id and o.orders_status = '2' // shipped status

 

[TEP STOP]

 

Please could you offer a little advice?

Link to comment
Share on other sites

Hi,

 

I've installed this but the cron wouldn't run. I then opened appu.php in a browser window and received the error

ok, so that file doesn't exist on our server...let's try it after removing line 3 and see if any other problems appear...

Please could you offer a little advice?

 

You need to remove the 2 instances of this line, and it will work:

 

// shipped status

Link to comment
Share on other sites

Not sure how to assemble a mind but a contribution it is.

 

http://www.oscommerce.com/community/contributions,3294

it keeps on amazing me what a little italoenglish can get done on these forums.

Nice to see you are still here Amanda !

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

  • 7 months later...
Not sure how to assemble a mind but a contribution it is.

 

http://www.oscommerce.com/community/contributions,3294

 

Hello Amanda,

 

My cron job result is this:

<b>Warning</b>: main(includes/configuration_cache_read.php): failed to open stream: No such file or directory in <b>/home/pcgroom/public_html/mysite/appu.php</b> on line <b>3</b><br />

 

configuration_cache_read is missing. Where could I find it? Thank you again.

Link to comment
Share on other sites

Hello Amanda,

 

My cron job result is this:

<b>Warning</b>: main(includes/configuration_cache_read.php): failed to open stream: No such file or directory in <b>/home/pcgroom/public_html/mysite/appu.php</b> on line <b>3</b><br />

 

configuration_cache_read is missing. Where could I find it? Thank you again.

 

ah, you have not installed the configuration cache contribution, highly recommended.

 

but for now, change this code :

 

require ('includes/configuration_cache_read.php');

 

 

into this code:

 

// set the application parameters

$configuration_query = tep_db_query('select configuration_key as cfgKey, configuration_value as cfgValue from ' . TABLE_CONFIGURATION);

while ($configuration = tep_db_fetch_array($configuration_query)) {

define($configuration['cfgKey'], $configuration['cfgValue']);

}

Treasurer MFC

Link to comment
Share on other sites

ah, you have not installed the configuration cache contribution, highly recommended.

 

but for now, change this code :

 

require ('includes/configuration_cache_read.php');

into this code:

 

// set the application parameters

$configuration_query = tep_db_query('select configuration_key as cfgKey, configuration_value as cfgValue from ' . TABLE_CONFIGURATION);

while ($configuration = tep_db_fetch_array($configuration_query)) {

define($configuration['cfgKey'], $configuration['cfgValue']);

}

 

and place it after:

 

tep_db_connect() or die('We are currently unavailable due to Maintenance..');

Treasurer MFC

Link to comment
Share on other sites

  • 6 months later...
ah, you have not installed the configuration cache contribution, highly recommended.

Amanda,

What is the link for this contribution? I tried searching for "configuration cache", "cache" but the search results don't look to be that contribution.

 

If you know the link to this contribution could you please post it?

 

Regards,

Marizka

Link to comment
Share on other sites

Amanda,

What is the link for this contribution? I tried searching for "configuration cache", "cache" but the search results don't look to be that contribution.

 

If you know the link to this contribution could you please post it?

 

Regards,

Marizka

 

this one :

 

http://www.oscommerce.com/community/contributions,1862

Treasurer MFC

Link to comment
Share on other sites

  • 2 months later...

I have installed this great contribution that speeds up my site ALOT. I have a problem thou... After I have installed ap_preselection everything looks just fine, but when I click on a productimage in the also_purchased module I get redirected to the startpage (index.php) and in the addressfield it says:

 

http://www.myhomepage.com/index.php?<osCsid>

 

I have really looked thru it and as far as I can see the problem lies in also_purchased_products.php.

 

Can someone please post their also_purchased_products.php module here if you got this contribution up and running?

 

I would be more than greatful for help. Im kinda desparate since the also_purchased module really slows down my site...

 

Best regards / Zuncan

So what?! Who care in a hundred years anyway?

Link to comment
Share on other sites

  • 2 years later...

Absolutely brilliant idea! This new table really helps on products with alot of sales. We had some products taking 7 seconds or more to do this query every page load before the contribution, and now the query takes less than .005 seconds!

 

One thing I should point out which is not included in the contribution, is you really need to add these indexes Chemo suggested earlier in the thread so your cron query does not take too long and timeout:

 

TABLE orders

1. index => date_purchased

 

TABLE orders_products

1. index => orders_id (single column)

2. index => products_id (single column)

3. index => orders_id (single column)

 

TABLE products

1. index => products_status

Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...