使用php在不同的sql表中导入csv文件的选定列 [英] importing selected columns of csv file in different sql tables using php

问题描述

我的csv档案如下：

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 

我已经能够通过使用php的fgetcsv（）函数来填充patient_info表。 $ sql工作正常，但$ sql2不。

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';
            }

    }

?>

问题：
1）如何在patient_info表中为每个病人插入一个记录，但病人在test_info表中进行的每个实例的多个记录？
2）如何使用patient_info表中的patientID和csv文件中的其他值填充test_info表？
3）我想在每次上传新的csv文件时清空这两个表。我使用TRUNCATE查询来做到这一点，但是由于我定义了PatientID作为外键，TRUNCATE不工作。如何在上传新的csv文件之前清空这两个表？

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?

提前感谢！

推荐答案

为什么要使用PHP？我会在SQL中原生的：

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

1. 定义表格（如果还没有完成）。请注意，您必须在 patient_info 中的相关列定义唯一性约束，您将通过该列从CSV数据中识别匹配的患者：

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)
);

定义要导入原始CSV数据的表格：

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
);

定义 AFTER INSERT < a href =http://dev.mysql.com/doc/en/triggers.html =nofollow>触发器，它将分割导入的数据并执行相关插入到真实表中。您可以使用 INSERT ... ON DUPLICATE KEY UPDATE 以及 LAST_INSERT_ID（） 以获取每个记录的 PatientID ，即使它已经存在：

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 ;

汇入资料：

Import the data:

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

整理：

Tidy up:

DROP TRIGGER trig;
DROP TABLE import;

或者（取决于您打算在将来重用它）：

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

TRUNCATE import;

这篇关于使用php在不同的sql表中导入csv文件的选定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！