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]

Reply via email to