1054 Unknown column 'p.products_id' in 'on clause'

When upgrade mySQL 4.x to mySQL 5.x, it will create this error in zen-cart 1.2.x. The get around is change sql select statemenst as follows:

ZenCart/includes/modules/meta_tags.php, about line 180

from ((" . TABLE_PRODUCTS . " p), " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id = p.products_id and mtpd.language_id = '" . (int)$_SESSION['languages_id'] . "

ZenCart/includes/blocks/blk_advanced_search_result.php

$from_str = "from (((" . TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd), " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id= p.products_id and mtpd.language_id = '" . $_SESSION['languages_id'] . "'";

ZenCart/includes/index_filters/default_filter.php

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_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 from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_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)$_GET['filter_id'] . "'";
} else {
// We show them all
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_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 from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$_GET['manufacturers_id'] . "'";
}
} else {
// show the products in a given categorie
if (isset($_GET['filter_id']) && zen_not_null($_GET['filter_id']))
{
// We are asked to show only specific catgeory
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_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 from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_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
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_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 from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c) left join " . TABLE_SPECIALS . " s on p.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 . "'";

ZenCart/includes/modules/product_listing.php, line 1

if($m_brandname&&!$model_code)
{
$listing_sql = "select p.products_image, pd.products_name, p.products_id, p.manufacturers_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 from ((".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p) left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_BRAND_MODEL." bm) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = bm.product_id and pd.products_id = bm.product_id and pd.language_id = '1' and bm.brand_name = '".$m_brandname."' order by p.products_sort_order, pd.products_name";
}
else if($m_brandname&&$model_code)
{
$listing_sql = "select p.products_image, pd.products_name, p.products_id, p.manufacturers_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 from ((".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p) left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_BRAND_MODEL." bm, ".TABLE_MODEL." md) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = bm.product_id and pd.products_id = bm.product_id and pd.language_id = '1' and bm.brand_model_id = md.brand_model_id and md.model_code = '".$model_code."' order by p.products_sort_order, pd.products_name";
}
else if($p_brandname&&!$part_code)
{
$listing_sql = "select p.products_image, pd.products_name, p.products_id, p.manufacturers_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 from ((".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p) left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_BRAND_PART." bp) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = bp.product_id and pd.products_id = bp.product_id and pd.language_id = '1' and bp.brand_name = '".$p_brandname."' order by p.products_sort_order, pd.products_name";
}
else if($p_brandname&&$part_code)
{
$listing_sql = "select p.products_image, pd.products_name, p.products_id, p.manufacturers_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 from ((".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p) left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_BRAND_PART." bp, ".TABLE_PART." pt) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = bp.product_id and pd.products_id = bp.product_id and pd.language_id = '1' and bp.brand_part_id = pt.brand_part_id and pt.part_code = '".$part_code."' order by p.products_sort_order, pd.products_name";
}

Although this may not solve all issues, most are eliminated

DataGrid.ItemCommand doesn't fire

In one of my web application, ItemCommand event of a datagrid doesn't fire as expected. After some work, I find out the reason.

The bind to datasouce code of this datagrid control is put in Page_PreRender handler, not in Page_Load. On first load of the page, everything is fine, the rows are populated in datagrid and I add "cancel" button for each row.

Then I click "cancel" button, which trigger a postback. Again, Page_Load hander runs first. Then .NET framework try to call ItemCommand event, I reckon it checks the rows in datagrid and since their are no rows yet. It decides not to call ItemCommand. Then Page_PreRender is called. The command is ignored.

So, don't do important tasks in Page_PreRender

Use custom control in Page_Load

As known, custom control's onload() method is called after container's page_load event fires. So in Page_Load handler, custom control hasn't been fully initialized. Although we can do something on custom control in prerender handler, but sometimes we need custom control ready before anything else can do.

Here is an example:

If we have an authenticate custom control and we want everything else run after check the permission of user, we have to make sure custom control is ready. But since onload hasn't been invoked, the codes in it hasn't been executed yet.

The work around is put all code in onload to anothe method, then call it directly from container page's Page_Load handler.