custom dropdown sidebox like music genres

[Badgermaster]

I have been working on figuring out a way to add a filter to my client's site so that customers can search by one of the fields in the custom product that I made for them. I finally realized that I could make a filter sidebox like the music genre or manufacturers, but I have hit a snag.

the box doesn't list the "drive types", but still allows for one to select types anyway, then comes up with this error:

1054 Unknown column 'pme.drive_type_id' in 'where clause'
in:
[select distinct c.categories_id as id, cd.categories_name as name from zen_products p, zen_products_to_categories p2c, zen_categories c, zen_categories_description cd, zen_product_drive_extra pme where p.products_status = 1 and pme.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '1' and pme.drive_type_id = '6' order by cd.categories_name]

I keep on looking up this error and finding that it generally has to do with people running an old version of zen cart on mysql5, but this isn't the problem. I am running zen cart 1.3.7 on my sql 4.1.

does anyone see the problem? is it just a typo? tell me where, please.

I will post all of my code in the next post.

[Badgermaster]

tpl_drive_type_select.php

<?php
/**
* Side Box Template
*
* @package templateSystem
* @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: tpl_drive_type_select.php 4773 2006-10-17 06:10:58Z drbyte $
*/
$content = "";
$content .= '<div id="' . str_replace('_', '-', $box_id . 'Content') . '" class="sideBoxContent centeredContent">';
$content .= zen_draw_form('drive_types_form', zen_href_link(FILENAME_DEFAULT, '', 'NONSSL', false), 'get');
$content .= zen_draw_hidden_field('main_page', FILENAME_DEFAULT) . zen_hide_session_id() . zen_draw_hidden_field('typefilter', 'drive_type');
$content .= zen_draw_pull_down_menu('drive_type_id', $drive_types_array, (isset($_GET['drive_type_id']) ? $_GET['drive_type_id'] : ''), 'onchange="this.form.submit();" size="' . MAX_DRIVE_TYPES_LIST . '" style="width: 90%; margin: auto;"');
$content .= '</form>';
$content .= '</div>';
?>

[Badgermaster]

drive_type.php

<?php
/**
* drive_types sidebox - displays list of available music genres to filter on
*
* @package templateSystem
* @copyright Copyright 2003-2005 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: drive_types.php 2834 2006-01-11 22:16:37Z birdbrain $
*/

$drive_types_query = "select drive_type_id, drive_type_name
from " . DRIVE_TYPE . "
order by drive_type_name";

$drive_types = $db->Execute($drive_types_query);

if ($drive_types->RecordCount()>0) {
$number_of_rows = $drive_types->RecordCount()+1;

// Display a list
$drive_types_array = array();
if (!isset($_GET['drive_type_id']) || $_GET['drive_type_id'] == '' ) {
$drive_types_array[] = array('id' => '', 'text' => PULL_DOWN_ALL);
} else {
$drive_types_array[] = array('id' => '', 'text' => PULL_DOWN_DRIVE_TYPES);
}

while (!$drive_types->EOF) {
$drive_type_name = ((strlen($drive_types->fields['drive_type_name']) > MAX_DISPLAY_DRIVE_TYPES_NAME_LEN) ? substr($drive_types->fields['drive_type_name'], 0, MAX_DISPLAY_DRIVE_TYPES_NAME_LEN) . '..' : $drive_types->fields['drive_type_name']);
$drive_types_array[] = array('id' => $drive_types->fields['drive_type_id'],
'text' => $drive_type_name);

$drive_types->MoveNext();
}
require($template->get_template_dir('tpl_drive_type_select.php',DIR_WS_TEMPLATE, $current_page_base,'sideboxes'). '/tpl_drive_type_select.php');

$title = '<label>' . BOX_HEADING_DRIVE_TYPES . '</label>';
$title_link = false;
require($template->get_template_dir($column_box_default, DIR_WS_TEMPLATE, $current_page_base,'common') . '/' . $column_box_default);
}
?>

[Badgermaster]

drive_type_filter.php

<?php
/**
* drive_type_filter.php for index filters
*
* index filter for the DRIVE product type
* show the products of a specified drive_type
*
* @package productTypes
* @copyright Copyright 2003-2006 Zen Cart Development Team
* @copyright Portions Copyright 2003 osCommerce
* @todo Need to add/fine-tune ability to override or insert entry-points on a per-product-type basis
* @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
* @version $Id: drive_type_filter.php 4254 2006-08-25 00:30:56Z ajeh $
*/
if (!defined('IS_ADMIN_FLAG')) {
die('Illegal Access');
}
if (!isset($select_column_list)) $select_column_list = "";

// show the products of a specified drive_type
if (isset($_GET['drive_type_id']))
{
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
{
// We are asked to show only a specific category
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.products_price, p.products_tax_class_id, pd.products_description, if(s.status = 1, s.specials_new_products_price, NULL) AS specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from " . TABLE_PRODUCTS . " p, " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " .
PRODUCT_DRIVE_EXTRA . " pme left join " . TABLE_SPECIALS . " s on pme.products_id = s.products_id, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
DRIVE_TYPE . " m
where m.drive_type_id = '" . (int)$_GET['drive_type_id'] . "'
and p.products_id = pme.products_id
and p.products_status = 1
and pme.drive_type_id = '" . (int)$_GET['drive_type_id'] . "'
and pme.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$_GET['filter_id'] . "'";
} else {
// We show them all
$listing_sql = "select " . $select_column_list . " pme.products_id, p.products_type, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from " . TABLE_PRODUCTS . " p, " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " .
PRODUCT_DRIVE_EXTRA . " pme left join " . TABLE_SPECIALS . " s on pme.products_id = s.products_id, " .
DRIVE_TYPE . " m
where m.drive_type_id = '" . (int)$_GET['drive_type_id'] . "'
and p.products_id = pme.products_id
and p.products_status = 1
and pd.products_id = pme.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and pme.drive_type_id = '" . (int)$_GET['drive_type_id'] . "'";
}
} else {
// show the products in a given category
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
{
// We are asked to show only specific category
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, m.drive_type_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " .
DRIVE_TYPE . " m, " .
PRODUCTS_DRIVE_EXTRA . " pme, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c
where p.products_status = 1
and pme.drive_type_id = m.drive_type_id
and m.drive_type_id = '" . (int)$_GET['filter_id'] . "'
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'";
} else {
// We show them all
if ($current_categories_id) {
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, m.drive_type_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_PRODUCTS . " p left join " . DRIVE_TYPE . " m, " . PRODUCT_DRIVE_EXTRA . " pme on pme.drive_type_id = m.drive_type_id, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
where p.products_status = 1
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'";
} else {
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, m.drive_type_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status = 1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_PRODUCTS . " p left join " . DRIVE_TYPE . " m, " . PRODUCT_DRIVE_EXTRA . " pme on pme.drive_type_id = m.drive_type_id, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
where p.products_status = 1
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";
}
}
}
// set the default sort order setting from the Admin when not defined by customer
if (!isset($_GET['sort']) and PRODUCT_LISTING_DEFAULT_SORT_ORDER != '') {
$_GET['sort'] = PRODUCT_LISTING_DEFAULT_SORT_ORDER;
}
$listing_sql = str_replace('m.manufacturers_name', 'm.drive_type_name as manufacturers_name', $listing_sql);

if (isset($column_list)) {
if ( (!isset($_GET['sort'])) || (!ereg('[1-8][ad]', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) )
{
for ($i=0, $n=sizeof($column_list); $i<$n; $i++)
{
if ($column_list[$i] == 'PRODUCT_LIST_NAME')
{
$_GET['sort'] = $i+1 . 'a';
$listing_sql .= " order by p.products_sort_order, pd.products_name";
break;
}
}
// if set to nothing use products_sort_order and PRODUCTS_LIST_NAME is off
if (PRODUCT_LISTING_DEFAULT_SORT_ORDER == '') {
$_GET['sort'] = '20a';
}
} else {
$sort_col = substr($_GET['sort'], 0 , 1);
$sort_order = substr($_GET['sort'], 1);
$listing_sql .= ' order by ';
switch ($column_list[$sort_col-1])
{
case 'PRODUCT_LIST_MODEL':
$listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
break;
case 'PRODUCT_LIST_NAME':
$listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
break;
case 'PRODUCT_LIST_MANUFACTURER':
$listing_sql .= "m.drive_type_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
break;
case 'PRODUCT_LIST_QUANTITY':
$listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
break;
case 'PRODUCT_LIST_IMAGE':
$listing_sql .= "pd.products_name";
break;
case 'PRODUCT_LIST_WEIGHT':
$listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
break;
case 'PRODUCT_LIST_PRICE':
// $listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
$listing_sql .= "p.products_price_sorter " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
break;
}
}
}
// optional Product List Filter
if (PRODUCT_LIST_FILTER > 0)
{
if (isset($_GET['drive_type_id']))
{
$filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name
from " . TABLE_PRODUCTS . " p, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
TABLE_CATEGORIES . " c, " .
TABLE_CATEGORIES_DESCRIPTION . " cd, " .
PRODUCT_DRIVE_EXTRA . " pme
where p.products_status = 1
and pme.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and p2c.categories_id = cd.categories_id
and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and pme.drive_type_id = '" . (int)$_GET['drive_type_id'] . "'
order by cd.categories_name";
} else {
$filterlist_sql= "select distinct m.drive_type_id as id, m.drive_type_name as name
from " . TABLE_PRODUCTS . " p, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
PRODUCT_DRIVE_EXTRA . " pme, " .
DRIVE_TYPE . " m
where p.products_status = 1
and pme.drive_type_id = m.drive_type_id
and p.products_id = p2c.products_id
and p2c.categories_id = '" . (int)$current_category_id . "'
order by m.drive_type_name";
}
$getoption_set = false;
$filterlist = $db->Execute($filterlist_sql);
$do_filter_list = false;
if ($filterlist->RecordCount() > 1)
{
$do_filter_list = true;
if (isset($_GET['drive_type_id']))
{
//die('here');
$getoption_set = true;
$get_option_variable = 'drive_type_id';
$options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));
} else {
$options = array(array('id' => '', 'text' => TEXT_ALL_DRIVE_TYPE));
}
while (!$filterlist->EOF) {
$options[] = array('id' => $filterlist->fields['id'], 'text' => $filterlist->fields['name']);
$filterlist->MoveNext();
}
}
}

// Get the right image for the top-right
$image = DIR_WS_TEMPLATE_IMAGES . 'table_background_list.gif';
if ($current_category_id) {

$sql = "select categories_image from " . TABLE_CATEGORIES . "
where categories_id = '" . (int)$current_category_id . "'";

$image_name = $db->Execute($sql);
$image = $image_name->fields['categories_image'];
}
?>


AND...
the site is at http://drivesunlimited.sporkdelis.com/shop

[TheOracle]

In your zen_product_drive_extra table, make sure you have this field inside:

drive_type_id. The error reports that it can't either be found or perhaps being empty.

[Badgermaster]

This gets rid of the error, but nothing is displayed in the dropbox, and nothing is filtered when a selection is made. (take a look at the page http://drivesunlimited.sporkdelis.com/shop)

Can anyone see any reason see why this isn't happening?

[TheOracle]

If you look in your phpmyadmin for the same field name, do you have content inside ? Looks like you re-added the field as it seem to be empty. If it's the case, it will obviously return no results since the server will have no idea on where to find your queries.

[Badgermaster]

yes, I just checked again, and there is info in that table. I used the admin to add data into the table (to make sure that it worked), plus I made the custom product before I needed any filters, so I didn't have that table, so when I added the filter and added drive_type_id I had to go through and re-enter the type in all 196 entries I already had, and I used the admin to make sure that the filter worked in that end.

It works everywhere but on the sidebox.

[TheOracle]

Very well, in your sideboxe module file (drive_type.php),

find:

$drive_types_query = "select drive_type_id, drive_type_name 

add above:

error_reporting(E_ALL); 

what are the returned results on top of your site ?

(Note: Please post these errors between [ quote ] and [ /quote ] (use brackets without spaces) - it's more readable on the forum. ).

[Badgermaster]

thanks for the tip. Here are the errors, I may be able to fix some of them myself. I switched from editing the music genre files to the manufacturer sidebox files which made it work better (with the exception of it not pulling the files, which I think I know why now) I will post the new code when I am done with dinner.

Thanks again

Notice: Use of undefined constant PRODUCTS_DRIVE_TYPE_STATUS - assumed 'PRODUCTS_DRIVE_TYPE_STATUS' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 27

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Use of undefined constant MAX_DISPLAY_DRIVE_TYPE_NAME_LEN - assumed 'MAX_DISPLAY_DRIVE_TYPE_NAME_LEN' in /home/sporkdel/public_html/drivesunlimited/shop/includes/modules/sideboxes/drive_type.php on line 54

Notice: Undefined variable: manufacturer_sidebox_array in /home/sporkdel/public_html/drivesunlimited/shop/includes/templates/template_default/sideboxes/tpl_drive_type_select.php on line 17

Notice: Use of undefined constant MAX_DRIVE_TYPE_LIST - assumed 'MAX_DRIVE_TYPE_LIST' in /home/sporkdel/public_html/drivesunlimited/shop/includes/templates/template_default/sideboxes/tpl_drive_type_select.php on line 17