Synch with mySQL?

[chrisleake]

Is there/could there be a way to make DM interface with mySQL? I was reading how you can do that with Microsoft Access so that you're working with the Access interface, but all the data is actually linked to a mySQL database and stored there. Is there any way to do the same type of thing with DM?

[jmuehleisen]

Chris,

The tables in the Donor Manager are Visual FoxPro data tables, and they are fully compatable with mySQL, except for logical fields (with FoxPro stores as true and false and mySQL stores as 1 and 0).

Tell me more what you have in mind when you say "synch."

Now, I can make an export of DM data that would result in SQL insert statements that would work with mySQL.

[chrisleake]

Well, like I always say, I don't know a whole lot about this stuff. Maybe I'd just better explain in layman's terms what we want to do:

We have a signup on our website for different types of email updates--there are three different lists people can sign up for. Currently, submitted forms are emailed to me and then I manually enter them into DM and mark user-defined checkboxes to indicate which list(s) a person wants to be on.

We send our email updates through GroupMail, so we would like to go ahead and set up automatic signup with double opt in, one-click unsubscribe links in our emails, bounce handling, and all that stuff. The problem is this--I know GroupMail can connect directly to a mySQL database (if we upgrade to the Pro edition), but unless there's something I don't know, we have no way to tap it into DM. We could make an Excel spreadsheet our master database, but when you export from DM you lose fields, right? So if we export out to Excel and then import from Excel a database that has been updated by GroupMail, I think we'll lose data.

Since GroupMail can link to mySQL, we were wondering if DM could also without losing info. That way, we could do all the actual data storage in a mySQL database, GroupMail would automatically be updating it, and DM could be our actual interface for accessing and manually manipulating the data.

Does that make sense? If there's something I'm missing, I'm all ears.

Thanks!

[jmuehleisen]

Chris,

I think I follow you.

What you are looking for is a way to store data so that Group Mail (a wonderful program, by the way ... I use the free version) can do your automatic updates.

Now, I checked on Infacta's website (http://www.infacta.com) and this is what it says about the databases that it supports:

Work with existing data and information in practically any format. Importing data from existing applications or databases couldn't be easier. GroupMail can import practically any file type, text, CSV, binary, databases, and email address books. At present any database that supports ODBC or OLEDB (SQL Server, Oracle, MySQL, Access, FoxPro, Excel, etc.) can be imported or linked directly into GroupMail Business Edition.

Now, the Donor Manager uses FoxPro databases, so, this may be the solution for you. At least it is worth taking a look at. You may not need the MySQL thing at all.

Back to you ...

[chrisleake]

You're right...we had overlooked that.

So here's an update: We had to upgrade to the business version of GroupMail in order to link to databases, which is something we had been thinking of for a while anyway. We made the upgrade, and were able to link to our DM database. What this does not allow us to do, unfortunately, is to handle all our opt-in opt-out mailing list stuff automatically. All GroupMail can do with FoxPro is add or delete records, but it can't change records. Because we have multiple lists that people subscribe to, we would need GroupMail to do more than just add or delete a record. It can only do that stuff with sql, apparently. It's still a nice upgrade, though, to be able to link directly to our current DM database, instead of having to always manually export our mailing lists, tweak the configuration in Excel, and then import them as a new group in GroupMail.

GroupMail is a really handy program, as you say. You were actually the one who got us onto it, as I remember. I think you had it recommended in your resource area.

[jmuehleisen]

One thing to keep in mind.

MySQL (and most other database systems) usually have one field that contains the "primary" key value ... a value that is unique for each record. That is how they know how to update particular records.

If GroupMail is adding a record to the Donor Manager, it can't simply append a record to a given table (putting in name, address, etc.). It also must add the "primary key" (which in the Donor Manager is called "link" ... 10 characters).

If Group Mail adds some records, but doesn't put a unique primary key in there, then you will have multiple donor manager records with the same (blank) primary key.

Ohh, ohh. This is a recipe for data problems! There are times that the donor manager looks at the primary key for a given partner, then uses that primary key to update data in other tables. If you have multiple partners with the same (blank) primary key, then who knows which of the records in the other tables will be properly updated.

Hmmm. See if there is some way to tell Group Mail which key is the "Primary Key" in the Donor Manager database.

[ordinary]

Hi John,

I was reading this post about mysql, and I'd like to be able to port the data from donormanager to a mysql database on my website, so that my wife can look up addresses when I'm not home (I travel a lot).

Is this do-able?

[jmuehleisen]

It is indeed doable. But, it may not be too easy.

The donor manager can export to a CSV file, or an Excel file, and the MyPhPAdmin tool can handle these (if things are mapped properly) and insert them into a MySQL table.

But, perhaps an easier way is to simply FTP your Excel file to the website, and run the query right from that as a data source, or just a link.

Or, how about Google Spreadsheets? They are Excel compatible and free.