Jason Dimberg wrote: > I am working on an application where data will be collected on laptops > and then uploaded to a central database once the laptop is able to > connect to the network after being in the field. I was initially > thinking of using MS Access as a front end with linked tables through > MySQL ODBC. I am now considering running WAMP on each machine with a > web interface because there will be no interoperability issues if > MySQL is the db server on both ends, but I am 1.) looking for any > recommendations for the laptop interface (MS Access/WAMP or whatever > other options might be available) and 2.) want to know what is the > actual command for updating a table across two servers (this is NOT > replication, but merely updating new data to an existing table). > > Data transfered will include binary objects and possibly GIS data. > > For example, Laptop 1 might have 10 rows of data from todays > activities that need to be added to the main Server. Laptop 2 might > have 30 rows of data that need to be added to the main Server. > Neither laptop needs to have the data from the other, but the Server > will contain data from both Laptops at the end of the day. The Server > will then offer the data through a web interface.
I'll start with the syncing records. You can script this in PHP or Perl very relatively. Your main issue is going to be dealing with primary keys. Here's how I'd do it. Each table has a number of fields for tracking changes, eg: - inserted - edited - deleted These would be boolean fields that your application sets when inserting / editing / deleting data. When the laptops return home to sync with the server, your script would select all the flagged records and take appropriate action. For example: - inserts: If you have related records ( ie primary key / foreign key relationships ), you'll have to do some shuffling of data around, eg insert record into server, fetch created primary key ( auto_increment ), then select 'child' relationship stuff from the laptop, and insert this into the server, using the newly created primary key, and NOT the primary key from the laptop. If you don't have relationships set up, you won't have this trouble. - edits: Just update the entire table with fresh data from the client - deleted: Delete :) After you've updated all data, you should probably dump everything ( drop tables, maybe even drop database ), and then import fresh data from a mysldump ( after you've imported data from the other laptop as well ). For the interface, let me push my own wheelbarrow for a second ... I've made a nifty set of libraries to help you create rich database front-ends ( using Gtk2 ) that you might be able to make use of instead of doing your stuff in PHP. They're written in Perl, but if you're doing simple data entry, you will hardly have to do any code at all. You create your GUI in Glade, create DBI database handle, and then use my libraries to connect your database to your GUI. Inserting, deleting, and applying records are all one-liners, and everything else is taken care of :) Everything is open-source and cross-platform, and there is even a module for creating PDF reports. Trust me - it's a LOT less work ( and trouble ) than doing it in PHP. If you're interested, have a look at: http://entropy.homelinux.org/axis_not_evil And also please send feature requests, bug reports, and contributions :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
