Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2006
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    newb q about comma-delimited dump

    hey all

    i've literally only used mysql a handful of times and, thankfully, i haven't encountered any problems so far. however, i've just been told that i've got about a week to move off my current server and find alternative hosting. that's fine, but i've never transfered a database before.

    i recently implemented a custom comments system that now has about 1200 entries in it. i assumed that the best way would be to an sql dump of the whole table, but after i did so i noticed that it was comma-delimited, and with phpMyAdmin you don't get a choice of delimiters. the thing is that, since it's all comments entered by users, quite a lot of the text entries have comments in them, e.g. "hey, how's it going? me, al, and simon are coming over tomorrow."

    will this totally screw up the importing of the sql dump to the database on my new host? is there a better way to approach this?

    any help gratefully received.
    you are invited | http://www.indextwo.net

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    with proper csv output, you have field delimiters (the commas) and something to enclose the data (typically "s), so an example like yours would become:
    1,Fred,"Fred, Barney and wilma went on holiday"

    (sometimes all fields will be quoted, even when it's not neccesary)

    if you are able to get an sql dump though, that would be the easiest.
    if you have command-line access:
    mysqldump --user=username --database=dbname --password > dump_file.sql

  • #3
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,088
    Thanks
    2
    Thanked 23 Times in 23 Posts
    Do you have cPanel on your current server? If so, try to find a new host that also has cPanel. Your new host can easily migrate your website from your current server to the new one with it. Works like a charm, too. Also, no worries about your database(s). Using cPanel's utility takes care of everything, in a matter of minutes.

    Edit: And another thing. I seem to recall that if your new web host has cPanel, the old one doesn't necessarily have to in order to use the cPanel utility I mentioned to transfer your site to the new server. Be sure to ask about that.
    Last edited by vinyl-junkie; 09-02-2006 at 04:16 AM.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #4
    New Coder
    Join Date
    Jan 2006
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hey guys, thanks for the replies - unfortunately my old host is on ensim (bleurgh) and my new host uses some kind of custom control panel.

    i did a mysql dump a few days ago, which looks like a whole lot of sql queries to re-insert the data into another database; it starts by creating the tables, then each data entry looks like this:

    INSERT INTO `comments` VALUES ('2006-04-11 17:31:31', '', '', 'http://', 'This blog is the most beautiful thing I\'ve ever seen... kudos, my friend, kudos.', 1144187765, '195.93.21.67');

    as you can see, each field value is contained within apostrophes rather than quotes, and the text comment has a couple of commas in it.

    if this is going to cause issues when i try and push it in to a new database, what's the best way for me to dump the data? if it helps, i'm dumping from phpmyadmin 2.5.1 and my new host has phpmyadmin 2.7.0

    thanks for all your help.
    you are invited | http://www.indextwo.net

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    that's absolutely fine- it shouldn't be surprising that output from mysql will be valid input, it'd be a fairly serious problem if it wasn't.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •