Полезные для базы магазина SQL команды - страница 6 - [VM1] Управление товарами, атрибутами, типами, ценами

$db = &JFactory::getDBO();
$sku = JRequest::getVar('product_sku');
$pID= JRequest::getVar('pID');
$ordering	= 'product_id';
$query = 'SELECT p.product_id, p.product_name, p.product_sku, p.product_desc, p.product_in_stock, mn.mf_name'
	  . ' FROM #__vm_product AS p'
	  . ' INNER JOIN #__vm_product_mf_xref AS mx ON mx.product_id = p.product_id'
	  . ' INNER JOIN #__vm_manufacturer AS mn ON mn.manufacturer_id = mx.manufacturer_id'
	  . ' WHERE p.product_id = '.$pID
	  . ' ORDER BY p.'.$ordering
				;
$db->setQuery( $query );
$pInfo = $db->loadObjectList();
return $pInfo;

. ' WHERE p.product_sku = '.$sku

function getProductInfo()
{
$db = &JFactory::getDBO();
$sku = JRequest::getVar('sku');
$query = "SELECT p.product_id, p.product_name, p.product_sku, p.product_desc, p.product_in_stock, mn.mf_name 
              FROM jos_vm_product AS p 
	      INNER JOIN jos_vm_product_mf_xref AS mx ON mx.product_id = p.product_id 
	      INNER JOIN jos_vm_manufacturer AS mn ON mn.manufacturer_id = mx.manufacturer_id 
	      WHERE p.product_sku LIKE '%$sku%'				  
	      ORDER BY p.product_id";
$db->setQuery( $query );
$pInfo = $db->loadObjectList();
return $pInfo;
}

'UPDATE `#__vm_orders` SET `order_status`="S" WHERE `order_status`="P" OR `order_status`="C"'

'UPDATE `jos_vm_orders` SET `order_status`="S" WHERE `order_status`="P" OR `order_status`="C"'

UPDATE `jos_vm_orders` SET `order_status` = "S" WHERE `order_status` = "P" OR `order_status` = "C" 

UPDATE `jos_vm_orders` SET `order_status` = "S" WHERE `order_status` = "P" OR `order_status` = "C" 

product_price+100 

UPDATE jos_vm_product_price 
  INNER JOIN jos_vm_product_category_xref
    ON jos_vm_product_category_xref.category_id = XXX AND jos_vm_product_price.product_id = jos_vm_product_category_xref.product_id
SET jos_vm_product_price.product_price = jos_vm_product_price.product_price+100;

update `jos_vm_product_price` 
set `product_price` = 60
where `product_id` in (SELECT `product_id` FROM `jos_vm_product_price` WHERE product_price <60)

update `jos_vm_product_price` 
set `product_price` = 60
where `product_price` = 50

update `jos_vm_product_price` 
set `product_price` = 33
where `product_id` in (SELECT `product_id` FROM `jos_vm_manufacturer` WHERE manufacturer_id = 1 AND `product_price` = 10)

update `jos_vm_product_price` 
set `product_price` = 45
where `product_id` in (SELECT `product_id` FROM `jos_vm_product_mf_xref` WHERE `manufacturer_id` = 40 AND `product_price` < 45)