I know this crops up quite a bit but I'm struggling to work out the optimal method to get data from FMP to MySQL on a regular basis.

As a bookshop, we maintain all our inventory data in Filemaker. We are looking to set up an ecommerce website which would run on MySQL and with either PHP or RubyOnRails as the server-side technology.

Now, rather than having to maintain 2 versions of the database in FM and MySQL, the preferred solution is to move data from the FMP database to MySQL on a, say, weekly, basis.

Has anyone done this sort of thing successfully and care to talk about it? I am aware of FMP Migrator and fmSQL Synch but am nor sure how robust these are.

I am also aware of a product by Datawise(?) that uses http and XML to talk between FMP and MySQL.

Is there anything I might be missing here. The situation seems fraught with technical difficulties and I cannot work out what the optimal and most elegant/efficient solution might be to this problem.

Many thanks

Richard

Howdy, b! I, too, am interested in hearing about "the optimal method", but I thought I'd just mention that I've just been using phpMyAdmin to export MySQL data into .csv comma separated text and then importing into FileMaker.

It's not quite as easy as FM export / FM import but it's do-able, and I've gone both MySQL to FM and FM to MySQL using this method. I haven't had to do this regularly yet so I always forget what I did before and sometimes I've used Excel as an intermediary to "wash" the data, but it works.

BTW, for anyone who's wondering, phpMyAdmin is a popular open source web-based utility for managing MySQL databases. There are also OS-specific utilities out there, too. Some Mac OS X users (like Marc Liyanage) use CocoaMySQL.

http://www.phpmyadmin.net/

http://cocoamysql.sourceforge.net/

--ST

I'm also currently trying to work out a FMP >> MySQL solution. I agree with Steve, exporting a .csv in filemaker and then loading it in phpMyAdmin is very painless...BUT... I want to automate the process as much as possible, so I am experimenting with other ideas. I just wrote a FMP script that assembles a php program into a global field. The php program dumps the entire MySQL table, recreates it, and adds all rows (records). From a coding perspective this doesn't seem like the most efficient option, but it's all I can think of for right now, and most importantly IT WORKS. Now, here's my dilemma, when I export the contents of that global field to a text file (.php file) it is in UNICODE format, and it needs to be ANSI. My webserver can't properly interperate the unicode file, and without manually saving it as ANSI before uploading and executing it, I can't fully automate the process.

For the uploading I was going to save command line FTP commands in a batch file, which would be triggered from the original FMP script as well as finally executing and deleting the php file.

I apologize if I sound like a dummy, or am giving bad advice. I am new to the world of php/mysql, and just thought I would share what I know. Curt

Here is a method that is used by one of my clients. It just uses PHP and an exported tab delimited file from FM.

Here is the Form for the Upload:

Products:

Here are extracts of PHP in the "import.php" script:

$products = move_uploaded_file($_FILES["products"]["tmp_name"], 'products.txt');

$file = 'products.txt';

$fp = fopen($file, "r");

$txt = fread($fp, filesize($file));

$end = fclose($fp);

$line = explode("n", $txt);

$clearout = mysql_query("DELETE FROM prod_items");

$item_lines = count($line);

for ($i = 0; $i < $item_lines; $i++)

{

$cells = explode("t", $line[$i]);

if (strlen($cells[0]))

{

$id = trim($cells[0]);

$name = trim($cells[1]);

$desc = trim($cells[2]);

$man = trim($cells[3]);

$part = trim($cells[4]);

$price = trim($cells[5]);

$sql = "INSERT INTO prod_items (item_id, name, description, manufacturer, partnum, price) VALUES ($id . "', '" . addslashes($name) . "', '" . addslashes($desc) . "', '$man', '$part', '$price')";

};

};

Hope this is of interest.

All the best.

Garry

I've finally figured it all out today. I have successfully setup an EFFORTLESS filemakerpro >> MySQL solution. I am very excited about this. So far I only have it going for one table, eventually this will be setup for all tables that apply. What happens is this, a filemaker pro script triggers the entire process:

-show all records (optional)

-sort records (optional)

-export records (must be a tab seperated text file)

-open url (local batch file ftpconnect.bat)

-open url (remote php file www...import.php)

the ftpconnect.bat uses windows command line ftp commands to connect to my webserver's ftp, erase the existing (previous) TAB file, upload the current TAB file (non-public folder).

import.php deletes the MySQL table, re-creates it, and then uses the LOAD DATA LOCAL INFILE command to import the TAB file into the MySQL table.

and that's it. My ultimate goal is to setup a customer fullfillment system for a printing company, which will not only display real time data but accept incoming requests (quotes, re-orders, etc.). The incoming requests will remain in the php/mysql world. I will no doubt be developing this solution as time goes on.

Curt

Well done Curt

Garry

hi.. i desparately need this solution but am completely new to filemaker.. could u please send me a sample of this technique.. will be greatly appreciated..

OK, so this could be the most stupid question on this forum, but why can you not script Filemaker using an EXECUTE SQL statement to insert data into your mySQL DB ?

Never tried it but that was the way I was hoping it would work. Feel free to set me straight.

Hi Richard;

I've got extensive experience with FM and sql. I've written several databases in FileMaker that massage SQL databases (better than the SQL versions did I might add )

It's not a complicated task, but it does require a little knowledge of SQL to make it all work.

If you haven't yet figured it out, please feel free to email me with a more detailed description of what you need to do and I can forward you some sample solutions to achieve your results.

Regards

STeve

>> [email protected] <<

I am also very interested in this topic- I have recreated a mysql database in FM, using all the same table and field names and hope to build the db locally and then export it back online and eventually vice-versa.