Reply to Re: Merging two sql databases

Your name:

Reply:


Posted by J.O. Aho on 01/16/07 22:04

Andy wrote:
> This is a table of database I bought:
>
> recipes
> Field Type Null Default
> id bigint(20) No
> title varchar(255) No
> category varchar(100) No
> incredients text No
> procedures text No
> imagepath varchar(255) No
> notes varchar(255) No
> status varchar(15) No Active
>
> And this are fields I want to transfer to my recepies database I had:
>
> Tables I bought:
>
> recipes
> Field Type Null Default
> id bigint(20) No
> title varchar(255) No
> category varchar(100) No
> incredients text No
> procedures text No

--- this is your own recipes table ---
recipes
Field Type Null Default
recipeid int(10) No 0
title varchar(255) Yes NULL
recipe text Yes NULL
userid int(10) Yes NULL
rating int(10) Yes 0
categoryid int(10) Yes NULL
date varchar(20) No
story text Yes NULL
hits int(10) Yes 0
status char(1) Yes W

--- eof ---

Okey, I assume you already have data in the, which makes you already will have
items with a 'recipeid', this makes trouble as the bought ones will also have
'id', then you have the trouble and do you have auto_increment for your
'recipeid', are your current 'categoryid' the same values as the bought
'category'.
Is 'recipe'='incredients'?
Is 'story'='procedures'?

I assume the bought database came in a sql file, then the easist would really
you used the find&replace function in a text editor (it has to manage to save
pure text files), replace the 'recipes' to say 'bought_recipes', as this will
make things a lot easier. Remove text that drops and creates database, should
be at the top of the file. ALWAYS MAKE A COPY OF THE ORIGINAL FILE BEFORE YOU
START EDITING.

You will need to inject that file to the mysql server, write this on one line,
and change the "name_of_your_database" to the name of the database where you
have your site data. change "mysql_user" to the username you have to login to
the database (see there is no space between -u and the name) and in the same
manner change the "password" to the real password (notice there is no space
between -p and the password) and last change the "modified_bought.sql" to the
name of the file you save the modifications I wrote before.

mysql name_of_your_database -umysql_user -ppassword < modified_bought.sql


When you have done that, you need to login into the mysql server (you can use
myphpadmin if you have that or the mysql command), select the database where
the two tables are located and do the following:

INSERT INTO recipes(`title`, `categoryid`, `recipe`, `story`,`date`) SELECT
`title`, `category`, `incredients`, `procedures`, CURDATE() as `date` FROM
bought_recipes;

We have assumed that recipeid used auto_increment, which is the smart thing to
do, this will lead to new id numbers for the bought recipes, if your database
don't use auto_increment, then you need to use:

INSERT INTO recipes(`recipeid`, `title`, `categoryid`, `recipe`,
`story`,`date`) SELECT `id`, `title`, `category`, `incredients`, `procedures`,
CURDATE() as `date` FROM bought_recipes;

Now you will most likely get the trouble that you can have two or more recipes
with the same id number, which is really bad.

--

//Aho

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация