Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

MySQL query to sun quantity of products of same id

Psytanium

By Psytanium
in General Discussions

Recommended Posts

Psytanium

Psytanium

Posted
Posted

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

Link to comment
Share on other sites
Psytanium

Psytanium

Posted
  • Author
Posted

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

Link to comment
Share on other sites
burt

burt

Posted
Posted

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.

Link to comment
Share on other sites
Psytanium

Psytanium

Posted
  • Author
Posted
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.

Link to comment
Share on other sites

Archived

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

Go to topic listing
×
×
  • Create New...