Supertex Posted January 22, 2014 Posted January 22, 2014 I've added a few colums to my admin (categories.php), and would like to add an additional column for specials price (retail price level as per SPPC) to either show "none" or the specials price. My original query (977-985) was : $products_count = 0; if (isset($HTTP_GET_VARS['search'])) { $products_query = tep_db_query("select p.products_model, p.products_weight, p.products_id, pd.products_name, p.products_quantity, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order, p2c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and pd.products_name like '%" . tep_db_input($search) . "%' order by p.products_sort_order, p.products_model, p.products_price, pd.products_name"); } else { $products_query = tep_db_query("select p.products_model, p.products_weight, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by p.products_sort_order, p.products_model, p.products_price, pd.products_name"); } while ($products = tep_db_fetch_array($products_query)) { $products_count++; $rows++; My listing (1014-1020) is: <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products['products_id'] . '&action=new_product_preview') . '">' . tep_image(DIR_WS_ICONS . 'preview.gif', ICON_PREVIEW) . '</a> ' . $products['products_name']; ?></td> <td class="dataTableContent"><?php echo $products['products_model']; ?></td> <td class="dataTableContent"><?php echo $products['products_quantity']; ?></td> <td class="dataTableContent"><?php echo $products['products_price']; ?></td> <td class="dataSaleContent"><?php echo $products['specials_new_products_price']; ?></td> <td class="dataTableContent"><?php echo $products['products_weight']; ?></td> <td class="dataTableContent" align="center"> I've made the appropriate changes to the language file, and the stylesheet, and I tried adding the the following changes to the query: To SELECT: s.products_id, s.specials_new_products_price, s.customers_group_id To FROM: " . TABLE_SPECIALS ." s To WHERE: p.products_id=s.products_id So my new query is : $products_count = 0; if (isset($HTTP_GET_VARS['search'])) { $products_query = tep_db_query("select p.products_model, p.products_weight, p.products_id, pd.products_name, p.products_quantity, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order, p2c.categories_id, s.products_id, s.specials_new_products_price, s.customers_group_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_SPECIALS ." s where p.products_id=s.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and pd.products_name like '%" . tep_db_input($search) . "%' order by p.products_sort_order, p.products_model, p.products_price, pd.products_name"); } else { $products_query = tep_db_query("select p.products_model, p.products_weight, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order, s.products_id, s.specials_new_products_price, s.customers_group_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_SPECIALS ." s where p.products_id=s.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by p.products_sort_order, p.products_model, p.products_price, pd.products_name"); } while ($products = tep_db_fetch_array($products_query)) { $products_count++; $rows++; That caused only results with active specials to display. If I remove p.products_id=s.products_id, from WHERE, then it gives me numbers in the specials_new_product_price column that make no sense at all. Something tells me I need to use another query for this. Someone feel inclined to issue some guidance? S osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
♥bruyndoncx Posted January 22, 2014 Posted January 22, 2014 you have to use an outer join construction in sql basically, you need to express the FROM clause differently products left join specials using (products_id) or alternatively spelling out the tables to join with their aliases products p left join specials s on (p.products_id = s.products_id) I adapted the second query using the first notation, if it works that is the simples, if mysql complains you might need to rewrite it to the second longer version $products_query = tep_db_query("select p.products_model, p.products_weight, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order, s.products_id, s.specials_new_products_price, s.customers_group_id from " . TABLE_PRODUCTS . " p join " . TABLE_PRODUCTS_DESCRIPTION . " pd using(products_id) join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_SPECIALS ." s using (products_id) where language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "' order by p.products_sort_order, p.products_model, p.products_price, pd.products_name"); 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
Supertex Posted January 22, 2014 Author Posted January 22, 2014 If no specials price exists for given p.product_id, will it break or just return 'null'? NVM...that did work, but now anything with no specials price is "selected" while viewing the file in admin, which may be a result of my doing this: <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products['products_id'] . '&action=new_product_preview') . '">' . tep_image(DIR_WS_ICONS . 'preview.gif', ICON_PREVIEW) . '</a> ' . $products['products_name']; ?></td> <td class="dataTableContent"><?php echo $products['products_model']; ?></td> <td class="dataTableContent"><?php echo $products['products_quantity']; ?></td> <td class="dataTableContent"><?php echo $products['products_price']; ?></td> <td class="dataSaleContent"><?php if ($products['specials_new_products_price']==null) { echo "none"; }else{ echo $products['specials_new_products_price']; } ?></td> <td class="dataTableContent"><?php echo $products['products_weight']; ?></td> <td class="dataTableContent" align="center"> osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
Supertex Posted January 22, 2014 Author Posted January 22, 2014 What have I done? This is after removing the "if" statement. Nothing changed except where no specials exists, it no longer says "none". If I try to change the line selection to something NOT selected (something that has a specials price) then it selects as expected. If I click on something that does not have a specials price, I get: Warning: reset() [function.reset]: Passed variable is not an array or object in /blah/catalog/control/includes/classes/object_info.php on line 17 Warning: Variable passed to each() is not an array or object in /blah/catalog/control/includes/classes/object_info.php on line 18 osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
Supertex Posted January 22, 2014 Author Posted January 22, 2014 I suppose it would help for me to provide the query, heh. $products_query = tep_db_query("select p.products_model, p.products_weight, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status, p.products_sort_order, s.products_id, s.specials_new_products_price, s.customers_group_id from " . TABLE_PRODUCTS . " p join " . TABLE_PRODUCTS_DESCRIPTION . " pd using(products_id) join " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c using(products_id) left join " . TABLE_SPECIALS ." s using(products_id) where language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by p.products_sort_order, p.products_model, p.products_price, pd.products_name"); osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
Supertex Posted January 24, 2014 Author Posted January 24, 2014 Removing s.products_id from the SELECT statement remedied this problem. osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
Recommended Posts
Archived
This topic is now archived and is closed to further replies.