Re: UPDATE from one server to another
Daniel da Veiga wrote: On 6/6/06, Jason Dimberg [EMAIL PROTECTED] 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. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Don't use ACCESS to deal with MySQL Data, you'll probably meet inconsistencies, along with bugs and problems with field types, besides, MS sucks... To sync the laptop with the actual database, you can use a Web Interface, or simply upload data via ftp or any other protocol (check for security) and write a script to add this data to MySQL. Are you sure you're not better served using the filesystem to store the data and simply indexing names, sizes, maybe a hash for security reasons (md5 or whatever) and timestamps so you can use the database to quickly search or organize files but offer them via HTTP or FTP? A simple app can get info about the file, upload it to the server and add a row to MySQL with the info and the location of the file in the filesystem. You would get something like: filesystem: /home/ftp/file1.bin mysql: name: file1.bin location: /home/ftp size: 1024 bytes hash: 78687hhg89686578h786 uploaded: 06/06/2006 14:30 from: laptop1 A simple web interface written in PHP or whatever can search this database, filter data, sort stuff and simply offer links to the ftp site... Just a suggestion... Daniel, Thanks for the on-topic response! Binary data will be a small portion of the data collected in the field (it will be checklists and data entry, mostly), but I am glad to hear what you said about Access. I have some fears about integrating it with MySQL and I think I am going to set up MySQL on each laptop to avoid those issues altogether and work in an environment I am familiar with (PHP/MySQL). Thanks for your suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE from one server to another
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. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fun with Dates and Incentives.
I think doing a sort by date with limit 10 should get you the first ten. I believe the now() function uses the server time, so no need to do date/time calcs really. Good luck, Jason Brian Menke wrote: I'm hoping for some general advice on an approach for the following scenario: I have a customer who wants to put an incentive program in place for students taking learning modules and then completing tests. The concept is simple. Award the first 10 people who complete a test with a score of 100%... that type of thing. Students are allowed to take test more than once. Track each time the student takes the test and show the latest score ect. You get the idea. I have the database tables and relationships already all set up for the tests, but it's the tracking of the dates and times that I don't have and it got me thinking. I need to track down to the day/hour/minute level. Okay, that should be easy (I think). I'm going to need to do a lot of date/time calculations. Would it be best just to have a default of CURRENT_TIMESTAMP set for a TIMESTAMP field? Or, is their something else I should be using? I have limited experience having to munge and crunch date/time info and I want to make sure I have the flexibility to do what I need in the future. The next gotcha I thought up is what about different time zones. Obviously without this consideration, people on the East coast would have an unfair 3 hour advantage over people on the west coast. I guess I can have a time zone field in my student table so I could derive the time difference. Any suggestions on a good time zone approach? Here are my two tables as they stand now. I'm wondering if these are set up in a way to allow me to do all this date time crunching I'm going to need to do in the future? Any suggestions are greatly appreciated :-) CREATE TABLE `students` ( `store_id` varchar(6) NOT NULL, `email` varchar(64) NOT NULL, `fname` varchar(32) NOT NULL, `lname` varchar(32) NOT NULL, `role` char(2) NOT NULL default '5', `password` varchar(8) NOT NULL, `phone` varchar(24) default NULL, `reg_date` date default NULL, PRIMARY KEY (`email`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `completed_modules` ( `module_id` char(2) NOT NULL default '', `email` varchar(64) NOT NULL, `score` int(2) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Brian Menke Visual Matter, Inc 1445 Foxworthy Ave., Suite 50-215 San Jose, CA 95118 408 375 9969 San Jose ~ Los Angeles www.visualmatter.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AlterTable Structure Across Multiple DBs
I have about 25 databases with the same structure and occasionally need to update the table structure. For example, I recently found a mistake in a field that was of type SET and needed to be VARCHAR. I will now need to edit each table. Is there an easy method to alter table structure across multiple dbs as opposed to editing each one individually? In retrospect I should have combined them into one db and may consider doing that. All dbs start with 'pm_' and have identically named tables MySQL 5.0.18 Windows 2003 Thank you, -- *Jason Dimberg* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting rows in 2 tables
Rhino wrote: - Original Message - From: Merlin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 09, 2005 1:31 PM Subject: deleting rows in 2 tables Hi there, I am wondering if there is a possiblity to delete rows in more than one table with one query. At the moment I am doing this: I do have at the moment 3 querys!: # get table1_id SELECT table1_id from ... WHERE ID = ... # delete dependent entry DELETE FROM $DB.$T5 WHERE ID = '$data[table1_id]' LIMIT 1 # delete main entry DELETE FROM $DB.$T4 WHERE ID = '$data[id]' LIMIT 1 Is there a better solution as this? Have you considered using foreign keys with DELETE CASCADE rules? Basically, if you define the dependent tables as children of the first (parent) table via the appropriate foreign keys and primary keys, and if you establish DELETE CASCADE on the foreign keys, you could be sure that whenever you deleted a row in the parent table, all dependent rows in all dependent tables are deleted automatically. In other words, you set up the rules in your table definitions and then simply delete what you like from the parent table; the dependent rows will be deleted for you without you having to write any explicit DELETE statements for the dependent tables. For example, if your parent table was EMPLOYEE and your dependent tables were EMPLOYEE_CARS and EMPLOYEE_CLAIMS, you'd have the definitions set up roughly like this: create table EMPLOYEE empno integer not null, ... primary key(empno)); create table EMPLOYEE_CARS empno integer not null, licence_plate_state char(2) not null, licence_plate_number char(8) not null, ... primary key(empno, licence_plate_state, licence_plate_number), foreign key(empno) references EMPLOYEE on delete cascade)); create table EMPLOYEE_CLAIMS empno integer not null, claim_number integer not null,, ... primary key(empno, claim_number), foreign key(empno) references EMPLOYEE on delete cascade)); Then, once the tables are defined, all your program needs to do is delete a given employee and the employee's cars and claims will be deleted automatically, via the DELETE CASCADE rules in the definitions of the dependent tables: delete from EMPLOYEE where empno = 10; --- If you want to use this approach, I believe you have to be using InnoDB tables; I don't think the other engines support foreign keys. By the way, this whole concept is called Referential Integrity and is very useful for ensuring that your tables are consistent with one another. Rhino Excellent info Rhino. This will help me greatly with my application! * Jason * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
