Мой файл 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 Каждый пациент будет иметь два ряда (для левого и правого глаза) для каждого экземпляра теста, проведенного ими, и они могут провести несколько тестов. Я должен помещать эту информацию в базу данных таким образом, что ее можно извлечь из разных измерений, например, анализировать данные у всех женщин или анализировать данные всех плотников и т. Д. Я думал о следующих двух таблицах:
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
Мне удалось заполнить таблицу patient_info с помощью функции fgetcsv () php. $ sql работает нормально, но $ sql2 этого не делает.
<?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) Как мне заполнить таблицу test_info с помощью patientID из таблицы patient_info и других значений из файла csv? 3) Я хочу очистить обе таблицы каждый раз, когда загружается новый файл csv. Я использовал запрос TRUNCATE для этого, но поскольку я определил PatientID как внешний ключ, TRUNCATE не работает. Как я могу удалить обе таблицы перед загрузкой нового файла csv?
Заранее спасибо !
Зачем использовать PHP для этого? Я бы сделал все это изначально в SQL:
Определите свои таблицы (если они еще не сделаны). Обратите внимание, что вы должны определить ограничение уникальности над соответствующими столбцами в patient_info помощью которого вы будете идентифицировать совпадающих пациентов с данными CSV:
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:
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 который будет разделять импортированные данные и выполнять соответствующие вставки в ваши «реальные» таблицы. Вы можете использовать INSERT ... ON DUPLICATE KEY UPDATE вместе с LAST_INSERT_ID() чтобы получить PatientID каждой записи, даже если он уже существует:
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 ;
Импортируйте данные:
LOAD DATA INFILE '/path/to/file.csv' INTO TABLE import FIELDS TERMINATED BY ',' ;
Убирать:
DROP TRIGGER trig; DROP TABLE import;
Или альтернативно (в зависимости от того, собираетесь ли вы повторно использовать его в будущем):
TRUNCATE import;