Jump to content

Can I merge 2 database tables using phpmyAdmin? Newbie!


keveen

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.