Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

splitPageResults won't work when using GROUP BY


Psytanium

Recommended Posts

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

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

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

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

Link to comment
Share on other sites

@@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

 

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

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

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

Link to comment
Share on other sites

????
 

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";

:blink:
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

@@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

post-244988-0-00403800-1490980707_thumb.jpg

Link to comment
Share on other sites

@@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?

:blink:
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

@@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

splitPageResults($query, $max_rows, $count_key = '*', $page_holder = 'page')

so

$clearance_split = new splitPageResults($clearance_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS, 'p.products_id');

:blink:
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

  • 3 weeks later...

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

Archived

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

×
×
  • Create New...