Can I merge 2 database tables using phpmyAdmin? Newbie!

[keveen]

Hello,

 

phpMyAdmin - 2.8.2.4

MySQL - 4.1.14-nt

 

Using phpMyAdmin, I'm trying to merge sections, categories and content tables from one Joomla database into another. No matter how many variations I have tried I cannot find the one that simply merges and APPENDS the additional list of category names etc into the target database. I only manage to lose the original list of categories (Importing and Replacing) which I don't want to do! I'm not used to SQL scripts. Can't find a simple description anywhere.

 

Thanks

[artacus]

INSERT INTO newtable

SELECT NULL AS autoinc_id, field1, field2, field3

FROM database2.oldtable AS ot

LEFT JOIN newtable AS nt ON ot.field1 = nt.field1

WHERE nt.id IS NULL

[keveen]

Thanks - I'll see what all that means tomorrow when I can think again. Wish there were options that work in phpMyAdmin! I've tried every one I can think of!

[worldworld]

I think you can not use phpmyadmin for two databases simultaneously . Else develop a customized admin panel for it... 

[keveen]

got as far as this with errors:

 

INSERT IGNORE INTO jos_sections

SELECT NULL AS autoinc_id, field1, field2, field3

FROM learning.jos_sections AS learning.jos_sections

LEFT JOIN jos_sections AS jos_sections ON learning.jos_sections.field1 = jos_sections.field1

WHERE jos_sections.id IS NULL

 

Working in the Query window of phpmyAdmin the result is:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.jos_sections

LEFT JOIN jos_sections AS jos_sections ON learnin

 

 

The Target database is called writers and the source is learning. the table names are identical - sections

The problem I think is that the sections of the learning database will have the same ID numbers as the one I'm trying to import into.

 

Someone else suggested:

 

INSERT IGNORE INTO jos_sections

SELECT *

FROM learning.jos_sections

 

That one runs but does not insert anything. The result message is:

 

Inserted rows: 0 (Query took 0.0007 sec)SQL query:INSERT IGNORE INTO jos_sections

SELECT *

FROM learning.jos_sections

[artacus]

Close. You'll have to change your field names to what they really are. Mine are place holders for you. Don't try putting .'s in your aliases. And you won't need IGNORE with this because it doesn't select the ones where there is already a match.

INSERT INTO jos_sections
SELECT NULL AS autoinc_id, field1, field2, field3
FROM learning.jos_sections AS ljs
LEFT JOIN jos_sections AS js ON ljs.field1 = js.field1
WHERE js.id IS NULL

[keveen]

So this is my attempt - one more go and then I give up and do it all by cut and paste!

 

INSERT INTO jos_sections

SELECT NULL AS autoinc_id, id, parent_id, section

FROM learning.jos_sections AS learning.jos_sections

LEFT JOIN jos_sections AS jos_sections ON learning.jos_sections.id = jos_sections.id

WHERE jos_sections.id IS NULL

[fenway]

I haven't really looked closely at all of the posts, but you should definitely have an explicit column list for your INSERT... and what errors do you get back?

[artacus]

Yeah, I highly doubt your columns are correct just by looking at them. What are the columns in the table? The null is there so it uses the autoinc value (assuming that it has one) instead of writing the id from the old table which is most assuredly taken already.

[keveen]

INSERT INTO jos_sections

SELECT NULL AS autoinc_id, id, parent_id, section

FROM learning.jos_sections AS learning.jos_sections

LEFT JOIN jos_sections AS jos_sections ON learning.jos_sections.id = jos_sections.id

WHERE jos_sections.id IS NULL

 

The columns are id, parent_id, and sections.

 

Interesting experiment! But I don't think it is going to work as it should (apart from my lack of knowledge) also because the Category and Section id in Joomla is somehow bound together and even when I manage to cut and paste the tables and manually change ids and the incremental value -  categories somehow get linked to the wrong section id.

 

Just out of interest for yourselves this is the table structrue I copied from the sql dump:

 

DROP TABLE IF EXISTS `jos_categories`;

CREATE TABLE IF NOT EXISTS `jos_categories` (

  `id` int(11) NOT NULL auto_increment,

  `parent_id` int(11) NOT NULL default '0',

  `title` varchar(50) NOT NULL default '',

  `name` varchar(255) NOT NULL default '',

  `image` varchar(100) NOT NULL default '',

  `section` varchar(50) NOT NULL default '',

  `image_position` varchar(10) NOT NULL default '',

  `description` text NOT NULL,

  `published` tinyint(1) NOT NULL default '0',

  `checked_out` int(11) unsigned NOT NULL default '0',

  `checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',

  `editor` varchar(50) default NULL,

  `ordering` int(11) NOT NULL default '0',

  `access` tinyint(3) unsigned NOT NULL default '0',

  `count` int(11) NOT NULL default '0',

  `params` text NOT NULL,

  PRIMARY KEY  (`id`),

  KEY `cat_idx` (`section`,`published`,`access`),

  KEY `idx_section` (`section`),

  KEY `idx_access` (`access`),

  KEY `idx_checkout` (`checked_out`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;

 

Typical data is :

 

INSERT INTO `jos_categories` VALUES (13, 0, 'Copywriting services', 'Copywriting services', '', '4', 'left', '<br />\r\n', 1, 0, '0000-00-00 00:00:00', '', 2, 0, 0, '');

INSERT INTO `jos_categories` VALUES (16, 0, 'Document Management Services', 'Document Management Services', '', '6', 'left', '<br />\r\n', 1, 0, '0000-00-00 00:00:00', '', 1, 0, 0, '');

 

Soemtimes it is possible just to work directly on the exported sql file doing Find and Replace actions for example to replace a changed Path.

 

I've started to redo it all manually! Not possible if I had hundreds or pages!

[keveen]

Found a mySQL for Dummies workaround

 

I found that when I tried to import the Content tables from the other website database they were imported but were not showing up when I went into Joomla to see the list of content items that should have been there. The reason is because the section/category ids that come with the content table do not match up with the target list and it seems nigh on impossible to think of a way around that unless there was a way of wiping clean both sets of ids and replacing them with matching sets - quite beyond my expertise!!

 

However, at least phpmySQL lets me view and edit tables so all I have done (doing right now) is to delete the section and category ids that were imported with each Content Record, replacing them with the matching ones I created and then when I go to Joomla Admin and look for content  - magic they show up. So at least that has saved me the chore of cutting and pasting content, about 10x faster I guess. Hope that helps any other dummys out there.

 

The first image of course refers to phpMyAdmin interface and the second one is from Joomla.

 

[attachment deleted by admin]