Looking to convert one DB to another

Discussion in 'MySQL' started by scoopy82, Oct 12, 2008.

0
  1. #1
    I would like to import a database I have had laying around from an older site into my newer website... but it is from a different script and the tables are different. I am sure its possible... but where do I begin ?

    I am looking to convert:
    INSERT INTO `cheats` (`id`, `hits`, `votes`, `rating`, `title`, `cheats`, `console`, `letter`)
VALUES (5, 1, 1, '5.0', 'Advance GTA', 'TEXT HERE', 'GameBoy Adv.', 'A')
    Code (markup):
    to
    INSERT INTO `dd_items` (`ItemID`, `ItemTitle`, `ItemCategory`, `ItemSubcategory`, `ItemText`, `ItemImage`, `Contributor`, `DateAdded`, `ItemType`, `ItemStatus`) 
VALUES (8,'Extra items',1,4,'TEXT HERE','','n/a',1169965010,'T','approved')
    Code (markup):
    I have them both loaded in cpanel and will be using phpMyAdmin.

    I know I will have to use some blank fields that I won't need (IE: '' ) and that I can probably rename some of the fields to reflect its name in the new database. The newer database also has the 'console' entry in a separate table.

    Where do I start with this project ?
     
    scoopy82, Oct 12, 2008 IP
  2. dcr226uk

    dcr226uk Peon

    Messages:
    174
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #2
    First, copy the MySQL insert statement into wordpad (the first statement above)

    Then use Find/Replace on things like 'id' to be replaced with 'ItemID' (try to include the apostrophes as this will help with any potential mistakes)

    Once you are done, you should have a modified MySQL statement to run against a test table (maybe change dd_items for dd_items_test). Run against test table, verify the entries - away you go.

    Have fun!
     
    dcr226uk, Oct 13, 2008 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    Try navicat. You can import/export from text, excel, and other flat file types and directly from one DB to another.

    On the import side, you can specify exactly what fields you want the data to go into so the original table doesn't need to match the destination.

    http://www.navicat.com/
     
    jestep, Oct 13, 2008 IP
  4. scoopy82

    scoopy82 Active Member

    Messages:
    838
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    70
    #4
    I have managed to get the basic info I needed playing around with phpMyAdmin and a few MySQL commands I found. This was better to change things with as wordpad would of done some real damage with its find/replace (for example: every "gameboy" replacement... "gameboy adv."... would of become "1 adv." instead)

    So now I can import this info... BUT HERE IS my next problem:

    I am finding the main text uses HTML tags... and the PHP code the site uses wants to strip it all out leaving me with 9,000 blanks that I just imported. I also noticed the previous entries use the pipe thing (|) to start each text entry.

    Even if I add this | to the new database... any edits made thru the site's admin makes the entry disappear.

    What is this pipe thing doing and is there anything I can do about this ? (I tried removing the "strip_tags" query out of the code... but it still happens.)
     
    scoopy82, Oct 15, 2008 IP