Jump to content
Psytanium

MySQL query to sun quantity of products of same id

Recommended Posts

Hello,

I created a group table and page in backend, now I can create groups and insert products into those groups.

Can someone help me create a query to sum the quantity of products that have the same group_id ?

Groups table screenshot attached.

Here is the code where the query is

<?php
// create column list
    $define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
                         'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
                         'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
                         'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
                         'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
                         'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
                         'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,
                         'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW,
						 'PRODUCT_AVAILABILITY' => PRODUCT_AVAILABILITY);

    asort($define_list);

    $column_list = array();
    reset($define_list);
    while (list($key, $value) = each($define_list)) {
      if ($value > 0) $column_list[] = $key;
    }

    $select_column_list = '';

    for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
      switch ($column_list[$i]) {
        case 'PRODUCT_LIST_MODEL':
          $select_column_list .= 'p.products_model, p.products_size,';
          break;
        case 'PRODUCT_LIST_NAME':
          $select_column_list .= 'pd.products_name, p.products_model, ';
          break;
        case 'PRODUCT_LIST_MANUFACTURER':
          $select_column_list .= 'm.manufacturers_name, m.manufacturers_country_name, m.manufacturers_country_flag, ';
          break;
        case 'PRODUCT_LIST_QUANTITY':
          $select_column_list .= 'p.products_quantity, p.products_incoming_quantity, (p.products_quantity + p.products_incoming_quantity) AS total_quantity, ';
          break;
        case 'PRODUCT_LIST_IMAGE':
          $select_column_list .= 'p.products_image, ';
          break;
        case 'PRODUCT_LIST_WEIGHT':
          $select_column_list .= 'p.products_weight, ';
          break;
		case 'PRODUCT_AVAILABILITY':
          $select_column_list .= 'p.products_availability_status, ';
          break;
      }
    }

// show the products of a specified manufacturer
    if (isset($HTTP_GET_VARS['manufacturers_id']) && !empty($HTTP_GET_VARS['manufacturers_id'])) {
			
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
        $listing_sql = "select " . $select_column_list . " 
		p.products_id, 
		p.manufacturers_id, 
		p.fr_currencies_id, 
		p.products_ship_free, 
		p.products_price, 
		p.products_show_price, 
		p.products_availability_status, 
		p.products_tax_class_id, 
		IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
		IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
			TABLE_PRODUCTS . " p left join " . 
			TABLE_SPECIALS . " s on p.products_id = s.products_id, " . 
			TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
			TABLE_MANUFACTURERS . " m, " . 
			TABLE_PRODUCTS_TO_CATEGORIES . " p2c where " . $product_web_status . " and p.manufacturers_id = m.manufacturers_id " . $hide_brands . " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
      } else {
// We show them all
        $listing_sql = "select " . $select_column_list . " 
		p.products_id, 
		p.manufacturers_id,  
		p.fr_currencies_id, 
		p.products_ship_free, 
		p.products_price, 
		p.products_show_price, 
		p.products_availability_status, 
		p.products_tax_class_id, 
		IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
		IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
			TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
			TABLE_PRODUCTS . " p left join " . 
			TABLE_SPECIALS . " s on p.products_id = s.products_id, " . 
			TABLE_MANUFACTURERS . " m where  " . $product_web_status . " and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id " . $hide_brands . " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
      }
    } else {
// show the products in a given categorie
      if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
        $listing_sql = "select " . $select_column_list . " 
		p.products_id, 
		p.manufacturers_id, 
		p.fr_currencies_id, 
		p.products_ship_free, 
		p.products_price, 
		p.products_show_price, 
		p.products_availability_status, 
		p.products_tax_class_id, 
		IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
		IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
			TABLE_PRODUCTS . " p left join " . 
			TABLE_SPECIALS . " s on p.products_id = s.products_id, " . 
			TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
			TABLE_MANUFACTURERS . " m where p.manufacturers_id = m.manufacturers_id, " . 
			TABLE_PRODUCTS_TO_CATEGORIES . " p2c where  " . $product_web_status . " and p.manufacturers_id = m.manufacturers_id " . $hide_brands . " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$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.fr_currencies_id, 
		p.products_ship_free, 
		p.products_price, 
		p.products_show_price, 
		p.products_availability_status, 
		p.products_tax_class_id, 
		IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, 
		IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . 
			TABLE_PRODUCTS_DESCRIPTION . " pd, " . 
			TABLE_PRODUCTS . " p left join " . 
			TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . 
			TABLE_SPECIALS . " s on p.products_id = s.products_id, " . 
			TABLE_PRODUCTS_TO_CATEGORIES . " p2c where  " . $product_web_status . $hide_brands . " and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
      }
    }


	//start add 17.06.2018 DrTech76, sort by price, with currency conversion, fix
	$has_tax_sql=(defined("DISPLAY_PRICE_WITH_TAX") and DISPLAY_PRICE_WITH_TAX=="true");//in product_listing.php prices are always shown with tax currently, so the only determinant woulnd be the admin setting whether to take tax at the sorting level or not
	$has_fr_currency=(function_exists("tep_has_friendly_currency") and tep_has_friendly_currency()===true);

	if($has_tax_sql===true)
	{
		$parts=explode(" from ",$listing_sql);
		$fore=array_shift($parts);
		$fore.=", tmp_taxes.tax_rate";
		
		if($has_fr_currency===true)
		{
			$fore.=", tmp_fr_curr.fr_currency_multiplier";
		}
		
		foreach($parts as $ix=>$part)
		{
			if(($tpp_pos=strpos($part," ".TABLE_PRODUCTS." p "))!==false)
			{
				$tpp_pos+=strlen(" ".TABLE_PRODUCTS." p ");
				$fp=substr($part,0,$tpp_pos);
				$aft=substr($part,$tpp_pos);
				
				$price_with_tax_sql=" LEFT JOIN (SELECT tr.tax_class_id, SUM(tax_rate) AS tax_rate FROM ".TABLE_TAX_RATES." AS tr LEFT JOIN ".TABLE_ZONES_TO_GEO_ZONES." AS za ON (tr.tax_zone_id = za.geo_zone_id) LEFT JOIN ".TABLE_GEO_ZONES." AS tz ON (tz.geo_zone_id = tr.tax_zone_id) WHERE (za.zone_country_id IS NULL OR za.zone_country_id = '0' OR za.zone_country_id = '118') AND (za.zone_id IS NULL OR za.zone_id = '0' OR za.zone_id = '-1') GROUP BY tr.tax_class_id, tr.tax_priority) AS tmp_taxes ON (tmp_taxes.tax_class_id=p.products_tax_class_id)";
				
				if($has_fr_currency===true)
				{
					$price_with_tax_sql.=" LEFT JOIN (SELECT currencies_id,IF(currencies_id=0 OR value=0,1,(1/value)) AS fr_currency_multiplier FROM ".TABLE_CURRENCIES." ORDER BY currencies_id) AS tmp_fr_curr ON tmp_fr_curr.currencies_id=(IF(p.fr_currencies_id NOT IN (SELECT currencies_id FROM ".TABLE_CURRENCIES."),(SELECT currencies_id FROM ".TABLE_CURRENCIES." WHERE code='".DEFAULT_CURRENCY."' LIMIT 1),p.fr_currencies_id)) ";
				}
				
				$part=$fp.$price_with_tax_sql.$aft;
				$parts[$ix]=$part;
				break;
			}
		}
		$parts=array_merge(array($fore),$parts);
		$listing_sql=join(" from ",$parts);
		$has_tax_sql=true;
	}
	else
	{
		$has_tax_sql=false;
		if($has_fr_currency===true)
		{
			$parts=explode(" from ",$listing_sql);
			$fore=array_shift($parts);
			$fore.=", tmp_fr_curr.fr_currency_multiplier";
			
			foreach($parts as $ix=>$part)
			{
				if(($tpp_pos=strpos($part," ".TABLE_PRODUCTS." p "))!==false)
				{
					$tpp_pos+=strlen(" ".TABLE_PRODUCTS." p ");
					$fp=substr($part,0,$tpp_pos);
					$aft=substr($part,$tpp_pos);
					
					$price_with_curr_sql=" LEFT JOIN (SELECT currencies_id,IF(currencies_id=0 OR value=0,1,(1/value)) AS fr_currency_multiplier FROM ".TABLE_CURRENCIES." ORDER BY currencies_id) AS tmp_fr_curr ON tmp_fr_curr.currencies_id=(IF(p.fr_currencies_id NOT IN (SELECT currencies_id FROM ".TABLE_CURRENCIES."),(SELECT currencies_id FROM ".TABLE_CURRENCIES." WHERE code='".DEFAULT_CURRENCY."' LIMIT 1),p.fr_currencies_id)) ";
					
					$part=$fp.$price_with_curr_sql.$aft;
					$parts[$ix]=$part;
					break;
				}
			}
			$parts=array_merge(array($fore),$parts);
			$listing_sql=join(" from ",$parts);
		}
	}
	//end add 17.06.2018 DrTech76, sort by price, with currency conversion, fix


    if ( (!isset($HTTP_GET_VARS['sort'])) || (!preg_match('/^[1-8][ad]$/', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) 
	{
      for ($i=0, $n=sizeof($column_list); $i<$n; $i++) 
	  {
        if ($column_list[$i] == 'PRODUCT_LIST_PRICE') 
		{
          $HTTP_GET_VARS['sort'] = $i+1 . 'a';
		  
		  //start edit 17.06.2018 DrTech76, sort by price, with currency conversion, fix
          /*
          originally it was
          
		  $listing_sql .= " order by products_availability_status = 1, final_price = 0, p.products_show_price = 0, final_price ASC, pd.products_name ASC";
		  
		  */
		  
		  	if($has_tax_sql===true)
		  	{
				$listing_sql .= " order by products_availability_status = 1, total_quantity < 1,(final_price".(($has_fr_currency===true)?"*(IF(tmp_fr_curr.fr_currency_multiplier IS NULL,1,tmp_fr_curr.fr_currency_multiplier))":"")."*(1+(IF(p.products_tax_class_id NOT IN (SELECT tax_class_id FROM ".TABLE_TAX_CLASS."),0,(tax_rate/100))))) ".(($sort_order=='d')?'DESC':'ASC').", final_price = 0, p.products_show_price = 0, pd.products_name ASC";
			}
			else
			{
				$listing_sql .= " order by products_availability_status = 1, total_quantity < 1,(final_price".(($has_fr_currency===true)?"*(IF(tmp_fr_curr.fr_currency_multiplier IS NULL,1,tmp_fr_curr.fr_currency_multiplier))":"").") ".(($sort_order=='d')?'DESC':'ASC').", final_price = 0, p.products_show_price = 0, pd.products_name ASC";
			}
			
		//end edit 17.06.2018 DrTech76, sort by price, with currency conversion, fix
          break;
        }
      }
    } 
	else 
	{
      $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
      $sort_order = substr($HTTP_GET_VARS['sort'], 1);
      //start edit 17.06.2018 DrTech76, sort by price, with currency conversion, fix
      $sort_order=strtolower($sort_order);
      //end edit 17.06.2018 DrTech76, sort by price, with currency conversion, fix

      switch ($column_list[$sort_col-1]) {
        case 'PRODUCT_LIST_MODEL':
          $listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_NAME':
          $listing_sql .= " order by pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
          break;
        case 'PRODUCT_LIST_MANUFACTURER':
          $listing_sql .= " order by m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_QUANTITY':
          $listing_sql .= " order by total_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_IMAGE':
          $listing_sql .= " order by pd.products_name";
          break;
        case 'PRODUCT_LIST_WEIGHT':
          $listing_sql .= " order by p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
          break;
        case 'PRODUCT_LIST_PRICE':
          
          //start edit 17.06.2018 DrTech76, sort by price, with currency conversion, fix
          /*
          originally it was 
          
		  $listing_sql .= " order by products_availability_status = 1, final_price = 0, p.products_show_price = 0, final_price ASC, pd.products_name ASC";
		  
		  */
		  
		  	if($has_tax_sql===true)
		  	{
				$listing_sql .= " order by products_availability_status = 1, total_quantity < 1,(final_price".(($has_fr_currency===true)?"*(IF(tmp_fr_curr.fr_currency_multiplier IS NULL,1,tmp_fr_curr.fr_currency_multiplier))":"")."*(1+(IF(p.products_tax_class_id NOT IN (SELECT tax_class_id FROM ".TABLE_TAX_CLASS."),0,(tax_rate/100))))) ".(($sort_order=='d')?'DESC':'ASC').", final_price = 0, p.products_show_price = 0, pd.products_name ASC";
			}
			else
			{
				$listing_sql .= " order by products_availability_status = 1, total_quantity < 1,(final_price".(($has_fr_currency===true)?"*(IF(tmp_fr_curr.fr_currency_multiplier IS NULL,1,tmp_fr_curr.fr_currency_multiplier))":"").") ".(($sort_order=='d')?'DESC':'ASC').", final_price = 0, p.products_show_price = 0, pd.products_name ASC";
			}
		  
		  //end edit 17.06.2018 DrTech76, sort by price, with currency conversion, fix
          break;
      }
    }

    $catname = HEADING_TITLE;
    if (isset($HTTP_GET_VARS['manufacturers_id']) && !empty($HTTP_GET_VARS['manufacturers_id'])) {
      $image = tep_db_query("select manufacturers_image, manufacturers_name as catname from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");
      $image = tep_db_fetch_array($image);
      $catname = $image['catname'];
    } elseif ($current_category_id) {
      $image = tep_db_query("select c.categories_image, cd.categories_name as catname from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");
      $image = tep_db_fetch_array($image);
      $catname = $image['catname'];
    }
$image2 = '';
$extraset = '';
if	(TITLE_PIC == 'true'){
// Get the right image for the top-right
   
    if (isset($HTTP_GET_VARS['manufacturers_id']) && !empty($HTTP_GET_VARS['manufacturers_id'])) {
      $image2 = tep_db_query("select manufacturers_image from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");
      $image2 = tep_db_fetch_array($image2);
      $image2 = $image2['manufacturers_image'];
    } elseif ($current_category_id) {
      $image2 = tep_db_query("select categories_image from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");
      $image2 = tep_db_fetch_array($image2);
      $image2 = $image2['categories_image'];
    }
}
?>

<?php if ($image2 !=''){?><div class="title_pic"><?php echo tep_image(DIR_WS_IMAGES . $image2, $catname, '', HEADING_IMAGE_HEIGHT); ?></div><?php } ?>

<?php require (DIR_WS_MODULES . 'products_filter.php');?>


<?php
// optional Product List Filter
    if (PRODUCT_LIST_FILTER > 0) {
      if (isset($HTTP_GET_VARS['manufacturers_id']) && !empty($HTTP_GET_VARS['manufacturers_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 where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";
      } else {
        $filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";
      }
      $filterlist_query = tep_db_query($filterlist_sql);
      if (tep_db_num_rows($filterlist_query) > 1) {
        echo  tep_draw_form('filter', FILENAME_DEFAULT, 'get') . '<div class="show"><div>' . TEXT_SHOW . '&nbsp;';
        if (isset($HTTP_GET_VARS['manufacturers_id']) && !empty($HTTP_GET_VARS['manufacturers_id'])) {
          echo tep_draw_hidden_field('manufacturers_id', $HTTP_GET_VARS['manufacturers_id']);
          $options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));
        } else {
          echo tep_draw_hidden_field('cPath', $cPath);
          $options = array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS));
        }
        echo tep_draw_hidden_field('sort', $HTTP_GET_VARS['sort']);
        while ($filterlist = tep_db_fetch_array($filterlist_query)) {
          $options[] = array('id' => $filterlist['id'], 'text' => $filterlist['name']);
        }
        echo tep_draw_pull_down_menu('filter_id', $options, (isset($HTTP_GET_VARS['filter_id']) ? $HTTP_GET_VARS['filter_id'] : ''), 'onchange="this.form.submit()" class="select"');
        echo tep_hide_session_id() . '</div></div></form>' . "\n";
	//	echo '<div class="prods_hseparator">'.tep_draw_separator('spacer.gif', '1', '1').'</div>';
      }
	}

?>

<?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?>

 

group-table.jpg

Share this post


Link to post
Share on other sites

The reason I'm doing this, is to give products that belong to the same group the same quantity, which is the sum of their quantities.

e.g.

Product A qty = 5
Product B qty = 10

if both belong to the same group then

Product A qty = 15
Product B qty = 15

Share this post


Link to post
Share on other sites

You'll need to join the products table to the products_to_groups table, do a count on products_quantity and then group by group_id; then output the products_id and grouped count.  At this point, update your DB appropriately with the outputted numbers.


This is a signature that appears on all my posts.  
IF YOU MAKE A POST REQUESTING HELP...please state the exact version
of osCommerce that you are using. THANKS

 
Get the latest Responsive osCommerce CE (community edition) here

Share this post


Link to post
Share on other sites
On 1/9/2019 at 8:48 PM, burt said:

You'll need to join the products table to the products_to_groups table, do a count on products_quantity and then group by group_id; then output the products_id and grouped count.  At this point, update your DB appropriately with the outputted numbers.

This is what I'm thinking but I can't seem to implement it in my query.

If someone have few minutes and mysql knowledge I appreciate more details.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×