Magento Database Log Cleaning

Its important to keep Magento running as fast and efficient as possible, one thing that can easily be over looked and scare people is cleaning the database. Magento over time gathers logs on customers and orders that isn’t actually needed. To clean them out go to phpmyadmin and empty or truncate the following tables:

dataflow_batch_export
dataflow_batch_import
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
report_viewed_product_index
report_compared_product_index
report_event

Over the course of time Magento databases can grow to many gigabytes, by running the above command I have seen the size drop to a quarter of its original size and vastly improve the time to byte.

Command Line DB changes

I recently came across a reason to know how to alter the database using command line

  1. Log into the client’s server using terminal.
  2. Gain access to MySQL using m or mysql -u root -p.
  3. Access their database using: use database ;

where database is the database name.

  1. Run the following command:  select * from core_config_data where path like ‘%base%url%’;
  2. This will display the current base_urls set in magento.
  3. To change the base URLs run:
update core_config_data set value = 'http://domainname/' where path = 'web/unsecure/base_url';
update core_config_data set value = 'http://domainname/' where path = 'web/secure/base_url';

Magento add Gift Message to packing slip

I wanted to add gift messages to the PDF packing slip, for some reason this isn’t standard in Magento (you write a gift message that never goes to customer, makes sense)

To add the gift message you need to go to /app/code/core/Mage/Sales/Model/Order/Pdf/Shipment.php

You then need to add the following code, I added it after

Mage::getStoreConfigFlag(self::XML_PATH_SALES_PDF_SHIPMENT_PUT_ORDER_ID, $order->getStoreId())
);

But it depends where you want to display it on the PDF

/* START Add Gift Message to Packing Slip */
$this->_drawHeader1($page);
$this->_setFontBold($style, 10);

$message = Mage::getModel(‘giftmessage/message’);
$gift_message_id = $order->getGiftMessageId();
if(!is_null($gift_message_id)) {

$message->load($gift_message_id);
$gift_sender = $message->getData(‘sender’);
$gift_recipient = $message->getData(‘recipient’);
$gift_message = $message->getData(‘message’);

$page->drawText(Mage::helper(‘sales’)->__(‘Message from:’), 35, $this->y, ‘UTF-8’);
$page->drawText(Mage::helper(‘sales’)->__($gift_sender), 120, $this->y, ‘UTF-8’);
$this->y -=15;
$page->drawText(Mage::helper(‘sales’)->__(‘Message to:’), 35, $this->y, ‘UTF-8’);
$page->drawText(Mage::helper(‘sales’)->__($gift_recipient), 120, $this->y, ‘UTF-8’);
$this->y -=15;
$page->drawText(Mage::helper(‘sales’)->__(‘Message:’), 35, $this->y, ‘UTF-8’);
$page->drawText(Mage::helper(‘sales’)->__($gift_message), 120, $this->y, ‘UTF-8’);
$this->y -=15;

while ($token != false) {
if ($y < 60) {
$pdf->pages[] = ($page = $pdf->newPage(Zend_Pdf_Page::SIZE_A4));
$page->setStyle($style);
} else {
$page->drawText($token, 30, $this->y);
$this->y-=10;
}
$token = strtok(“\n”);
}
}
/* END Gift Message to Packing Slip */

Magento Coupons insert

Experimenting with inserting magento coupon codes directly into the db ive found you need to run the following two inserts

 

INSERT INTO `mg_salesrule` (`rule_id`, `name`, `description`, `from_date`, `to_date`, `uses_per_customer`, `is_active`,

`conditions_serialized`, `actions_serialized`, `stop_rules_processing`, `is_advanced`, `product_ids`, `sort_order`,

`simple_action`, `discount_amount`, `discount_qty`, `discount_step`, `simple_free_shipping`, `apply_to_shipping`,

`times_used`, `is_rss`, `coupon_type`, `use_auto_generation`, `uses_per_coupon`, `promo_sku`)

VALUES (181, ‘Wills Test’, NULL, NULL, NULL, 0, 1, ‘a:6:{s:4:”type”;s:32:”salesrule/rule_condition_combine”;s:9:”attribute”;N;s:8:”operator”;N;s:5:”value”;s:1:”1″;s:18:

“is_value_processed”;N;s:10:”aggregator”;s:3:”all”;}’, ‘a:7:

{s:4:”type”;s:40:”salesrule/rule_condition_product_combine”;s:9:”attribute”;N;s:8:”operator”;N;s:5:”value”

;s:1:”1″;s:18:”is_value_processed”;N;s:10:”aggregator”;s:3:”all”;s:10:”conditions”;a:1:{i:0;a:5:

{s:4:”type”;s:32:”salesrule/rule_condition_product”;s:9:”attribute”;s:3:”sku”;s:8:”operator”;s:2:”()”;s:5:

“value”;s:12:”57, 122,

290″;s:18:”is_value_processed”;b:0;}}}’, 0, 1, NULL, 0, ‘by_percent’, ‘10.0000’, NULL, 0, 0, 0, 2, 1, 2, 0, 0, NULL);

 

INSERT INTO `mg_salesrule_coupon` (`coupon_id`, `rule_id`, `code`, `usage_limit`, `usage_per_customer`, `times_used`, `expiration_date`, `is_primary`, `created_at`, `type`) VALUES
(9999, 191, ‘willstest’, NULL, NULL, 0, ‘NULL’, 1, ‘2012-10-04 12:37:05’, 0);

 

 

Then to assign the correct customer groups

 

INSERT INTO `mg_salesrule_customer_group` (`rule_id`, `customer_group_id`) VALUES
(181, 0),
(181, 1),
(181, 4);

 

For attributes
INSERT INTO `mg_salesrule_product_attribute` (`rule_id`, `website_id`, `customer_group_id`, `attribute_id`) VALUES
(181, 1, 0, 74),
(181, 1, 1, 74),
(181, 1, 4, 74);

 

For different store fronts

 

INSERT INTO `mg_salesrule_website` (`rule_id`, `website_id`) VALUES
(181, 1);

 

Magento Remove Sidebar Cart and Compare

To remove the Cart and compare products from the sidebar within magento go to

customer.xml in your themes layout and comment out the following couple of lines

<block type=”checkout/cart_sidebar” name=”cart_sidebar” template=”checkout/cart/sidebar.phtml”>
<action method=”addItemRender”><type>simple</type><block>checkout/cart_item_renderer</block><template>checkout/cart/sidebar/default.phtml</template></action>
<action method=”addItemRender”><type>grouped</type><block>checkout/cart_item_renderer_grouped</block><template>checkout/cart/sidebar/default.phtml</template></action>
<action method=”addItemRender”><type>configurable</type><block>checkout/cart_item_renderer_configurable</block><template>checkout/cart/sidebar/default.phtml</template></action>
</block>
<block type=”catalog/product_compare_sidebar” name=”catalog.compare.sidebar” template=”catalog/product/compare/sidebar.phtml”/>

 

Magento Custom Options on category

<?php
Mage::app(‘base’);
$product = Mage::getModel(‘catalog/product’)->load($_product->getId());
$options = $product->getOptions();
//calendarstock/calendarstock
echo ‘Other parking options’;
foreach ($options as $option) {
foreach($option->getValues() as $value) {
echo ‘<span>’.$value->getTitle().’ </span>’;
$fvalue = $value->getPrice(true);
echo ‘<span class=”custom-price”>’.number_format($fvalue, 2).'</span> | ‘;
}
}
?>