Magento is one of the undisputed juggernauts of the online ecommerce industry. For massive, complex, and detailed enterprise environments, it is and has been for years the go-to solution and currently holds appx. 30% of the current market share, as of an October 2014 survey. However, from a developer’s point of view, in recent years the platform as a whole has grown stagnant, with the admin interface largely untouched since it’s initial release in 2008. In 2010 it was acquired by eBay to the tune of a quarter billion dollars, promising a 2.0 release that continues to be pushed further in the future, currently slated for general release Q4 2015. In the meantime, it becomes necessary to adapt to the tools available.

Downloadable products in Magento are quite slick, but the way they are handled I have found to be not so. The admin interface works fine I’m sure a few times through, but it quickly becomes more than one can bear when faced with the prospect of creating many hundreds of product-file relationships. The best programmer is a lazy programmer, and here are my notes on the process one would go through to associate products and their files programmatically. Note: I would not recommend this process to someone not extremely familiar and comfortable with a UNIX-flavored command line, PHP, MySQL, and Magento as a whole.

Part One: Directories and files

The first step in the process is to understand how Magento handles the physical placement of the files you will be downloading. Supposing we are building a system to allow users to purchase sheet music files, available to download after purchase. Our files might look something like this:

files/
├── beethoven_piano_sonata_no_1_in_f_minor.pdf
├── beethoven_piano_sonata_no_2_in_a_major.pdf
├── beethoven_piano_sonata_no_3_in_c_major.pdf
├── sonata_in_d_major_hob_xvi_37.pdf
├── sonata_in_c_major_hob_xvi_6.pdf
├── piano_sonata_sz_80.pdf
├── piano_sonata_c_major_k_545.pdf
├── piano_sonata_e-flat_major_k_282.pdf
└── piano_sonata_d_major_k_576.pdf

Here’s a quick overview of some piano pieces I’ve got saved. You’ll notice that even as careful as I generally am with my naming conventions, they are still reasonably irregular, some of them starting with the name of the composer, some with the form of the composition, and many using established musical reference terms (Hob. XVI is the common designation of a Haydn piano sonata, Sz. for Bartók, K for Mozart). We will avoid needing to worry about all of that later. The first task is just to get them stored in the proper directory structure.

To check it out, I uploaded a few files with product associations, to see what we’ll be setting up. From the root of the Magento install, it looked like this:

media/downloadable/files/
└── links
    ├── b
    │   └── e
    │       └── beethoven_piano_sonata_no_29_in_c_major.pdf
    ├── c
    │   ├── h
    │   │   └── chopin_piano_sonata_no_3_in_b_minor_op_58.pdf
    │   └── z
    │       └── czerny_piano_sonata_no_6_op_124.pdf
    └── s
        └── o
            └── sonata_in_e-flat_major_hob_xvi_52.pdf

Inside media/downloadable/files/links, there is a series of directories composed of the first letter of the file, followed by another directory with the second letter of the file, and finally the file itself. To start, using scp, ftp or some other protocol, move all of your files into a directory (files/, for the sake of this example) in the root dir of your Magento installation. Copy the following script into a php file, also in the root directory, and run it to create the directory structure we will need and move the files in to it.

This quick script will take all the files in the directory files/, create the first-letter dir in media/downloadable/files/links, create the subdir in media/downloadable/files/links/firstletter, and move the files appropriately. At the same time, I’m also lowercasing the filenames to be identical to what Magento would have created itself. This script handles numbers, lowercase or capital letters, and underscores fine at the least, but I would make sure you don’t have any special chars like apostrophes or spaces in the filenames before proceeding.

Part Two: Database relations

Going back to reference the files I already created/associated, I’m going to check out how they’re currently done in the db. ‘Downloadable Links’, or the references to the associated files, are stored in the downloadable_link table. Note: Magento will usually prefix your tables, so be careful about your copypasta. Checking out a single record, it looks like this:

select * from downloadable_link limit 1\G
*************************** 1. row ***************************
            link_id: 1
         product_id: 6609
         sort_order: 0
number_of_downloads: 0
       is_shareable: 0
           link_url: NULL
          link_file: /b/e/beethoven_piano_sonata_no_29_in_c_major.pdf
          link_type: file
         sample_url: NULL
        sample_file: NULL
        sample_type: file
1 row in set (0.00 sec)

You can see that the link_file is in fact just the file inside the directories we just created. It may be possible to just upload the files without the multi-directory indexing, but I’d personally rather persist with the protocol Magento itself implements. Additionally, there is a reference to the product_id, which is stored associated with your product names in the catalog_product_entity_varchar table as entity_id.

catalog_product_entity_varchar stores many other varchar references to that product, all with different attribute_ids according to which they are. If you’re curious about the other columns on this table, this blog post has an excellent overview of them all. Your attribute_ids may vary, and the table relationships are (needlessly but ¯\_(ツ)_/¯ ) complicated, so here’s a quick SQL statement you can run to get the product_id/entity_id of a product paired with the name of that product:

It may or may not be the most efficient query, but you will only need to run it once really. Note that I’m getting only the downloadable products here, because those are the only ones we’ll be working with. Be careful if you have thousands or tens of thousands of products, because it will return all of them. In fact, there’s a quick trick you can do to pipe the results from this query to a .csv for readability & workability by humans:

mysql -u username -ppassword databasename -e "QUERY HERE" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > entity_id-product_name.csv

This is a useful command that will take the results of any query and stick them into a .csv file. When I run it, I end up with something that looks like this:

And so on.

Now you’re going to do the only actual manual work that is really going to be needed, and I’m recommending it purely because there is no guarantee that the name of the file will in any way match the name of the product itself, so I’d rather not automate past the point of enforceable results. Add another column to your csv in your editor of choice titled file_name, and fill in the column with the name of the file that should be associated with that product, like so:

Don’t worry about the path to the file; because it is of a regular composition, we can reliably determine it. Technically, I suppose you can delete the product_name column after adding in the file if you like, we only really care about the entity_id and file_name for the database insert. I’m going to be keeping the title attribute on the product the same as the name of the file. When you’re finished, I’ve done up another quick php script to take that CSV and generate the appropriate SQL to create the associations:

There are three tables involved here – downloadable_link will save the reference to the file itself as well as associate it with the product. downloadable_link_price holds any price modifier for that file. An example of where that might be used is if you were offering a CD for sale, and for an extra $10.00 you could add a “digital download” of that album. I don’t need this, as the products will be purely digital, so I’ve left it at 0. The final table is downloadable_link_title, which will save the display name for the file (different from the name of the physical file, this is what will display on the front end). I’ve kept this the same as the original file name, with caps if there are any just to differentiate. The file containing this script needs to be placed in the same directory as the entity_id-file_name.csv file we created in the last step, which has the entity_id and the file_name that should be associated with it. Once you’ve run it, there will be a create-file-product-association.sql file in that same directory. Note: I prefer to save my SQL statements to a file, because it makes it easy to track my db deltas as I go. Execute that script like so:

mysql -u username -ppassword databasename < create-file-product-association.sql

Provided everything in that script worked (which it should), you’re done! It takes a lot of explaining (and a whole lot of figuring out on this lovely Saturday), but I feel like this will be a good resource for anyone who doesn’t want to interact with the administration GUI to get this task accomplished. I failed to mention that there are many many Magento extensions which will help with this as well, but I was unable to find any that weren’t paid, and I feel more comfortable with my own small scripts for something I’ll only do once than with an entire extension with questionable documentation. Feel free to leave a comment if you followed this method and let me know if it all came together for you — or not :)