Psytanium Posted March 30, 2017 Share Posted March 30, 2017 Hi, When I add GROUP BY to a query, all the products (45 products) appear, but why it's saying Displaying 1 (of 1 products) ? if the GROUP BY is removed then the product count right. This is the query: $promotions_query_raw = "SELECT p.products_id, pd.products_name, p.products_price, p.products_model, p.products_availability_status, p.products_tax_class_id, p.products_image, p.fr_currencies_id, c.parent_id, p2c.products_id, p2c.categories_id, c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_promotion != '0' and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id != 31 and c.parent_id != 39 and pd.language_id = '" . (int)$languages_id . "' GROUP BY p2c.products_id ORDER BY p.products_price ASC"; Link to comment Share on other sites More sharing options...
♥kymation Posted March 30, 2017 Share Posted March 30, 2017 The splitPageResults class expects to see a SQL string in lower case only, and will ignore all upper case characters. Fix your case and your results should be correct. Regards Jim See my profile for a list of my addons and ways to get support. Link to comment Share on other sites More sharing options...
Psytanium Posted March 30, 2017 Author Share Posted March 30, 2017 The splitPageResults class expects to see a SQL string in lower case only, and will ignore all upper case characters. Fix your case and your results should be correct. Regards Jim when its in lower case I get this error 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 '*) as total from products p, products_to_categories p2c, products_description p' at line 1 select count(distinct *) as total from products p, products_to_categories p2c, products_description pd, categories c where p.products_status = '1' and p.products_promotion != '0' and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id != 31 and c.parent_id != 39 and pd.language_id = '1' Link to comment Share on other sites More sharing options...
Dan Cole Posted March 30, 2017 Share Posted March 30, 2017 The splitPageResults class expects to see a SQL string in lower case only, and will ignore all upper case characters. Fix your case and your results should be correct. Regards Jim That has caused me to pull my hair out a couple of times. (w00t) Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
Psytanium Posted March 30, 2017 Author Share Posted March 30, 2017 @@kymation I couldn't change it to lowercase, it produce some syntax error, so I ended changing classes/split_page_results.php and it worked. Thanks u saved few remaining hairs on my head :D If you know what to do concerning the upper / lowercase let me know Thanks Link to comment Share on other sites More sharing options...
Dan Cole Posted March 30, 2017 Share Posted March 30, 2017 when its in lower case I get this error 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 '*) as total from products p, products_to_categories p2c, products_description p' at line 1 select count(distinct *) as total from products p, products_to_categories p2c, products_description pd, categories c where p.products_status = '1' and p.products_promotion != '0' and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id != 31 and c.parent_id != 39 and pd.language_id = '1' @@Psytanium Did you change the first SELECT to lower case?...from the error message it looks like it is skipping that select completely. Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
Psytanium Posted March 30, 2017 Author Share Posted March 30, 2017 @@Dan Cole I tried SELECT in lower and uppercase, both produce errors if group by is lowercase. could it be a server configuration issue ? Link to comment Share on other sites More sharing options...
Dan Cole Posted March 30, 2017 Share Posted March 30, 2017 Did you try running the query directly within your phpadmin panel or whatever you're using for an database editor? You also try commenting out the call to split_page_results and see it that is still the cause of the issue. Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
bonbec Posted March 30, 2017 Share Posted March 30, 2017 Take a look to this page about count(distinct *) : http://stackoverflow.com/questions/5010470/why-doesnt-countdistinct-work with OsC 2.2 since 2006 ... Link to comment Share on other sites More sharing options...
burt Posted March 30, 2017 Share Posted March 30, 2017 Google; SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() which might work for you. If not then you need to make another query to get the correct number of results; select distinct products_id from products_to_categories and direct that result through the splitPageResult call Link to comment Share on other sites More sharing options...
Psytanium Posted March 31, 2017 Author Share Posted March 31, 2017 Originally I'm trying to display all the products in a parent directory. DISTINCT is not working, I still have duplicate products, those with duplicate shortcuts. GROUP BY displayed the desired products (duplicates removed), but with problems in the count and page split. Any ideas ? Link to comment Share on other sites More sharing options...
Psytanium Posted March 31, 2017 Author Share Posted March 31, 2017 Did you try running the query directly within your phpadmin panel or whatever you're using for an database editor? You also try commenting out the call to split_page_results and see it that is still the cause of the issue. Dan running the query directly from phpmyadmin returned good results, using both lower and upper case. apparently I have to modify something in the class split_page_results. thx dan Link to comment Share on other sites More sharing options...
Dan Cole Posted March 31, 2017 Share Posted March 31, 2017 Interesting....why don't you post your final lower case query here (the one that works in phpmyadmin) and the full php code your using so we can have a look and/or play around with it. Dan Need help? See this thread and provide the information requested. Is your version of osC up to date? You'll find the latest osC community version (CE Phoenix) here. Link to comment Share on other sites More sharing options...
tgely Posted March 31, 2017 Share Posted March 31, 2017 ???? SELECT, GROUP BY, ORDER BY and ASC to select, group by, order by and asc leave table constants in UPPERCASE :) $promotions_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_model, p.products_availability_status, p.products_tax_class_id, p.products_image, p.fr_currencies_id, c.parent_id, p2c.products_id, p2c.categories_id, c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_promotion != '0' and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id != 31 and c.parent_id != 39 and pd.language_id = '" . (int)$languages_id . "' group by p2c.products_id order by p.products_price asc"; osCommerce based shop owner with minimal design and focused on background works. When the less is more.Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store. Link to comment Share on other sites More sharing options...
Psytanium Posted March 31, 2017 Author Share Posted March 31, 2017 @@Gergely I set everything to lower cases, except for tables. I get this sql error : 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 '*) as total from products p, products_to_categories p2c, products_description p' at line 1 select count(distinct *) as total from products p, products_to_categories p2c, products_description pd, categories c where p.products_status = '1' and p.products_promotion != '0' and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id != 31 and c.parent_id != 39 and pd.language_id = '1' This error is fixed by capitalizing only GROUP BY. This is the php page : <?php /* $Id$ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2010 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_OFFER_FIREPLACES); $current_page = FILENAME_OFFER_FIREPLACES; $breadcrumb->add(NAVBAR_TITLE, tep_href_link(FILENAME_OFFER_FIREPLACES)); require(DIR_WS_INCLUDES . 'template_top.php'); ?> <?php echo tep_draw_content_top();?> <?php $clearance_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_model, p.products_availability_status, p.products_tax_class_id, p.products_image, p.products_promotion, p.fr_currencies_id, c.parent_id, p2c.products_id, p2c.categories_id, c.categories_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_promotion != '0' and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id != 31 and c.parent_id != 39 and pd.language_id = '" . (int)$languages_id . "' GROUP BY p.products_id order by p.products_price asc"; $clearance_split = new splitPageResults($clearance_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS); if (($clearance_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) { ?> <?php echo tep_draw_result1_top(); ?> <div class="cl_both result_top_padd ofh"> <div class="fl_left result_left"><?php echo $clearance_split->display_count(TEXT_DISPLAY_NUMBER_OF_WAREHOUSE_CLEARANCE); ?></div> <div class="fl_right result_right"><?php echo $clearance_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></div> </div> <?php echo tep_draw_result1_bottom(); ?> <?php } ?> <div class="contentContainer page_specials prod-list-wide"> <div class="contentPadd t_view prod-list-wide"> <?php $col_special_wide = 0; $row_special_wide = 0; $col_items = (MAX_DISPLAY_SPECIAL_PER_ROW_WIDE -1); $colspan = ((MAX_DISPLAY_SPECIAL_PER_ROW_WIDE * 2) - 1); $col_width = (int)(100 / ($col_items + 1)).'%'; ?> <?php $clearance_prods_content_wide .= '<div class="prods_content">'; $clearance_query = tep_db_query($clearance_split->sql_query); while ($clearance = tep_db_fetch_array($clearance_query)) { if (($col_special_wide === 0) && ($row_special_wide != 0)) { $clearance_prods_content_wide .= '<ul class="row_separator"><li class="prods_hseparator">'.tep_draw_separator('spacer.gif', '1', '1').'</li></ul>'; } if ($col_special_wide === 0) { $clearance_prods_content_wide .= '<ul class="row row3" id="row-'.$row_special_wide.'">'; }else { $clearance_prods_content_wide .= '<li class="prods_vseparator equal-height3">'.tep_draw_separator('spacer.gif', '1', '1').''; } // ************************************* if (IMG_HOVER_EFFECT == 'style-1') { $img_effect = 'first'; $img_effect_width = PRODS_BLOCK_SPECIALS_WIDTH - PIC_MARG_W; } if (IMG_HOVER_EFFECT == 'style-2') { $img_effect = 'tenth'; $img_effect_width = PRODS_BLOCK_SPECIALS_WIDTH - PIC_MARG_W; } if (IMG_HOVER_EFFECT == 'style-3') { $img_effect = 'fifth'; $img_effect_width = PRODS_BLOCK_SPECIALS_WIDTH - PIC_MARG_W; } // START - check if product is promoted // END - check if product is promoted // ************************************* // ************************************* $product_query = tep_db_query("select products_description, products_id from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . (int)$clearance['products_id'] . "' and language_id = '" . (int)$languages_id . "'"); $product = tep_db_fetch_array($product_query); $p_id = $product['products_id']; $row_special_wide++; // ************************************* // ************************************* $p_pic = '<a class="prods_pic_bg" href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $clearance['products_id']) . '" style="width:'.(SPECIAL_IMAGE_WIDTH + PIC_MARG_W).'px;height:'.(SPECIAL_IMAGE_HEIGHT + PIC_MARG_H).'px;"><div class="cat-pic-zoom"></div>' . tep_image(DIR_WS_IMAGES . $clearance['products_image'], $clearance['products_name'], (SPECIAL_IMAGE_WIDTH), (SPECIAL_IMAGE_HEIGHT), ' style="width:'.(SPECIAL_IMAGE_WIDTH + PIC_MARG_W2).'px;height:'.(SPECIAL_IMAGE_HEIGHT + PIC_MARG_H2).'px;margin:'.PIC_MARG_T.'px '.PIC_MARG_R.'px '.PIC_MARG_B.'px '.PIC_MARG_L.'px;"') . ''; if (tep_not_null($clearance['products_model'])) { $p_name = $lc_text = '<span><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '">' . $clearance['products_name'] . '<span style="color:#999; font-size: 11px;"> [' . $clearance['products_model'] . ']</a></span>'; } else { $p_name = $lc_text = '<span><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '">' . $clearance['products_name'] . '</a></span>'; } $p_price = $lc_text = '<div class="price price_padd '.$extra.'"><span class="productSpecialPrice">' . $currencies->display_price($clearance['products_price'], tep_get_tax_rate($clearance['products_tax_class_id']), $clearance['fr_currencies_id']) . '</span></div>'; if($cPath != 102){ if($clearance['products_availability_status'] ==0) { $p_availability = '<div class="status-availability"><i style="color:#94ff54" class="fa fa-check"></i> Available</div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '"><div class="have-look">More Details <i class="fa fa-angle-double-right more-details-icon"></i></div></a><div style="clear:both;"></div>'; }elseif($clearance['products_availability_status'] ==1) { $p_availability = '<div class="status-availability"><i style="color:#54d1ff" class="fa fa-pencil"></i> On order</div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '"><div class="have-look">More Details <i class="fa fa-angle-double-right more-details-icon"></i></div></a><div style="clear:both;"></div>'; }elseif($clearance['products_availability_status'] ==2){ $p_availability = '<div class="status-availability"><i style="color:#ffc154" class="fa fa-exclamation"></i> Last piece</div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '"><div class="have-look">More Details <i class="fa fa-angle-double-right more-details-icon"></i></div></a><div style="clear:both;"></div>'; }elseif($clearance['products_availability_status'] ==3){ $p_availability = '<div class="status-availability"><i style="color:#fff554" class="fa fa-hourglass-half"></i> Coming soon</div><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '"><div class="have-look">More Details <i class="fa fa-angle-double-right more-details-icon"></i></div></a><div style="clear:both;"></div>'; }else{ $p_availability = ''; } } // if Discounted if ($clearance['products_promotion'] == 1) { $clearance_offer ='<div class="offer-ribbon"></div>'; } else if ($clearance['products_promotion'] == 2) { $clearance_offer ='<div class="warehouse-clearance-ribbon"></div>'; } $p_details_text = '' .tep_draw_button2_top() . '<a href="' . tep_href_link('product_info.php?products_id='.$p_id) . '" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-icon-primary ui-priority-secondary" role="button"><span class="ui-button-icon-primary ui-icon ui-icon-triangle-1-e"></span><span class="ui-button-text">'. IMAGE_BUTTON_DETAILS .'</span></a>' . tep_draw_button2_bottom().''; $p_buy_now_text = '' .tep_draw_button_top() . '<a href="'.tep_href_link("products_new.php","action=buy_now&products_id=".$p_id).'" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-icon-primary ui-priority-secondary" role="button"><span class="ui-button-icon-primary ui-icon ui-icon-cart"></span><span class="ui-button-text">'. IMAGE_BUTTON_IN_CART .'</span></a>' . tep_draw_button_bottom().''; // ************************************* // ************************************* $clearance_prods_content_wide .= '<li style="width:' . PRODS_BLOCK_SPECIALS_WIDTH . 'px;" class="wrapper_prods equal-height_listing_block hover">'. "\n". '' .$clearance_offer.'<div class="border_prods">'. "\n". '<div class="pic_padd wrapper_pic_div" style="width:'.(SEARCH_IMAGE_WIDTH + PIC_MARG_W).'px;height:'.(SEARCH_IMAGE_HEIGHT + PIC_MARG_H).'px;">'.$p_pic.'</a>'. "\n". '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $clearance['products_id']) . '"></a></div>'. "\n". '<div class="prods_padd">'. '<div class="name name_padd equal-height">'.$p_name.'</div>'. "\n". ''.$p_price. "\n". ''.$p_availability. "\n". '<div class="listing_padd">'.$p_listing.'</div>'. "\n". '</div>'. "\n". '</div>'. "\n"; $col_special_wide ++; if ($col_special_wide > $col_items) { $clearance_prods_content_wide .= '</ul>'; $row_special_wide ++; $col_special_wide = 0; }else{ $clearance_prods_content_wide .= '</li>'; } } $clearance_prods_content_wide .= '</div>'; echo $clearance_prods_content_wide; ?> </div> </div> <?php if (($clearance_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3'))) { ?> <?php echo tep_draw_result2_top(); ?> <div class="cl_both result_bottom_padd ofh"> <div class="fl_left result_left"><?php echo $clearance_split->display_count(TEXT_DISPLAY_NUMBER_OF_SPECIALS); ?></div> <div class="fl_right result_right"><?php echo $clearance_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></div> </div> <?php echo tep_draw_result2_bottom(); ?> <?php } ?> <?php echo tep_draw_content_bottom();?> <script type="text/javascript"> $(window).load(function() { var maxHeight = 0; $(".equal-height").each(function() { if ($(this).height() > maxHeight) { maxHeight = $(this).height(); } }); $(".equal-height").height(maxHeight); }); </script> <?php require(DIR_WS_INCLUDES . 'template_bottom.php'); require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> UUsing Upper case GROUP BY, output the correct result but with wrong count and split, see attached image. Big thanks Link to comment Share on other sites More sharing options...
tgely Posted March 31, 2017 Share Posted March 31, 2017 @@Psytanium The problem is that missing group by p2c.products_id from the query. Something is wrong around splitPageResults class constructor.Have you modified splitPageResults class? osCommerce based shop owner with minimal design and focused on background works. When the less is more.Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store. Link to comment Share on other sites More sharing options...
Psytanium Posted March 31, 2017 Author Share Posted March 31, 2017 @@Psytanium The problem is that missing group by p2c.products_id from the query. Something is wrong around splitPageResults class constructor. Have you modified splitPageResults class? if I don't modify splitPageResults class, the products count is 1 if I capitalizing GROUP BY in splitPageResults class, the products count is 45, while it must be 36. whether I change it or not, its output is wrong. Link to comment Share on other sites More sharing options...
tgely Posted March 31, 2017 Share Posted March 31, 2017 splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page') so $clearance_split = new splitPageResults($clearance_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS, 'p.products_id'); osCommerce based shop owner with minimal design and focused on background works. When the less is more.Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store. Link to comment Share on other sites More sharing options...
Psytanium Posted March 31, 2017 Author Share Posted March 31, 2017 Exactly.. Thank you bro Link to comment Share on other sites More sharing options...
Denkster Posted April 22, 2017 Share Posted April 22, 2017 Hi Do you use MySQL 5.7? It is more strict on queries! Check this out:https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html : Implementation for the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group. Also see http://www.oscommerce.com/forums/topic/410833-osc-234bs-edge-sql-code-and-install-script-issues/#entry1754242 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.