how can i import some articles from one site into another?

sthmtc · 2008-04-20 18:58:49

i have an outdated version of a txp site that contains ~150 articles in a specific section that i’d like to import into the current install. of course without overwriting the articles that are already present… :)

in phpmyadmin, i selected the textpattern table, setup a search (“section = thenameofthesection”) and then exported all these articles. now, when i try to import this phpmyadmin says:

#1062 - Duplicate entry '2' for key 1

what would be the best way to do this? also, i have no idea how IDs are being handled if the import/export is done that way… i’m pretty much cueless when it comes to these things, so any help is greatly appreciated.

Last edited by sthmtc (2008-04-20 18:59:07)

uli · 2008-04-22 21:15:42

You just need to increase your existing article IDs by the old articles count’s value using the following sql command: UPDATE textpattern SET ID = ID + 150
Backup before! ;)

uli · 2008-04-22 21:37:08

Crap! Increase your existing article IDs by the highest article ID to import (In german: um den höchsten ID-Wert der zu importierenden Artikel. SO! ;)

Gocom · 2008-04-22 22:29:28

You just need to increase your existing article IDs by the old articles count’s value using the following sql command: UPDATE textpattern SET ID = ID + 150

Isn’t the ID field auto_increment? I mean, it is. So, you can use it’s automatic increasing thing, by not using the ID in the insert. Then the primary value is filled without any conflicts ;)

uli · 2008-04-22 22:40:13

Sven, make a backup and test locally. I’ve tried the sql command on a very small site with just some articles, no comments etc. After posting these I saw there is a parent_id at least for comments. Think this has to be changed accordingly, if there are any.

Jukka, just for my poor brain’s sake, would you please shed a little light on this:

[…] by not using the ID in the insert.

Gocom · 2008-04-22 23:15:25

uli sure I can. In example (out from my head so fields don’t match):

insert into `textpattern`
(`id`, `title`, `body`, `body_html`) values
(NULL,'title', 'body', 'body_html') [etc];

And then it just counts the id byitself as it is auto increasing value ;)

Last edited by Gocom (2008-04-22 23:17:00)

uli · 2008-04-22 23:38:03

Yup, Gocom, works! Nice! :)
Do you have a regexp for the IDs to replace with NULL? All the lines begin with INSERT INTO `textpattern` VALUES (<ID_HERE>,'

Last edited by uli (2008-04-22 23:42:14)

sthmtc · 2008-04-23 08:03:15

thanks guys… i already solved this myself though :) i did it as gocom suggested… edited the sql

from:

INSERT INTO `textpattern` (`ID`, `Posted`, `AuthorID`, `LastMod`, ...

to

INSERT INTO `textpattern` (`Posted`, `AuthorID`, `LastMod`, ...

and deleted the ID values accordingly:

(281, '2007-08-18 12:25:15', 'admin', '2007-12-06 19:37:43', ...

to

('2007-08-18 12:25:15', 'admin', '2007-12-06 19:37:43', ...

that was it. oh and uli, why do you need a regexp for this? you can do that easily in any editor, no?

uli · 2008-04-23 08:26:29

SQL editor, yes, but I thought of a text editor for doing this. I’m not too familiar with phpMyAdmin and rather trust what I can see ;)

But one warning to future readers: relations between at least comments and articles will be lost for either method proposed here. So backup your data, test on a local installation, fasten all safety belts you can get!

sthmtc · 2008-04-23 08:34:04

uli wrote:

SQL editor, yes, but I thought of a text editor for doing this. I’m not too familiar with phpMyAdmin and rather trust what I can see ;)

i did it in a text editor, textmate to be precise. at least on os x you can press ALT and then select all the lines and edit them all at once. easy peasy :)

Last edited by sthmtc (2008-04-23 08:34:28)

Textpattern CMS

Textpattern CMS support forum

#1 2008-04-20 18:58:49

how can i import some articles from one site into another?

#2 2008-04-22 21:15:42

Re: how can i import some articles from one site into another?

#3 2008-04-22 21:37:08

Re: how can i import some articles from one site into another?

#4 2008-04-22 22:29:28

Re: how can i import some articles from one site into another?

#5 2008-04-22 22:40:13

Re: how can i import some articles from one site into another?

#6 2008-04-22 23:15:25

Re: how can i import some articles from one site into another?

#7 2008-04-22 23:38:03

Re: how can i import some articles from one site into another?

#8 2008-04-23 08:03:15

Re: how can i import some articles from one site into another?

#9 2008-04-23 08:26:29

Re: how can i import some articles from one site into another?

#10 2008-04-23 08:34:04

Re: how can i import some articles from one site into another?

Board footer