[Gllug] Importing data into MySQL

[Mark Preston]

Hi all,
I have been experimenting with remastering Knoppix to produce a CdRom 
for my dental patients. By putting the MySQL database files into RAM it 
is possible to modify data files, and this enables addition and deletion 
of records from web forms whilst running Linux from the CdRom. I plan to 
distribute these CdRoms by leaving them on my Reception desk free for 
anyone who wants to take one. In addition to Knoppix they contain 
information about my dental practice and other dental services in 
South-East Essex. One of the features I hope to put in is to enable 
users to fill in a form with their name and address details on so that 
they can put this information on a floppy disk or USB stick by clicking 
desktop icons. If they subsequently hand such a floppy disk to my 
receptionist she will be able to use this to add/update these details to 
the details held on the computer at the reception.
This should speed up the registration process, and enable computer savvy 
patients to have slightly more control over their contact details, as 
well as having the happy side-effect of introducing some people to Linux.
At the moment the commands I am using are outlined below, together with 
a sample mysqldump file. The problem for me is that I only have a 
rudimentary grasp of MySQL and the commands below will restore the 
details, but at the expense of overwriting any existing details held in 
the database. Ideally I would like to update and install new records 
whilst preserving other existing records on the reception computer.
I have tried various other options such as the -t option with mysqldump 
to transfer the just the data, and looked at mysqlimport and LOAD DATA 
INFILE, but there must be a number of ways of doing this. I'm sure I 
could probably sort out a way, but I'm also sure others have probably 
already sorted out a preferred way of doing this sort of thing.
I wonder if there is anybody on this list who has done something like 
this, and how they achieved it?
Regards,
Mark Preston




#!/bin/bash
# Back up toophpix data to Floppy disk.
mount /mnt/floppy
mysqldump --opt -u root databasename > /mnt/floppy/databasename.sql
umount /mnt/floppy


-- MySQL dump 9.10
--
-- Host: localhost    Database: databasename
-- ------------------------------------------------------
-- Server version    4.0.17-log

--
-- Table structure for table `patients`
--

DROP TABLE IF EXISTS patients;
CREATE TABLE patients (
  nhsno varchar(10) NOT NULL default '',
  lastname varchar(70) NOT NULL default '',
  firstname varchar(70) NOT NULL default '',
  T8 varchar(70) default NULL,
  T3a varchar(10) default NULL,
  T3b varchar(100) default NULL,
  T4 varchar(70) default NULL,
  T5 varchar(70) default NULL,
  T6 varchar(70) default NULL,
  T7 varchar(70) default NULL,
  T9 char(1) default NULL,
  T10 char(1) default NULL,
  age10digits varchar(10) NOT NULL default '',
  PRIMARY KEY  (lastname,firstname,age10digits)
) TYPE=MyISAM;

--
-- Dumping data for table `patients`
--


/*!40000 ALTER TABLE patients DISABLE KEYS */;
LOCK TABLES patients WRITE;
INSERT INTO patients VALUES ('','SMITH','FRED','','512','CRESCENT 
ROAD','BENFLEET','ESSEX','SS7','9JL','X','','1969-11-29'),('','SMITH','SARAH','','512','CRESCENT 
ROAD','BENFLEET','ESSEX','SS7','9JL','','X','1991-08-14');
UNLOCK TABLES;
/*!40000 ALTER TABLE patients ENABLE KEYS */;



#!/bin/bash
# Restore MySQL database details from floppy disk
mysql -u root databasename < /mnt/floppy/databasename.sql
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug