Moving db from localhost to Powweb

[ubern00b]

Hey all... just learned that I can't connect to my spanking new MySQL database through Dreamweaver. :(

Other threads have suggested developing the database locally and then uploading it. Can anyone point me to the E-Z instructions?

I'm on a Mac running Tiger. I've got PHP and MySQL running locally, but if I rebuild my database on localhost (say, via Terminal), how do I export it and then upload it to Powweb? I thought I'd ask here before starting to pull my hair out.

Thanks in advance.

[MarkDeNyse]

Hi ubern00b,

Someting I do for a client is to use the "LOAD DATA LOCAL INFILE" command to load a text file containing all of the data for the MySQL database (the local data is exported out of a FileMaker database). I FTP up the text file, then use LOAD DATA LOCAL INFILE to do the import. A few steps, but not too bad, and I've got it automated so I don't worry about it much.

See this thread about a recent change to PHP on PowWeb that temporarily hosed me:

http://forum.powweb.com/showthread.php?p=396654#post396654

Here's a snippet of some PHP code to do the import once the file is on the server. Don't shoot me if it doesn't compile as it's a reduced from a much larger function I use. Hopefully this will give you some ideas.

// ************************************************** ************************************************** *****************************
function DoImport($inFileName, $inHost, $inDBName, $inUser, $inPass, $inTable)
{
if (file_exists($inFileName) && (filesize($inFileName) != 0))
{
# Connect to the database
$result = mysql_connect($inHost, $inUser, $inPass, false, 128/*CLIENT_LOCAL_FILES*/) or die(mysql_errno()." Invalid mysql_connect");

# Delete the current content of the table
$result = mysql_db_query($inDBName,"DELETE FROM $inTable") or die(mysql_errno()." DELETE failure");

# Optimize the current table (recover empty space)
$result = mysql_db_query($inDBName,"OPTIMIZE TABLE $inTable") or die(mysql_errno()." OPTIMIZE TABLE failure");

# Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file
$result = mysql_db_query($inDBName." LOAD DATA LOCAL INFILE '$inFileName' INTO TABLE $inTable FIELDS TERMINATED BY ',' ENCLOSED BY '\"'")
or die(mysql_errno()." LOAD DATA LOCAL INFILE failure");

mysql_free_result($result);
}

return;
}

[ubern00b]

Thanks very much Mark. I'd not heard of LOAD DATA LOCAL INFILE, but the code you posted makes perfect sense. I'll give it a try.

I noted that Powweb's phpmyadmin installation has an import funtion, but the only option available under "format" is SQL. I download the freeware SQL4X Manager, but it will only export to CSV, tab-delimited, XML, HTML, and Excel. Is this an avenue worth pursuing?

[MarkDeNyse]

Just poking around I noticed that if you drill down to one of your tables and *then* click Import, you'll see options for importing a file from CSV, CSV using LOAD DATA, or SQL.

The only option at the top level of your database is SQL.

So, this option may work for you after all...