|
S
smith author
I Have a csv file with data of exam. It has got fields like publication_id, Student_ID, Question1, Question2, Question3,.............. Total number of questions for different test will be different. Now I want to upload this csv file to the mysql database, where after import it should take each question# and its answer along with Publication_id and Student_ID to a separate table. Now i have written a query which is able to save tests with 250 questions. But the test has got less than 250 questions also it will save 250 data and I'm deleting after the insert of this 250 data based on which question number is null on a answer key table. I have the query here for your reference, the problem is everything works well, but the even with a 40 question test of 20 students it takes more than 5 minitus to process Please give a faster way to do the same
$result = ("INSERT IGNORE INTO tbl_test_answer (publication_id, Student_ID, Question#, Answer) SELECT publication_id, Student_ID, '1', 1 FROM upload_test UNION
SELECT publication_id, Student_ID, '2', 2 FROM upload_test UNION
SELECT publication_id, Student_ID, '3', 3 FROM upload_test UNION
SELECT publication_id, Student_ID, '4', 4 FROM upload_test UNION
SELECT publication_id, Student_ID, '5', 5 FROM upload_test UNION
SELECT publication_id, Student_ID, '6', 6 FROM upload_test UNION
SELECT publication_id, Student_ID, '7', 7 FROM upload_test UNION
SELECT publication_id, Student_ID, '8', 8 FROM upload_test UNION
SELECT publication_id, Student_ID, '9', 9 FROM upload_test UNION
SELECT publication_id, Student_ID, '10', 10 FROM upload_test UNION
SELECT publication_id, Student_ID, '11', 11 FROM upload_test UNION
SELECT publication_id, Student_ID, '12', 12 FROM upload_test UNION
SELECT publication_id, Student_ID, '13', 13 FROM upload_test UNION
SELECT publication_id, Student_ID, '14', 14 FROM upload_test UNION
SELECT publication_id, Student_ID, '15', 15 FROM upload_test UNION
SELECT publication_id, Student_ID, '16', 16 .......................... .....................................................................
..................................'246', 246 FROM upload_test UNION
SELECT publication_id, Student_ID, '247', 247 FROM upload_test UNION
SELECT publication_id, Student_ID, '248', 248 FROM upload_test UNION
SELECT publication_id, Student_ID, '249', 249 FROM upload_test UNION
SELECT publication_id, Student_ID, '250', 250 FROM upload_test");
CustomQuery($result);
$sqlabc = ("INSERT IGNORE INTO tbl_result1_view1 (Course_ID,publication_id,Student_ID,Question#,Mark Assigned,Key,Answer,Result_P+N,Result_TP,Result_TL,subject,Topic,id)
SELECT tbl_result1_view.Course_ID,tbl_result1_view.publication_id,tbl_result1_view.Student_ID,tbl_result1_view.Question#,tbl_result1_view.Mark Assigned,tbl_result1_view.Key,tbl_result1_view.Answer,tbl_result1_view.Result_P+N,tbl_result1_view.Result_TP,tbl_result1_view.Result_TL,tbl_result1_view.subject,tbl_result1_view.Topic,tbl_result1_view.id FROM tbl_result1_view"); CustomQuery($sqlabc);
$sql2 = ("DELETE tbl_test_answer FROM tbl_test_answer LEFT JOIN tbl_answer_key ON tbl_test_answer.publication_id = tbl_answer_key.Pub_ID AND tbl_test_answer.Question# = tbl_answer_key.Question# WHERE tbl_answer_key.Question#IS NULL");
CustomQuery($sql2);
$sql3 = ("DELETE upload_test FROM upload_test LEFT JOIN tbl_test_answer ON upload_test.publication_id = tbl_test_answer.publication_id");
CustomQuery($sql3);
Regards Smith
|
|
