Best way to select the highest vaue for score

General PHP Help
#1

I made a table allstudentsAnswers20BE. When a student clicks “Submit” on the homework page, PHP immediately checks the student’s answers against the answers in the table correctAnswers20BE, according to the column weeknr. (Each week there are, of course, different answers.) PHP increments $score for a correct answer.

Each student’s answers and their name, number, week number and score are written to the table allstudentsAnswers20BE.

I want to extract the columns: name, number, score for any given week number.

For example:

SELECT name, number, score FROM allstudentsAnswers20BE WHERE weeknr = 'Week9';

will get me what I want, but:

Within the time limit for each homework, a student may send the homework more than 1 time. Therefore, a student may have more than 1 score. (Some send 4 or 5 times)

I want to get only the row with the highest score. (Not to be too harsh.)

Should I read everything into an array and then somehow eliminate the lower scores for each student number?

Or can this be done directly within the SELECT query??

#2

Well, Pedroski, that is a loaded question. First, why would you let a student send 5 answers?
I can see where you might let them change their answers up to the cutoff time, but, why would they
every need to have 5 different answers? I am not clear on the logic of this.

Also, when you create something like this, the table would have a datetime field that would be locked into the the date of entry for the student’s answers. And, it they sent a second one, or in other words, updated they answers, it would place them into the same row of data and update the data given.

But, if the table does allow for multiple entries into the same week number, you can use MAX() to locate that one. More like:
SELECT name, number, MAX(score) FROM allstudentsAnswers20BE WHERE weeknr = 'Week9';
This would depend on your table’s design and how the data is entered. You might need to use a GROUP-BY to group all of the students and then select the max one from their own entries.

Hope that made sense…

1 Like
#3

Thanks for your reply!

Looks great. I may need to put COUNT 1 in there somewhere though. Some students send twice, just in case. (If the sending works, they see a “Success page”, with the time, their name and number and a message “Your answers have been saved”, but some still send twice!). Then I have 2 identical rows (apart from the index number).

The logic: a student can open the HW page, enter or click answers and send. The student can then do this again if he or she wishes.

I do not have a timestamp in allstudentsAnswers20BE, but I can easily add it. That is a good a good idea!

However, wouldn’t I then need an UPDATE query to change existing values?

For that, I need to check if $studentnr and $weeknr are present in the same row, then update, I believe. (Correct me if I’m wrong, please.)

At the moment, I just use INSERT (this is for Week9):

$mysql = 'INSERT INTO allstudentsAnswers20BE(studiName, studiNumber, weeknr, score, Answer1, Answer2, Answer3, Answer4, Answer5, Answer6, Answer7, Answer8, 
				Answer9, Answer10, Answer11, Answer12, Answer13, Answer14, Answer15, Answer16, Answer17, Answer18, Answer19, Answer20, Answer21, Answer22, Answer23, Answer24, Answer25) 
				VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

Also, I have had cases where a student sends, say, all the correct answers, then sends again where some answers are wrong.

In Python, that is easy to fix: make dictionary where the student number is the key and the score is the value, then mark all students’ answers twice, but the second time around, only change the score if it is higher.

Not sure how to do that in PHP.

On the whole, I think it may be simpler to take all submissions and then try to filter out the ones with the highest score for each student and week.

#4

Just heading to bed, but, saw your note… A few further comments should be mentioned.

First, with something that is important, you usually make the user log in. Assuming you are doing this or planning on doing this online, the student would need a student ID and a password to be able to log in.
If that is the case, you already have an “id” field for the student in a user table. Normally, you would save their id from the user’s table in the answers table. In this way, you know the student. So, if they enter answers online, you just check if they already did that for the week in question. Then, you can UPDATE the results instead of INSERTing the second record. That way, there is only one and the last one entered is saved. Then, there would never be duplicates.

If you must allow dups, you can save the datetime stamp of when they entered the answers. Then, just take the latest date from that week and you would get the last ones entered.

I feel this is just your decision on how you want it designed. But, you normally would give exact instructions to the students and in my opinion just tell them the last one they send is the one that counts.

Anyway, just some more thoughts on it for you to think about. Goodnight, my electronic friend…

1 Like
#5

Oh dear, hope I haven’t disturbed your bed rest! That was never my intention! It’s only 16:39 here in China!

Also, this is not urgent. I can always export the result of my SELECT command and get rid of the rows I don’t want with Python, that is much easier for me than with PHP or MySQL.

BUT, if you have a good idea, please let me know!

Last semester, because of the virus, we had to do our classes online. I have a PDO login system that works nicely. The login was their attendance, so I knew who attended. (They kept dropping off Zoom)

I don’t use a login system for HW, because, well, it is only HW.

My colleague sometimes asks me to host an online exam for him, so I set up the PDO register, login system for him.

The only thing I do for HW is check the student number against the course list of student numbers. If the student number is not in the course list, the student/user is sent back to the HW page. If the student number is in the list, the HW is written to the table allstudentsAnswers20BE.

I will try what you suggested, maybe I can make it work for me.

If not, I find, with computing, there is always more than 1 way to skin a cat!

#6

Oh, I do not sleep much these days and I enjoy my time helping others here. Always fun to meet new people and assist as much as I am able. Very nice to assist someone across the world !

Well, python is okay, but, since it is more of a “scripted” language it does not work well online.
( At least in my humble opinion! ) It is very easy to use the datetime field to sort the answers and
groupt them by student. You can test this using your current table with something like this.
( I did not test this as I do not have a test database set up for this, but, you can test it… )

SELECT * FROM allstudentsAnswers20BE GROUP BY studiNumber ORDER BY answerDate DESC

This will sort it by the datetime field which I called answerDate and then will group it by the student number with will need to be unique to the student. Because it is grouped by student, it will only show the first answer date which will be the latest date only. Therefore, if the student sends five (5) answers at different times, only the last one, the latest, would be pulled from the database.

Just a thought for you to experiment with! Good luck!

Sponsor our Newsletter | Privacy Policy | Terms of Service