Magento操作数据库

1.Database Connections In Magento

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

$readConnection=$resource->getConnection('core_read');

$writeConnection = $resource ->getConnection( 'core_write' ) ;

2.Get Table Name

$tableName = $resource->getTableName('catalog/product');

3.Reading From The Database

3.1Varien_Db_Select::fetchAll

     /**

      * Get the resource model

      */

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

     

     /**

      * Retrieve the read connection

      */

     $readConnection = $resource ->getConnection( 'core_read' );

     

     $query = 'SELECT * FROM ' . $resource ->getTableName( 'catalog/product' );

     

     /**

      * Execute the query and store the results in $results

      */

     $results = $readConnection ->fetchAll( $query );

     

     /**

      * Print out the results

      */

      var_dump( $results );


3.2Varien_Db_Select::fetchCol

     /**

       * Get the resource model

       */

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

     

     /**

      * Retrieve the read connection

      */

     $readConnection = $resource ->getConnection( 'core_read' );

     

     /**

      * Retrieve our table name

      */

     $table = $resource ->getTableName( 'catalog/product' );

 

     /**

      * Execute the query and store the results in $results

      */

     $sku = $readConnection ->fetchCol( 'SELECT sku FROM ' . $table . ');

     

     /**

      * Print out the results

      */

      var_dump( $results );

3.3Varien_Db_Select::fetchOne

   /**

      * Get the resource model

      */

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

     

     /**

      * Retrieve the read connection

      */

     $readConnection = $resource ->getConnection( 'core_read' );

 

     /**

      * Retrieve our table name

      */

     $table = $resource ->getTableName( 'catalog/product' );

     

     /**

      * Set the product ID

      */

     $productId = 44;

     

     $query = 'SELECT sku FROM ' . $table . ' WHERE entity_id = '

              . (int) $productId . ' LIMIT 1' ;

     

     /**

      * Execute the query and store the result in $sku

      */

     $sku = $readConnection ->fetchOne( $query );

     

     /**

      * Print the SKU to the screen

      */

     echo 'SKU: ' . $sku . '<br/>' ;

4.Writing To The Database

<?php

 

     /**

      * Get the resource model

      */

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

     

     /**

      * Retrieve the write connection

      */

     $writeConnection = $resource ->getConnection( 'core_write' );

 

     /**

      * Retrieve our table name

      */

     $table = $resource ->getTableName( 'catalog/product' );

     

     /**

      * Set the product ID

      */

     $productId = 44;

     

     /**

      * Set the new SKU

      * It is assumed that you are hard coding the new SKU in

      * If the input is not dynamic, consider using the

      * Varien_Db_Select object to insert data

      */

     $newSku = 'new-sku' ;

     

     $query = "UPDATE {$table} SET sku = '{$sku}' WHERE entity_id = "

              . (int) $productId ;

     

     /**

      * Execute the query

      */

     $writeConnection ->query( $query );

Truncate table`tb_category`;
      INSERT INTO `tb_category` (`cid`, `parent_cid`, `name`, `is_leaf`, `sort_order`, `feature_list`)
SELECT * FROM (SELECT 11122, 0, '数码相机/单反相机/摄像机', 'false', 27, '') AS tmp
WHERE NOT EXISTS (
    SELECT `cid` FROM `tb_category` WHERE `cid`=11122
)