Thread

Hey guys,

I am looking to migrate an existing client’s website to EE version 1.6.9 - I would use V2.x but there’s still a few Modules I will need that are not ready 😊

My issue is that this client has nearly 3000 existing products with images.

I don’t want to have to upload them individually or even 10 at a time. How to I go about getting these image inserted into the DB via SQL insert? Is this possible?

I’v had a quick look at the DB structure on my local installation but can’t seem to find where the images are kept.

Can someone point me in the right direction?

Thanks in advance.

I don’t want to have to upload them individually or even 10 at a time. How to I go about getting these image inserted into the DB via SQL insert? Is this possible?

Normally you wouldn’t want to insert the image as such into the database but just a pointer to it, as part of a regular weblog entry. Yes, those can be created automatically, too, although it will require some custom code.

I’v had a quick look at the DB structure on my local installation but can’t seem to find where the images are kept.

In the file system. EE does not store binary data in the db. Moving to the CodeShare Corner.

Thanks for the reply - I probably used incorrect wording in the post. I realise the images themselves are not stored in the DB, just the path to them on the server.

I’m just trying to understand the EE DB structure so I can write a sql file to populate the new DB.

I have set up a ‘Products’ weblog with the required fields. I have taken a SQL dump of the old products table and now want to adapt it to allow me to insert all the data into the EE DB. This is the process I’m trying to understand.

It seems I need to find the products weblog table and associated tables and populate them individually? Is there a specific sequence I have to follow in doing this?

Hey Steve,  I had to deal with something like this recently.  I’m using 2.1 so I’ll use the word channel instead of weblog.

It took me a little while to figure it out but the channel data appears to be kept in two tables: exp_channel_titles and exp_channel_data.  I think inserting your Products data into these two tables should be good, but I’m an amateur at dealing with the EE db so take my suggestion with a bit of caution.  (Can anybody else confim that these are the only two tables that get updated when adding a channel entry?)

The exp_channel_data table is probably where your image data is stored and it definitely took me some time to figure out where the data I was looking for was kept because the data fields aren’t named the custom names we name them in the cp, they are named field_id_1, field_id_2, and so on.  The numbering corresponds with the order that you added your custom fields to your EE installation.

A quicker way I found to determine which field I was looking for was to look at the exp_channel_fields table.  It stores your custom fields and the field_id corresponds with the field_id_X in the exp_channel_data table.  So if your field_id = 23 in the exp_channel_fields table, the data for that field is stored in field_id_23 in the exp_channel_data table.

You’ll also have to update your file upload location paths to work in your new environment.  Depending on how many different file upload locations you have, this can be a real hassel to do through the CP interface (I had several to deal with).  I found that it was easier to export the exp_upload_prefs table, copy the sql statement to a text editor and find and replace the old paths with the new ones, and re-insert the exp_upload_prefs table.

Good luck!

Thanks for the reply Ben.

I actually ended up using the Solspace ‘Importer’ Module in the end. It may not be perfect but it definitely cut the import time down. It allows you to match your old db fields to your new EE fields using XML or Excel doc.
Definitely worth a look at if you are migrating databases to EE.

Good to know the uploads prefs table 😊

Steve