View Full Version : newb q about comma-delimited dump

09-01-2006, 11:05 PM
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.

09-01-2006, 11:21 PM
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

09-02-2006, 03:38 AM
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.

09-02-2006, 01:52 PM
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, '');

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.

09-02-2006, 04:57 PM
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.