Transferring/Editting a Table

Discussion in 'MySQL' started by Hyphen, Oct 21, 2008.

0
  1. #1
    I am trying to transfer a CRE Loaded customers database table to an osCommerce store, but the table needs a little bit of modification... for example, the address value for an osCommerce store may be in a different location in the table than in the CRE Loaded value. How can I do this change easily?
     
    Hyphen, Oct 21, 2008 IP
  2. Krnl

    Krnl Peon

    Messages:
    60
    Likes Received:
    3
    Best Answers:
    1
    Trophy Points:
    0
    #2
    location within a table doesn't matter as long as you use --complete-inserts (or -c) when you dump the data which will cause mysqldump to dump the column names as well as the values as follows:

    
INSERT INTO tablename (columnA, columnB, ColumnC) VALUES(dataA, dataB, dataC);
    Code (markup):
     
    Krnl, Oct 21, 2008 IP
  3. Hyphen

    Hyphen Well-Known Member

    Messages:
    464
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    110
    #3
    Here's what I mean:

    (14, 0, '', 'First', 'Last', 'birth_date 00:00:00', 'email@address.com', 16, 'phone_number', '', 'hashed_pw', '1', '0.00', 'false', NULL, 0, '0', '', '', '', '0', ''),


    Is there a way that I can rearrange the SQL dump of this table, and say... put the email address before the birthdate, or manipulate it in some way like that? That is the format of the Customers SQL dump for a osCommerce store. I am trying to convert an osCommerce SQL dump to a Zen Cart store.


    Here is an example of both of the formats for the customers table SQL dump for osCommerce and then for Zen Cart, so you can see the differences and how I am trying to convert osCommerce to Zen Cart: (I have received permission from both of my examples to use their information, with important parts editted out):

    osCommerce
    (14, 0, '', 'Alicia', 'Theroux', '1983-10-17 00:00:00', 'theroux@sbcglobal.net', 16, '926-958-\r\n\r\n5689', '', '60b498ffeef61948x7c4r1909efdbb5b:90', '1', '0.00', 'false', NULL, 0, '0', '', '', '', '0', '')

    Zen Cart
    (5, 'f', 'Linda', 'Clark', '1954-01-20 00:00:00', 'mamaw325@msn.com', '', 5, '349-665-6275', '', '54fdd2951073d4c4edb418c1a3ee0dee:1f', '0', 0, 'TEXT', 0, '', '', 0, 0)
     
    Hyphen, Oct 22, 2008 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    I use Navicat for all my database management. What you can do with it, and other good MySQL GUI's, is rearrange your current table to make it match your destination table. You can change column names, data types, column order, add new columns, indexes, etc.. until your existing table is the exact same format as the destination table. Then when you dump the table it will go perfectly into the other table.

    They have a free personal version, and a free 30 day trial on their commercial version. http://www.navicat.com/
     
    jestep, Oct 22, 2008 IP