Database

Magento Database is a nice little monster when you compare it to some other eCommerce platform. It is like that mostly due EAV architecture. Following articles speak about various database topics.


Programmatically create upsell, cross sell and related products in Magento

Programmatically create upsell, cross sell and related products in Magento

This article will explain how to add upsell, cross sell and related products programmatically to Magento. One of practical examples would be data migration from some other ecommerce system to Magento. You can read a nice article on how to add upsell, cross sell and related products from administration here. It explains what all these product relations mean and where are they used on the site.

Read more

External database connection in Magento

magento external database connection

Most of the time working with Magento, a single database connection is just enough. Magento has excellent system of adding new tables in database or extending existing ones. So, why would there be a need for an external database connection outside the Magento system? Well, one of the examples is data migration from another ecommerce system. In this article, a simple connection to external database is explained with CRUD (create, read, update, delete) examples.

Read more

Thou Shalt Not Do Inserts in a Foreach … Unless You Know The Trick

Thou Shalt Not Do Inserts in a Foreach … Unless You Know The Trick

TL;DR;

Don’t do it. Just, simply, never write a database query inside a loop. Ever. Put in a little bit of effort and write code that will insert or read all data in one big batch, or at least group big chunks of data in the smallest possible number of queries. That’s it. Move on. These aren’t the droids you’re looking for.

Read more

Magento – Install, install upgrade, data and data upgrade scripts

Magento – Install, install upgrade, data and data upgrade scripts

If you are in the business of creating your own custom extension (module) for magento, then chances are that at some point you will need your extension to be able to persist data to database, this is where the installation scripts come in place. Later on, when you upgrade your extension and decide to add few new tables to database or few new columns to database table that was initially created by the original installation extension you will need to look for upgrade script. Finally, if you want to set some initial default values in the database tables of your extension you will need to look for data scripts.

Read more

Prepared filter for Magento admin grids

Prepared filter for Magento admin grids

Maybe you can ask yourself why do I need prepared data for filters in my collections – in Magento admin grids. Well, I can perhaps create new grid and action for some custom functionality and show such grid and then additionally do (filter by) whatever I need.

But notice that if you create such collection with some addFieldToFilter() method you always use specified filter for that collection.
If you are interested how to use only one collection for showing different results for that collection (prepared filter), keep reading.

Read more

How to multiselect and filter in magento admin grids – IN and NOT IN

How to multiselect and filter in magento admin grids – IN and NOT IN

Did you ever need to create grid in Magento administration that is filterable by more column’s values, i.e. something from multiselect drop-down. Or perhaps more specific, you want to export all pending orders (order status: pending, pending_payment, pending_paypal) but you aren’t satisfied with Magento implementation of selecting, searching and exporting one by one pending order status. If you’re interested how I implemented similar behavior with input text field, simulating MySQL “IN” and “NOT IN” statement, keep reading!

Read more

Magento: Date format troubles

Magento: Date format troubles © shadowkill@sxc.hu

In this post, I will just mention some of the issues with date format from my own experience, in hope that this will help somebody to save few hours of tracing Magento to find the solution :-).

Read more

Solving problems with Category tree database information

Solving problems with Category tree database information © backtrust@sxc.hu

Did you ever had a problem where you can not see all available categories in “Categories” tab while editing a product? Does your Indexer process hangs when updating “Catalog URL rewrites”? At the same time your category tree looks just fine on “Manage Categories” menu and you can see assigned products in each category? This issue can happen for a number of reasons, but it usually happens when you are using some bad script for importing category information from some other 3rd party system (like switching your shop from osCommerce, for example). Well, look no further, here’s the solution.

Read more

How to translate form labels in Magento’s Admin Area

How to translate form labels in Magento’s Admin Area

Since Magento has built in functionality for translations, you can ask yourself why Magento doesn’t want to translate my labels… and everything is set up correctly!
For all of you who work on Magento for a while and you didn’t yet needed to translate everything in Magento admin area here is a short “howto” on what you can do:
go to app/code/core/Mage/Adminhtml/Block/Widget/Form.php
and find protected function _setFieldset($attributes, $fieldset, $exclude=array())
Instead of this line

//'label' => $attribute->getFrontend()->getLabel(),
//put this:
'label' => Mage::helper('core')->__($attribute->getFrontend()->getLabel()),

Of course you will not change Magento’s core file in this way 🙂 (more below).

Read more

How to add new custom category attribute in Magento

How to add new custom category attribute in Magento

Sometimes you need to extend functionality of Magento categories. There is several ways to do that, I will show you how it can be done.
You can do that by modifying and adding data into some of tables directly, but it can be waste of time if you don’t know what you are doing.
This post will describe how you can add new custom category attribute in your Magento store via sql_setup script.

Read more

Mysteries of Magento Encryption Key

Mysteries of Magento Encryption Key

If you ever went through Magento installation process, you know that at some point you are asked for Magento Encryption key. Magento will automatically generate one for you if you do not enter anything in this field. For first installation, this is just fine. You will see a note that Magento uses this key to encrypt passwords, credit cards and more. Is this really the case?

Read more

Magento MySQL database diagram

Magento MySQL database diagram

If you worked with osCommerce, Zen Cart, CRE Loaded or any similar eCommerce platform before, you might find Magento database structure quite confusing when you see it for the first time. I advise you not to rush too much figuring out what is what by glancing through database. Try to spend first few hours getting familiar with some background. For purposes of flexibility, the Magento database heavily utilizes an Entity-Attribute-Value (EAV) data model. As is often the case, the cost of flexibility is complexity. Is there something in Magento that is simple from developers point of view?

Read more