Magento - Module INSERT,UPDATE, DELETE, SELECT code

Suppose, I have a module named mynews. Here follows the code to select, insert, update, and delete data from the news table.

INSERT DATA

$data contains array of data to be inserted. The key of the array should be the database table’s field name and the value should be the value to be inserted.

$data = array('title'=>'hello there','content'=>'how are you? i am fine over here.','status'=>1);
$model = Mage::getModel('mynews/mynews')->setData($data);
try {
    $insertId = $model->save()->getId();
    echo "Data successfully inserted. Insert ID: ".$insertId;
} catch (Exception $e){
 echo $e->getMessage();   
}

SELECT DATA

$item->getData() prints array of data from ‘news’ table.
$item->getTitle() prints the only the title field.

Similarly, to print content, we need to write $item->getContent().

$model = Mage::getModel('mynews/mynews');
$collection = $model->getCollection();
foreach($collection as $item){
print_r($item->getData());
print_r($item->getTitle());
}

UPDATE DATA

$id is the database table row id to be updated. $data contains array of data to be updated. The key of the array should be the database table’s field name and the value should be the value to be updated.

// $id = $this->getRequest()->getParam('id');
$id = 2;
$data = array('title'=>'hello test','content'=>'test how are you?','status'=>0);
$model = Mage::getModel('mynews/mynews')->load($id)->addData($data);
try {
    $model->setId($id)->save();
    echo "Data updated successfully.";

} catch (Exception $e){
    echo $e->getMessage(); 
}

DELETE DATA

$id is the database table row id to be deleted.

// $id = $this->getRequest()->getParam('id');
$id = 3;
$model = Mage::getModel('mynews/mynews');
try {
    $model->setId($id)->delete();
    echo "Data deleted successfully.";

} catch (Exception $e){
    echo $e->getMessage(); 
}

In this way you can perform select, insert, update and delete in your custom module and in any magento code.

How to load all the products under one category in magento?.

If we want to load all the products under one category we need to use below code is the simplest way,

 $cat_id = 5;
 $collection = Mage::getModel("catalog/product")->getCollection();
 $_category =  Mage::getModel("catalog/category")->load($cat_id);    
 $collection->addCategoryFilter($_category);

How to get address collection of all customers in magento?

- To get all the address collection created by various customers in magento achieved by the following simple code,

$addressesCollection = Mage::getResourceModel('customer/address_collection');
$addressesCollection->addAttributeToSelect('*');
/* for particular address 
$addressesCollection->addFieldToFilter('id','12'); */

foreach ($addressesCollection as $address) {
echo "<pre>";
print_r($address->getData());
}

How to use select, update, delete and insert custom queries in magento

- We can Perform select, update, delete and insert queries through php file by following steps.


require_once '../../app/Mage.php';
Mage::app('default');


        Select query to get the value form table           

$connection = Mage::getSingleton('core/resource')
->getConnection('core_read');
$select = $connection->select()
->from('tablename', array('*')) // select * from tablename or use array('id','name') selected values
->where('id=?',1)               // where id =1
->group('name');         // group by name
$rowsArray = $connection->fetchAll($select); // return all rows
$rowArray =$connection->fetchRow($select);   //return row


             Insert query                  

$connection = Mage::getSingleton('core/resource')
->getConnection('core_write');
$connection->beginTransaction();
$fields = array();
$fields['name']= 'test';
$fields['age']='25';
$connection->insert('tablename', $fields);

$connection->commit();

            update query                 

$connection = Mage::getSingleton('core/resource')
->getConnection('core_write');
$connection->beginTransaction();
$fields = array();
$fields['name'] = 'jony';

//for single parameter
$where = $connection->quoteInto('id =?', '1');

/* For mulitple parameters
$where = $connection->quoteInto('id =?', '1')
                 .$connection->quoteInto('name =?', 'Mathi') ;
*/

$connection->update('tablename', $fields, $where);

$connection->commit();


                delete query                    

$condition = array($connection->quoteInto('id=?', '1'));

$connection->delete('tablename', $condition);

Magento - Remove or rename add new save continue delete button from magento admin

If you don’t want to show the ‘Add New’ button in the Grid. The Add New button is present in top right corner of Grid Page.

Rename ‘Add New’ button

Here are the steps to rename the ‘Add New’ text to anything you required (for example, ‘Add Report’):-

- Go to YourNamespace -> YourModule -> Block -> Adminhtml -> YourFile.php
- Add the following code in the constructor of this file:-

$this->_addButtonLabel = Mage::helper('yourmodulename')->__('Add Report');

Remove ‘Add New’ button

Here are the steps to remove the ‘Add New’ button:-

- Go to YourNamespace -> YourModule -> Block -> Adminhtml -> YourFile.php
- Add the following code in the constructor of this file (it should be just below the call to parent constructor):-

parent::__construct();
$this->_removeButton('add');


In edit.php
parent::__construct();
$this->_removeButton('delete');// Removes the delete button from edit page
$this->_removeButton('save');
$this->_removeButton('back');

in grid.php
parent::__construct();
$this->_removeButton('add');

Magento - Get Last Insert Id

- To get last inserted in magento there is two ways based on the insert method

First:

$connection    = Mage::getSingleton(’core/resource’)->getConnection(’core_write’);
$sql         = ”INSERT INTO `table` SET field1 = ?, field2 = ?, …”;
$connection->query($sql, array($field1_value, $field2_value, …));
$lastInsertId = $connection->lastInsertId();
echo $lastInsertId;

Second:

  $customAddress = Mage::getModel('customer/address');

         $customAddress->setData($_custom_address)
            ->setCustomerId($customer->getId())
            ->setIsDefaultBilling('0')
            ->setIsDefaultShipping('1')
            ->setSaveInAddressBook('1');

         try {
           $customAddress->save(); 

echo $customAddress->getId();

Magento - Magento TIPS

                To instert datat in DB
               ========================      
               $insert_data = array(
               col1 => value
               );
               $_conn = Mage::getSingleton('core/resource')->getConnection('core_write');
            $_conn->insert(Mage::getSingleton('core/resource')->getTableName('table_name'), $insert_data);
           
               =================================
           
               To use session
               ----------------
               protected function _getSession()
               {
                    return Mage::getSingleton('core/session');
               }
               $this->_getSession()->addSuccess(Mage::helper('module_name')->__('message goes here'));
           
               ======================================
               http://www.magentocommerce.com/knowledge-base/entry/magento-for-dev-part-8-varien-data-collections
           
               to select rows based on where
               ------------------------------
           
               var_dump((string)
               Mage::getModel('vendorinfo/favorites')
               ->getCollection()
               ->addFieldToFilter('user_id',$getCustomer['entity_id'])
               ->getSelect());        
               ============================================
Have a look at the helper class: Mage_Customer_Helper_Data
To simply get the customer name, you can write the following code:

$customerName = Mage::helper('customer')->getCustomerName();


For more information about the customer's entity id, website id, email, etc. you can use getCustomer function. The following code shows what you can get from it:-
echo "<pre>"; print_r(Mage::helper('customer')->getCustomer()->getData()); echo "</pre>";

               ============================================

Magento How get customer/user detail by Id

$customer_id = 1; // set this to the ID of the customer.
$customer_data = Mage::getModel('customer/customer')->load($customer_id);
print_r($customer_data);
Now if you want the customer name then you can use the following code:
$customer_data->getFirstname();

=====================================================================
Function to get wishlist items with out login based on wish_id

    public function getWishlistItems($wish_id)
    {
         $get_wishlist_data = Mage::getModel('wishlist/item')->getCollection();
          $get_wishlist_data->addFieldToSelect('product_id');
        return $get_wishlist_data->addFieldToFilter('main_table.wishlist_id', $wish_id);    
    }

Function to get wishlist collect

publicfunction getWishList(){
    $_itemCollection =Mage::helper('wishlist')->getWishlistItemCollection();
    $_itemsInWishList = array();foreach($_itemCollection as $_item){
        $_product = $_item->getProduct();

        $_itemsInWishList[$_product->getId()]= $_item;}return $_itemsInWishList;}
=====================================================================
http://blog.decryptweb.com/product-id-and-product-name/
1) Product details from Product ID.
<?php
$model = Mage::getModel('catalog/product') //getting product model

$_product = $model->load($productid); //getting product object for particular product id

echo $_product->getShortDescription(); //product's short description
echo $_product->getDescription(); // product's long description
echo $_product->getName(); //product name
echo $_product->getPrice(); //product's regular Price
echo $_product->getSpecialPrice(); //product's special Price
echo $_product->getProductUrl(); //product url
echo $_product->getImageUrl(); //product's image url
echo $_product->getSmallImageUrl(); //product's small image url
echo $_product->getThumbnailUrl(); //product's thumbnail image url

?>

==========================================================================
Using Collections in Magento
http://www.magentocommerce.com/wiki/5_-_modules_and_development/catalog/using_collections_in_magento

==========================================================================
Using Display error in Magento

put this in htaccess file
php_flag display_errors on
SetEnv MAGE_IS_DEVELOPER_MODE true
==========================================================================
Create Admin (Backend) Module in Magento with Grids
http://www.webspeaks.in/2010/08/create-admin-backend-module-in-magento.html



call a block in a phtml instead of through a layout?

<?php print $this->getLayout()->createBlock("catalog/product_view")->setTemplate("catalog/product/view/addto.phtml")->toHtml();?>

==========================================================================

To Enable Tag in Admin

go module and enable mage_tag
==========================================================================
To delete the module setup in magento DB, use core_resource table.
==========================================================================

Magento – check if user logged in

<?php
if ($this->helper('customer')->isLoggedIn() ) {
    echo "Welcome!";
} else {
    echo "Please log in.";
}
?>

==========================================================================

 


1. In the Admin Panel, go to System> Configuration
2. In the left column select the Customer Configuration tab under the Customers category.
3. Expand the Login Options bar to display the following options:
4. Redirect Customer to Account Dashboard after Logging in - Select No to allow customer to stay on their current page. Select Yes to redirect customers to the Account Dashboard after they successfully log in.
5. Click the [Save Config] button to save your changes.

==========================================================================

http://linssen.me/entry/extending-the-jquery-sortable-with-ajax-mysql/

=======================================================================

Pass data from controller to view in magento

Controller function:
public function indexAction()
{
  $bla = Mage::getSingleton('Modulename/viewinfo');
  $bla->setMsg("Thank You for your visit!");
  $this->loadLayout();
  $this->renderLayout();
}

Now in your View .phtml file add:
<?php $blas = Mage::getSingleton('Modulename/viewinfo'); ?>
<?php echo $blas->getMsg(); ?>

=========================================================================

Magento: get skin url, get media url, get base url, get store url

http://www.webdosh.net/2011/04/magento-get-skin-url-get-media-url-get.html

=========================================================

To get customer id

$customer = Mage::getSingleton('customer/session')->getCustomer();

$customerid = $customer->getId();

=========================================================

replace base_url

SELECT *
FROM `core_config_data`
WHERE `path` LIKE "%_url%"
LIMIT 0 , 30;

=========================================================

Zend_Debug::dump($this->getLayout()->getUpdate()->getHandles());

=========================================================
Current user in Magento?
down vote
Have a look at the helper class: Mage_Customer_Helper_Data
To simply get the customer name, you can write the following code:-
$customerName =Mage::helper('customer')->getCustomerName();

For more information about the customer's entity id, website id, email, etc. you can use getCustomer function. The following code shows what you can get from it:-
echo "<pre>"; print_r(Mage::helper('customer')->getCustomer()->getData()); echo "</pre>";
From the helper class, you can also get information about customer login url, register url, logout url, etc.
From the isLoggedIn function in the helper class, you can also check whether a customer is logged in or not.



=========================================================

<div class="block-content">
        <ol id="recently-viewed-items" class="mini-products-list">
        <?php foreach ($_products as $_item): ?>
            <li class="item">
                <a href="<?php echo $this->getProductUrl($_item) ?>" class="product-image"><img src="<?php echo $this->helper('catalog/image')->init($_item, 'small_image')->resize(50, 50) ?>" width="50" height="50" alt="<?php echo $this->htmlEscape($_item->getName()) ?>" /> </a>
                <div class="product-details"><p class="product-name"><a href="<?php echo $this->getProductUrl($_item) ?>"><?php echo $this->htmlEscape($_item->getName()) ?></a></p></div>
            </li>
        <?php endforeach; ?>
        </ol>
        <script type="text/javascript">decorateList('recently-viewed-items');</script>
    </div>
================================================

http://www.catgento.com/adding-configurable-product-options-to-category-list-in-magento/

================================================
http://www.magentocommerce.com/wiki/5_-_modules_and_development/search_and_advanced_search/how_to_add_search_by_category_to_advanced_search
================================================

How to Import multiple images through CSV,
Then we need to make a small change in app/code/core/Mage/Catalog/Model/Convert/Adapter/Product.php (Do keep a backup of original).
post the code just above:
$product->setIsMassupdate(true);

$galleryData = explode(',',$importData['gallery']);
        foreach($galleryData as $gallery_img)
        {
            if(!empty($gallery_img)){
                try {
                    $product->addImageToMediaGallery(Mage::getBaseDir('media') . DS . 'import' . $gallery_img, null, false, false);
                }
            catch (Exception $e) {}
            }
        }

http://go.magento.com/support/kb/entry/name/working-with-product-csv-files/
================================================
http://www.excellencemagentoblog.com/useful-code-snippets
==================================================
Create Order

 Below is the php code to create an order in magento. It requires a valid customer account with shipping and billing address setup.
$id=1; // get Customer Id
$customer = Mage::getModel('customer/customer')->load($id);

$transaction = Mage::getModel('core/resource_transaction');
$storeId = $customer->getStoreId();
$reservedOrderId = Mage::getSingleton('eav/config')->getEntityType('order')->fetchNewIncrementId($storeId);

$order = Mage::getModel('sales/order')
->setIncrementId($reservedOrderId)
->setStoreId($storeId)
->setQuoteId(0)
->setGlobal_currency_code('USD')
->setBase_currency_code('USD')
->setStore_currency_code('USD')
->setOrder_currency_code('USD');
//Set your store currency USD or any other

// set Customer data
$order->setCustomer_email($customer->getEmail())
->setCustomerFirstname($customer->getFirstname())
->setCustomerLastname($customer->getLastname())
->setCustomerGroupId($customer->getGroupId())
->setCustomer_is_guest(0)
->setCustomer($customer);

// set Billing Address
$billing = $customer->getDefaultBillingAddress();
$billingAddress = Mage::getModel('sales/order_address')
->setStoreId($storeId)
->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_BILLING)
->setCustomerId($customer->getId())
->setCustomerAddressId($customer->getDefaultBilling())
->setCustomer_address_id($billing->getEntityId())
->setPrefix($billing->getPrefix())
->setFirstname($billing->getFirstname())
->setMiddlename($billing->getMiddlename())
->setLastname($billing->getLastname())
->setSuffix($billing->getSuffix())
->setCompany($billing->getCompany())
->setStreet($billing->getStreet())
->setCity($billing->getCity())
->setCountry_id($billing->getCountryId())
->setRegion($billing->getRegion())
->setRegion_id($billing->getRegionId())
->setPostcode($billing->getPostcode())
->setTelephone($billing->getTelephone())
->setFax($billing->getFax());
$order->setBillingAddress($billingAddress);

$shipping = $customer->getDefaultShippingAddress();
$shippingAddress = Mage::getModel('sales/order_address')
->setStoreId($storeId)
->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_SHIPPING)
->setCustomerId($customer->getId())
->setCustomerAddressId($customer->getDefaultShipping())
->setCustomer_address_id($shipping->getEntityId())
->setPrefix($shipping->getPrefix())
->setFirstname($shipping->getFirstname())
->setMiddlename($shipping->getMiddlename())
->setLastname($shipping->getLastname())
->setSuffix($shipping->getSuffix())
->setCompany($shipping->getCompany())
->setStreet($shipping->getStreet())
->setCity($shipping->getCity())
->setCountry_id($shipping->getCountryId())
->setRegion($shipping->getRegion())
->setRegion_id($shipping->getRegionId())
->setPostcode($shipping->getPostcode())
->setTelephone($shipping->getTelephone())
->setFax($shipping->getFax());

$order->setShippingAddress($shippingAddress)
->setShipping_method('flatrate_flatrate');
/*->setShippingDescription($this->getCarrierName('flatrate'));*/
/*some error i am getting here need to solve further*/

//you can set your payment method name here as per your need
$orderPayment = Mage::getModel('sales/order_payment')
->setStoreId($storeId)
->setCustomerPaymentId(0)
->setMethod('purchaseorder')
->setPo_number(' – ');
$order->setPayment($orderPayment);

// let say, we have 1 product
//check that your products exists
//need to add code for configurable products if any
$subTotal = 0;
$products = array(
    '1' => array(
    'qty' => 2
    )
);

foreach ($products as $productId=>$product) {
$_product = Mage::getModel('catalog/product')->load($productId);
$rowTotal = $_product->getPrice() * $product['qty'];
$orderItem = Mage::getModel('sales/order_item')
->setStoreId($storeId)
->setQuoteItemId(0)
->setQuoteParentItemId(NULL)
->setProductId($productId)
->setProductType($_product->getTypeId())
->setQtyBackordered(NULL)
->setTotalQtyOrdered($product['rqty'])
->setQtyOrdered($product['qty'])
->setName($_product->getName())
->setSku($_product->getSku())
->setPrice($_product->getPrice())
->setBasePrice($_product->getPrice())
->setOriginalPrice($_product->getPrice())
->setRowTotal($rowTotal)
->setBaseRowTotal($rowTotal);

$subTotal += $rowTotal;
$order->addItem($orderItem);
}

$order->setSubtotal($subTotal)
->setBaseSubtotal($subTotal)
->setGrandTotal($subTotal)
->setBaseGrandTotal($subTotal);

$transaction->addObject($order);
$transaction->addCommitCallback(array($order, 'place'));
$transaction->addCommitCallback(array($order, 'save'));
$transaction->save();
===========================================================

Creating Customer

Below is a php code to create a customer account in magento.
$customer = Mage::getModel('customer/customer');
$password = 'test1234';
$email = 'dtest@gmail.com<script type="text/javascript">
/* <![CDATA[ */
(function(){try{var s,a,i,j,r,c,l,b=document.getElementsByTagName("script");l=b[b.length-1].previousSibling;a=l.getAttribute('data-cfemail');if(a){s='';r=parseInt(a.substr(0,2),16);for(j=2;a.length-j;j+=2){c=parseInt(a.substr(j,2),16)^r;s+=String.fromCharCode(c);}s=document.createTextNode(s);l.parentNode.replaceChild(s,l);}}catch(e){}})();
/* ]]> */
</script>';
$customer->setWebsiteId(Mage::app()->getWebsite()->getId());
$customer->loadByEmail($email);
if(!$customer->getId()) {
    $groups = Mage::getResourceModel('customer/group_collection')->getData();
    $groupID = '3';

    $customer->setData( 'group_id', $groupID );
    $customer->setEmail($email);
    $customer->setFirstname('test');
    $customer->setLastname('testing');
    $customer->setPassword($password);

    $customer->setConfirmation(null);
    $customer->save();

    echo $customer->getId();
}

 ==================================================
Creating Invoice

Below is a php code to create invoice for an order in magento.
$order = Mage::getModel('sales/order')->loadByIncrementId('100000001');
try {
if(!$order->canInvoice())
{
Mage::throwException(Mage::helper('core')->__('Cannot create an invoice.'));
}

$invoice = Mage::getModel('sales/service_order', $order)->prepareInvoice();

if (!$invoice->getTotalQty()) {
Mage::throwException(Mage::helper('core')->__('Cannot create an invoice without products.'));
}

$invoice->setRequestedCaptureCase(Mage_Sales_Model_Order_Invoice::CAPTURE_ONLINE);
//Or you can use
//$invoice->setRequestedCaptureCase(Mage_Sales_Model_Order_Invoice::CAPTURE_OFFLINE);
$invoice->register();
$invoice->sendEmail(); $invoice->setEmailSent(true);
$transactionSave = Mage::getModel('core/resource_transaction')
->addObject($invoice)
->addObject($invoice->getOrder());

$transactionSave->save();
}
catch (Mage_Core_Exception $e) {

}
============================================================
Create Shipment

Below is a php code to create a shipment for an order in magento.
$order = Mage::getModel('sales/order')->loadByIncrementId('100000001');
try {
    if($order->canShip()) {
        //Create shipment
        $shipmentid = Mage::getModel('sales/order_shipment_api')
                        ->create($order->getIncrementId(), array());
        //Add tracking information
        $ship = Mage::getModel('sales/order_shipment_api')
                        ->addTrack($order->getIncrementId(), array());    
    }
}catch (Mage_Core_Exception $e) {
 print_r($e);
}

Creating Credit Memo

Below is a php code to create a credit memo for an order in magento.
$order = Mage::getModel('sales/order')->load('100000001', 'increment_id');
        if (!$order->getId()) {
            $this->_fault('order_not_exists');
        }
        if (!$order->canCreditmemo()) {
            $this->_fault('cannot_create_creditmemo');
        }
        $data = array();

       
        $service = Mage::getModel('sales/service_order', $order);
     
        $creditmemo = $service->prepareCreditmemo($data);

        // refund to Store Credit
        if ($refundToStoreCreditAmount) {
            // check if refund to Store Credit is available
            if ($order->getCustomerIsGuest()) {
                $this->_fault('cannot_refund_to_storecredit');
            }
            $refundToStoreCreditAmount = max(
                0,
                min($creditmemo->getBaseCustomerBalanceReturnMax(), $refundToStoreCreditAmount)
            );
            if ($refundToStoreCreditAmount) {
                $refundToStoreCreditAmount = $creditmemo->getStore()->roundPrice($refundToStoreCreditAmount);
                $creditmemo->setBaseCustomerBalanceTotalRefunded($refundToStoreCreditAmount);
                $refundToStoreCreditAmount = $creditmemo->getStore()->roundPrice(
                    $refundToStoreCreditAmount*$order->getStoreToOrderRate()
                );
                // this field can be used by customer balance observer
                $creditmemo->setBsCustomerBalTotalRefunded($refundToStoreCreditAmount);
                // setting flag to make actual refund to customer balance after credit memo save
                $creditmemo->setCustomerBalanceRefundFlag(true);
            }
        }
        $creditmemo->setPaymentRefundDisallowed(true)->register();
        // add comment to creditmemo
        if (!empty($comment)) {
            $creditmemo->addComment($comment, $notifyCustomer);
        }
        try {
            Mage::getModel('core/resource_transaction')
                ->addObject($creditmemo)
                ->addObject($order)
                ->save();
            // send email notification
            $creditmemo->sendEmail($notifyCustomer, ($includeComment ? $comment : ''));
        } catch (Mage_Core_Exception $e) {
            $this->_fault('data_invalid', $e->getMessage());
        }
        echo $creditmemo->getIncrementId();
============================================================================
Magento Change order status to complete without invoicing

$orderOBJ = Mage::getModel('sales/order')->load($order->getId());
$orderOBJ->setStatus('complete');
$orderOBJ->save();

============================================================================
 $rClass = new ReflectionClass($this);
var_dump($rClass->getFilename());

============================================================================