SELECT multiple adjacent columns without writing all the column names

MySQL database
#1

I have a table allstudentsAnswers20EAPCW This table stores the answers a student sends. So, apart from student info it has columns like Answer1, Answer2, Answer3 … Answer75

There are not always 75 questions, so sometimes some columns in some rows will be NULL

Instead of specifically writing the names of all the columns, is there a MySQL shortcut to so I may write something like:

SELECT Answer1 .... Answer75 FROM allstudentsAnswers20EAPCW WHERE studentnr = 1234567890 AND weeknr = 'Week10_T' AND Answer1 ... Answer75 != '';

#2

Consecutively named columns is a big red flag your DB design is wrong. You need to learn about Database Normalization.

1 Like
#3

I would do something like the following

id - primary key
student_id - int
question - either the question itself or the number of the question
answer
date

#4

Thanks for the replies!
@benanamen as this is only my school homework (and lately, because of the virus, online class) database, I am not too worried about being really strict.

@Strider64 Hope your blood pressure thing is working out! I can recommend exercise and diet and green tea. A South African friend, after he came to China, started drinking green tea and stopped taking blood pressure pills!

Well, I found a quick way to get all the column names: just click the SELECT button in the phpMyAdmin SQL tab, then just delete the things I don’t want! I can do what I want easily in Python, but I was hoping to do it directly in MySQL.

What I really want to do is: find every incorrect student answer, so I can quickly tell them what they got wrong, they can try again. Maybe I should start another question.

Trouble is, the answers are in the table markingAnswers20EAPCW. There is a column questionnr and the answers go in, (sorry benanamen again) columns like Week1, Week2 … Week 19 So all the correct answers are in 1 column.

I can easily get the correct answers:

SELECT questionnr, Week10_T FROM markingAnswers20EAPCW WHERE Week10_T != ''

When the student clicks “send answers” PHP gets the correct answers, compares them with the student’s answers and marks them. Each time a student sends answers, PHP inserts a new row in allstudentsAnswers20EAPCW with all the answers, (sorry again benanamen) in columns like Answer1, … Answer75

So all the student’s answers are in 1 row!

Now I want to compare, not for all students, just occasionally, the correct answers and the student’s answers and filter out the wrong answers.

I just realized, I could save all wrong answers as they are marked in an array and write them to a wrong_answers_table.

However I would like to develop some MySQL query to do get the wrong answers when I want. I’m working on it.

In Python it is a lot easier, I can just get lists of tuples and compare them!

#5

Normalizing your data is about storing it in a way such that you can easily insert new data, update existing data, find and sort data, produce reports from the data, and write simple code and queries.

When your data isn’t normalized, every operation you try to perform on the data is slow, more complicated, or even impossible.

What you have shown us in your threads is that you have converted a paper recordkeeping activity to use a computer, but you are not really using the computer as a tool to do the work for you. You are still creating tables and more tables that have columns upon columns and you are spending all your time typing just to make it look like the old paper records, but because the data isn’t stored in a usable form in the database, you cannot use the database engine as it was meant to be, to write simple queries to produce useful reports.

1 Like
#6

OK, I do not profess to being any kind of expert and would be grateful for your advice.

Tell me please, what do you suggest I name the columns??

#7

Multiple tables. One for the question, one for the answers. You link the answer by question Id. Then it doesn’t matter if there are two possible answers or hundreds.

Following that you can even have an input type for the answer to differentiate between radio, checkbox, or text fields.

1 Like
#8

Have you looked at any LMS systems? Seems like you are trying to recreate the premise.

#9

Here is a partial normalized Schema.

Capture
Capture862×405 48.2 KB

#10

Well thanks for the tips, I’ll try to rearrange my db in the summer break, try to make it more efficient!

Sponsor our Newsletter | Privacy Policy | Terms of Service