1

I'm running a website aimed for medical students, I have a profile and points for every student in my database, So when they participate in an activity I must add X amount of points for all the participants, and I do this manually using an ajax form, How can I run a MySQL query to give X amount of points for each student in a large text input (textarea)? So I can insert the points all at once which is much easier and faster, Here is an example:

21910431,1,Participant
22010431,3,Subleader
21810531,6,Headleader
(Student ID),(points),(role in activity)

How can I insert this into the database all at once? every student in users table is identified through the (Student ID).

Please help with a reliable way, Thank you!!

Dr.Jamal
  • 33
  • 5
  • Where are you pulling this data from? Seems like a CSV. – El_Vanja Feb 02 '21 at 17:38
  • It would seem likely that a `trigger` could be used to do this automatically rather than you having to manually update records – Professor Abronsius Feb 02 '21 at 17:38
  • A small JavaScript program could read and parse that text area and call the existing AJAX endpoint several times. The problem will be typing all those student numbers correctly. – Tangentially Perpendicular Feb 02 '21 at 17:55
  • @TangentiallyPerpendicular any further explanation? and concerning the numbers, they are correct almost always – Dr.Jamal Feb 02 '21 at 19:32
  • @Dr.Jamal You say you have an AJAX form. Create a new form based on that original, with a TEXTAREA and a submit button. Add an event handler to intercept the Submit process, read the text area line by line, and create AJAX requests with the same parameters as you already have. This will use your existing system repeatedly. It won't be the fastest way to insert lines in a database, but it will be much faster than manually entering each one. If the data is coming from a file you could just load the file with Javascript and do away with the TEXTAREA. – Tangentially Perpendicular Feb 02 '21 at 21:19
  • If you really want one SQL statement to enter all the data then that's possible too, but involves rewriting the server code as well as the client code. – Tangentially Perpendicular Feb 02 '21 at 21:20
  • If the data is already in a file, use `LOAD DATA`. – Rick James Feb 10 '21 at 22:46

0 Answers0