[greyhat]

Festive greetings WDF!

To cut a long story short i have a site that is running on wp-ecommerce and i have 'out grown' it. So... I am porting it to opencart. I have a module for Opencart that allows for importing CSV files but as you may / may not know there is currently no way to get an export CSV of products out of wordpress e-commerce. My problem is that all of the product images and ifo are stored in seperate rwos in the 'wp_posts' table within the wordpress database. As far as i can tell they are all linked by the primary key which is ID. Here is the query for 'wp_posts':

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` bigint(20) unsigned NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL default 'publish',
  `comment_status` varchar(20) NOT NULL default 'open',
  `ping_status` varchar(20) NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  `post_type` varchar(20) NOT NULL default 'post',
  `post_mime_type` varchar(100) NOT NULL default '',
  `comment_count` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
)

And for example, the product with 'ID' 1037 you have this: (i have removed any identifying information)

INSERT INTO wp_posts VALUES("1037","1","2011-05-06 10:56:52","2011-05-06 10:56:52","product title ","Product page heading","LONG PRODUCT DESC - REMOVED FOR PRIVACY","publish","closed","closed","","product-SEO-friendly-URL","","","2011-10-04 23:23:10","2011-10-04 23:23:10","","0","http://www.removedforprivacy/product-url/","0","wpsc-product","","0");

Then a few of these: (you can see the '1037' appears further along)

INSERT INTO wp_posts VALUES("1079","1","2011-05-06 11:13:56","2011-05-06 11:13:56","","ImageName","","inherit","open","open","","catbag1-3","","","2011-05-06 11:13:56","2011-05-06 11:13:56","","1037","http://www.removedforprivacy/wp-content/uploads/2011/05/ImageName1.jpg","0","attachment","image/jpeg","0");

INSERT INTO wp_posts VALUES("1080","1","2011-05-06 11:14:05","2011-05-06 11:14:05","","ImageName","","inherit","open","open","","imageName","","","2011-05-06 11:14:05","2011-05-06 11:14:05","","1037","http://www.removedforprivacy/wp-content/uploads/2011/05/ImageName2.jpg","0","attachment","image/jpeg","0");

So you can see the data is all over the place. I just want to arrange it so that all the info i need is in one row. All teh description, prices etc AND al of the additional thumnails. That way i can go through and just dump the table and format it as i need as a CSV ready to import in to my new store.

Does that make sense?

Is there a simple way to do this?!

Any help much appreciated!

Thanks.

- G

[Jay Gilford]

Well it's been years since I last used wp-ecommerce but I am pretty adept with OC. To be honest, you are better off not using a CSV importer in my opinion, and using theImport/Export tool by JNeuhoff - Totally free and works with Excel. This comes at the cost of a bigger file, but its so much easier to work with, and configure. If you find that the upload speed takes too long, you can always go the extra step and duplicate your store locally, import on your local machine and then upload the SQL to your store. Of course if that's not an option, the other option would be to use something like Cart2Cart (commercial). You may also want to consider something like this - Commercial again, but at $17 it's hardly a huge spend. Sorry I can't be of more help. Product imports are something I actually steer clear of when it comes to shopping carts due to the headache you are facing right now. Good luck mooving it across!

This post has been edited by Jay Gilford: 29 December 2011 - 03:23 AM

[greyhat]

Hi, thanks for the pointers. I have seen cart 2 cart and that is my safety net! I was aware of the dashboard WP plugin but it only exports order history as opposed to product data.

The export module looks good and i might just give it a shot. If i can do this in a couple of hours myself i would rather do that and learn along the way as opposed to pay cart2cart.

I guess i could try it and see.... i didnt know they offer a free demo, worth a shot i suppose.

Thanks!

p.s. if anybody can tell me a query that will get all my data sorted i am still looking for an answer!

[greyhat]

*sigh* seems that cart2cart stopped supporting wp-ecommerce at version 3.7, or havent updated w/eva. . . Unless their response to my ticket tells me otherwise. I am sure there must be a way to do this!

[greyhat]

Ok so i am going to try and get my head round this SQL query that i need (should have learned this a long time ago!).

Anyway! The two fields that are important to me are `post_parent` and `guid`

All of the main posts have their post ID. And all of the subsequent posts are tied to that via the `post_parent` field, which is blank in the actual posts, as they have no parent.

So... I need some sort of query that does the following:

set post id, select `guid`from post where `post_parent` = post id.

Does that make any sense at all!??

I need it to run through the entire DB, identify which posts have no parent. Then go through and get the `guid` field from all of their 'childern'. And then append those values (the image URL's) to the original parent post row..

Then i can just go through and sort it and turn it in to a CSV.

help!

[Anonimista]

Is it possible for posts to have different number of child posts with guids? How is that going to affect the CSV file?