importing selected columns of csv file in different sql tables using php

Question

My csv file looks like this:

Name | DateOfBirth| Gender| Occupation| DateOfTest | FieldNumber | Eye | ThresholdValue

Fred, 12/08/68,M, carpenter, 12/02/06, 1, R, 1234
Susan, 3/09/72, F, hairstylist, 12/04/07, 1, R, 1234
Fred, 12/08/68,M, carpenter, 12/02/06, 1, L, 1234
Susan, 3/09/72, F, hairstylist, 12/04/07, 1, L, 1234
Fred, 12/08/68,M, carpenter, 12/02/08, 1, R, 1234
Susan, 3/09/72, F, hairstylist, 12/04/08, 1, R, 1234
Fred, 12/08/68,M, carpenter, 12/02/08, 1, L, 1234
Susan, 3/09/72, F, hairstylist, 12/04/08, 1, L, 1234

Each patient will have two rows(for left and right eye) for each instance of test undertaken by them and they can undertake multiple tests. I have to put this information in a database in a way that it can be extracted from different dimensions like analyse data from all females, or analyse data from all carpenters etc. I thought of following two tables:

Patient_info
PatientID(auto-increment primary key), name, dob, gender, occupation 

test_info
testID(auto-increment primary key), patientID(foreign key from patient_info), dot, fieldnumber, eye, thresholdvalue 

I have been able to populate patient_info table by using fgetcsv() function of php. $sql works fine but $sql2 does not.

<?php



    ini_set("auto_detect_line_endings", true);
    include "connection.php";

    $deleterecords = "TRUNCATE TABLE patient_info";
    mysql_query($deleterecords);

    $deleterecords = "TRUNCATE TABLE test_info";
    mysql_query($deleterecords);    

    if(isset($_POST['submit']))
    {

        $file = $_FILES['file']['tmp_name'];

        $handle = fopen($file,"r");

        while(($fileop = fgetcsv($handle, 1000, ",")) !== false)
        {
            $name = $fileop[0];
            $dateofbirth = $fileop[1];
            $gender = $fileop[2];
            $occupation = $fileop[3];
            $dateoftest = $fileop[4];
            $field = $fileop[5];
            $eye = $fileop[6];
            $thresholdvalues = $fileop[7];
            $sql = mysql_query("INSERT INTO patient_info (Name, DateOfBirth, Gender, Occupation) VALUES ('$name','$dateofbirth','$gender','$occupation')" );
            $sql2 = mysql_query("INSERT INTO test_info (DateOfTest, FieldNumber, Eye, ThresholdValues) VALUES   ('$dateoftest','$field','$eye','$thresholdvalues')" );

        }
            fclose($handle);
            if($sql2) { echo 'test data successfully uploaded';}

            if($sql) 
            {
                echo 'Data uploaded successfully';
                header("Location: http://localhost/lei/test.com/proper/upload_page.php");
                exit();
            }
            else
            {
                echo 'Upload Failed';
            }

    }

?>

Questions: 1) How can I insert just one record for each patient in patient_info table, but multiple records for each instance of test undertaken by the patient in test_info table?? 2) How should I populate test_info table using patientID from patient_info table and other values from csv file? 3) I want to empty both the tables every time a new csv file is uploaded. I was using TRUNCATE query to do that, but since I defined PatientID as foreign key, TRUNCATE does not work. How can I empty both the tables before uploading a new csv file?

Thanks in advance !

Answer 1

Why use PHP for this at all? I'd do it all natively within SQL:

1. Define your tables (if not already done). Note that you must define a uniqueness constraint over the relevant columns in patient_info by which you will identify matching patients from your CSV data:

   CREATE TABLE patient_info (
     PatientID      SERIAL,
     Name           VARCHAR(255),
     DateOfBirth    DATE,
     Gender         ENUM('M','F'),
     Occupation     VARCHAR(255),
     UNIQUE(Name, DateOfBirth, Gender, Occupation)
   );
   
   CREATE TABLE test_info (
     TestID         SERIAL,
     PatientID      BIGINT UNSIGNED,
     DateOfTest     DATE,
     FieldNumber    INT UNSIGNED,
     Eye            ENUM('R','L'),
     ThresholdValue INT UNSIGNED,
     FOREIGN KEY (PatientID) REFERENCES patient_info (PatientID)
   );
   

2. Define a table into which you will import the raw CSV data:

   CREATE TABLE import (
     Name           VARCHAR(255),
     DateOfBirth    DATE,
     Gender         ENUM('M','F'),
     Occupation     VARCHAR(255),
     DateOfTest     DATE,
     FieldNumber    INT UNSIGNED,
     Eye            ENUM('R','L'),
     ThresholdValue INT UNSIGNED
   );
   

3. Define an AFTER INSERT trigger that will split the imported data and perform the relevant insertions into your "real" tables. You can use INSERT ... ON DUPLICATE KEY UPDATE together with LAST_INSERT_ID() to obtain the PatientID of each record, even if it already exists:

   DELIMITER //
   CREATE TRIGGER trig AFTER INSERT ON import FOR EACH ROW BEGIN
     INSERT INTO patient_info (
       Name,
       DateOfBirth,
       Gender,
       Occupation
     ) VALUES (
       NEW.Name,
       NEW.DateOfBirth,
       NEW.Gender,
       NEW.Occupation
     ) ON DUPLICATE KEY UPDATE
       PatientID = LAST_INSERT_ID(PatientID)
     ;
   
     INSERT INTO test_info (
       PatientID,
       DateOfTest,
       FieldNumber,
       Eye,
       ThresholdValue
     ) VALUES (
       LAST_INSERT_ID(),
       NEW.DateOfTest,
       NEW.FieldNumber,
       NEW.Eye,
       NEW.ThresholdValue
     );
   END//
   DELIMITER ;
   

4. Import the data:

   LOAD DATA INFILE '/path/to/file.csv'
     INTO TABLE import
     FIELDS TERMINATED BY ','
   ;
   

5. Tidy up:

   DROP TRIGGER trig;
   DROP TABLE import;
   

   Or alternatively (depending on whether you intend to reuse it in the future):

   TRUNCATE import;