magento1 数据库操作

摘要：直接操作数据库和常用的自带操作数据库。

1. 直接操作数据库：

$resource = Mage::getSingleton("core/resource");

    $read = $resource->getConnection('core_read');
    $table_sales_flat_quote = $resource->getTableName('sales_flat_quote');

    // 查询
    $sql = $read->select()->from(['main_table' => $table_sales_flat_quote])->where('main_table.entity_id=?', $lastQuoteId)->limit(1);
    $result1 = $read->fetchAll($sql);
    if (!$result1) {
        $this->_redirect('checkout/cart');
        return;
    }
    // var_dump($result1);
    $lastResaleTaxNumber = $result1[0]['resale_tax_number'];

    if ($lastResaleTaxNumber != null) {

        $write = $resource->getConnection('core_write');
        $table_sales_flat_order = $resource->getTableName('sales_flat_order');
        
        // 更新 update(表名, 更新的数据, 更新的条件)
        $result2 = $write->update($table_sales_flat_order, ['resale_tax_number' => $lastResaleTaxNumber], ['entity_id=?' => $lastOrderId]);
        if (!$result2) {
            $this->_redirect('checkout/cart');
            return;
        }

        // 插入
        $result3 = $write->insert($table_sales_flat_order, ['resale_tax_number' => '12345678901']);

        // 删除
        $result4 = $write->delete($table_sales_flat_order, ['resale_tax_number' => '12345678901']);
        
    }
           

2. 常用的自带操作数据库：

• 判断用户是否登录，获取登录用户信息

  $customerSession = Mage::getSingleton('customer/session');
      if ($customerSession->isLoggedIn()) {
          $customer = $customerSession->getCustomer();
  
          $customerId = $customer->getId();
          // $customerId = $customer->getData('entity_id');
          $customerGroupId = $customer->getGroupId();
          // $customerGroupId = $customer->getData('group_id');
  
          // ...
  
          // 通过 getData 查看所有信息
          $customerData = $customer->getData();
          var_dump($customerData);
  
          // 设置新的用户信息
          $customer->setData('group_id', 2);
          $customer->save();
          
      }
             

• 获取产品信息

  // 通过 id 获取产品
      $_product = Mage::getModel("catalog/product")->load($_productId);
      // 通过 sku 获取产品
      $_product = Mage::getModel("catalog/product")->loadByAttribute("sku", $_sku);
  
      // 获取产品信息
      $productShortDescription = $_product->getShortDescription();
      $productDescription = $_product->getDescription();
      $productName = $_product->getName();
      $productPrice = $_product->getPrice();
      $productSpecialPrice = $_product->getSpecialPrice();
      $productProductUrl = $_product->getProductUrl();
      $productImageUrl = $_product->getImageUrl();
      $productSmallImageUrl = $_product->getSmallImageUrl();
      $productThumbnailUrl = $_product->getThumbnailUrl();
  
      // 查看产品所有信息
      $productData = $_product->getData();
      var_dump($productData);
  
      // 链式查询产品
      $code = '12321';
      $_products = Mage::getModel('catalog/product')->getCollection()
                      ->setStoreId(Mage::app()->getStore()->getId())
                      ->addAttributeToFilter('price', ['gt' => '740'])
                      ->addAttributeToFilter('type_id', ['eq' => 'simple'])
                      ->addAttributeToFilter('status', ['eq' => '1'])
                      ->addAttributeToFilter('sku', ['like' => $code])
                      ->addAttributeToSelect('*')
                      ->load();
      $entity_id = $_products->getData()[0]['entity_id'];
  			
    *备注：可通过数组遍历等方式获得产品信息并进行相应的处理。*
             

• 获取

  catagory

  下产品信息

  // 获取catagory下的产品，并按价格排序
      $catIds = explode(',', $this->getIds());
      $categories = array();
      foreach ($catIds as $catId) {
          $category = Mage::getModel('catalog/category')->load($catId);
  
          /* new code started */
          //get products from category ID
          $products = $category //Mage::getModel('catalog/category')->load($catId)
                       ->getProductCollection()
                       ->addAttributeToSelect('*') // add all attributes - optional
                       ->addAttributeToFilter('status', 1) // enabled
                       ->addAttributeToFilter('visibility', 4) //visibility in catalog,search
                       ->setOrder('price', 'ASC'); //sets the order by price
  		
  		// 获取价格范围
          $largeNumber = 9999999999;
          $minPrice = $largeNumber; //set to higher number 
          $maxPrice = 0;
          foreach($products as $product){
              if($product->getFinalPrice() < $minPrice){
                $minPrice = $product->getFinalPrice();
              }
              if($product->getFinalPrice() > $maxPrice){
                $maxPrice = $product->getFinalPrice();
              }
          }
  
          if($minPrice == $largeNumber){
              $minPrice = 0;
          }
  
          //echo "<div>".$minPrice."</div>";
          // echo "<div>".$maxPrice."</div>";
  
          /* new code finished */
  
          $categories[$category->getName()] = array(
              'url' => $category->getUrl(),
              'img' => $category->getImageUrl(),
              'minPrice' => $minPrice,
              'maxPrice' => $maxPrice
          );
      }
  
      ksort($categories, SORT_STRING);
             

• 其他查询

  // Checkout/controllers/OnepageController successAction()
      
      // table: sales_flat_quote
      $resaleTaxNumber = Mage::getSingleton('sales/quote')->load($lastQuoteId)->getData('resale_tax_number');
      
      if ($resaleTaxNumber != null) {
      
          // table: sales_flat_order
          $orderQuote =  Mage::getSingleton('sales/order');
      
          $lastOrder = $orderQuote->load($lastOrderId);
          $lastOrder->setData('resale_tax_number', $resaleTaxNumber);
          $lastOrder->save();
      }
             

- 欢迎各大神点评 -