0

I have a registration application for registering 6 courses. The program will check the repeated register users first. Those users that needed to be checked comes from last year. If users registered for one course, they cannot register for any other courses.

---firstroundregisted table structure---

id|englishname  |idcard |
1 |CHAN SIU MING|A1234  |
2 |TAK HO KANG  |Z4123  |

---Contain >5000 last year registered users records for the 6 courses---

The sql in PHP as below.

SELECT * FROM firstroundregisted 
WHERE englishname='".$_POST['studentname']."' 
AND idcard='".$_POST['idcard']."' 
LIMIT 1;

Is this sql the fastest way to find out if the user registered or not?

Others:

I asked a question here : How to increase Max_used_connections? Some gentlemen suggested me to optimizing the sql query so I start a new question to focus on optimizing. Thank you all for your help.

3
  • The sql in PHP as below. This SQL is not related to the problem. Is this sql the fastest way to find out if the user registered or not? For definite user and course - yes. But it cannot help in duplicates searching. If users registered for one course, they cannot register for any other courses. Does the user is identified uniquely by englishname column? What column identifies the course? – Akina Nov 13 '20 at 4:50
  • Do you have INDEX(englishname, idcard) ? – Rick James Nov 13 '20 at 5:04
  • @Akina @Rick James The user is identified uniquely by englishname AND idcard. Don't have INDEX. Thanks. – Muffin Nov 13 '20 at 8:04
0

Is this sql the fastest way to find out if the user registered or not?

It's close enough.

If the table is indexed on englishname (and, preferably, idcard as well) then yes. Indeed, if those two uniquely identify each row, then they really ought to be the Primary Key of the table.

If the table is not indexed, then it really ought to be.

However ...

Never use "select *" in Production code.

Never use raw, User-supplied values directly in dynamically generated SQL.

Doing so is one of the quickest ways to get your table and, possibly, your whole database well-and-truly fouled up. The problem is known as an SQL Injection Attack and your code is wide open to one.

Obligatory XKCD Reference: Little Bobby Tables.

Your best plan would be to move to using Prepared/Parameterised Statements but, failing that, you must "escape" the values from the User so that they form valid and safe SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.