Login page check username from two different tables

Question

I have a site which has two type of users, doctor and patient. I store the information about these two types of users in two different tables. When a user is logging in, I want check if the login name submitted by the user exists in the first table (ex: patient) or in the second table (ex: doctor). How I can do this?

Below is part of my code containing the SELECT statement that I have written:

$query = 
"SELECT username, password 
 FROM patient 
 WHERE username='$username' AND password='$password'";

I'm currently SELECTing from the patient table, but I want to know how to SELECT both from the patient table and the doctor table.

Can you help me please?

Use `UNION` to select from both tables. – Barmar Dec 27 '14 at 18:02 — Barmar, Dec 27 '14 at 18:02

score 5 · Accepted Answer · edited May 23 '17 at 12:17

You can do this using MySQL's UNION() function.

$query = "SELECT username, password 
    FROM table_1 
    WHERE username='$username' AND password='$password' 
    UNION 
    SELECT username, password 
    FROM table_2 
    WHERE username='$username' AND password='$password'";

Nota: String variables must be wrapped in quotes.

I must point out though, that this method is open to SQL injection and I hope you are not storing passwords in plain text.

If you are storing passwords in plain text, it is highly discouraged and is prone to your site being compromised.

It is recommended to use CRYPT_BLOWFISH or PHP 5.5's password_hash() function.
For PHP < 5.5 use the password_hash() compatibility pack.

Plus, in regards to SQL injection, use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

score 0 · Answer 2 · edited Dec 27 '14 at 19:28

0

Does your DBMS support UNION statements?

$query = "SELECT username,password FROM patient WHERE username='$username' AND password='$password' UNION SELECT username,password FROM othertable WHERE username='$username' AND password='$password";

edited Dec 27 '14 at 19:28

Funk Forty Niner

74,450
15
68
141

answered Dec 27 '14 at 18:01

dfsg76

504
1
6
22

I get this error (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''123' at line 1)... Do you know why? – Waaaaat Dec 27 '14 at 18:06
`AND password='$password"` <= missing quote `AND password='$password'"` @NickBourlai You should have figured that. – Funk Forty Niner Dec 27 '14 at 18:10
Although it was accepted and OP did in fact notice my comment about the missing quote, have downvoted this answer because it doesn't reflect the change that needs to be made, as stated in my comment above. What can I say, I'm being honest about it. – Funk Forty Niner Dec 27 '14 at 18:16
@NickBourlai You shouldn't have accepted the answer since the person who answered didn't make the proper change in regards to the missing quote, something "I" had to point out to you. Had I not pointed that out, well... the question remains. You should have given a comment to that effect after my comment. – Funk Forty Niner Dec 27 '14 at 18:18
@Fred-ii- I accepted because in the comment below you fixed the error, i cant accept as an answer your comment if you write the answer I will accept your answer to be fair with you – Waaaaat Dec 27 '14 at 18:21
@Fred-ii- I un-accept the answer in order to be able to edit the given answer but I cant edit it, maybe the user which gives this answer see your comments and edit his answer to be corrected 100%. I will wait until he edit his answer and then I will accept it. PS: Thanks for your comments and your help. – Waaaaat Dec 27 '14 at 18:33
@niklakis You're welcome. If that person hasn't edited it to be 100% correct within a 24 hour period, then I might edit it myself. It shouldn't be up to me to do that, although I do have that privilege to do so. Plus, I could put in an answer later on, **if** the person didn't edit accordingly. There has been an edit made already, but not the one with the fixed missing quote. It's best that a person know the error of their ways and should give code that does not produce errors. – Funk Forty Niner Dec 27 '14 at 18:36
1

@Fred-ii- i edited it and I wrote that the answer is correct because of you – Waaaaat Dec 27 '14 at 18:40
@niklakis I saw that. I myself did approve the edit, but the person may choose to refuse the edit, or other editors may refuse it also. Only then, should you accept it. I know I'm being hard-nosed about it, but that's how the Stack system works; I didn't come up with the system, I'm only adhering to it. – Funk Forty Niner Dec 27 '14 at 18:46
@Fred-ii-ok thank you for the information. Check it in a few hours if you want and if the answer is still not accepted then write your answer. – Waaaaat Dec 27 '14 at 18:48
@niklakis Update: The edit was performed, but your comment about me was removed by Josh Pinter, who made the edit. I'm tempted to do a rollback to the original answer and post my own answer. – Funk Forty Niner Dec 27 '14 at 19:22
1

@Fred-ii- write your answer and i will accept yours – Waaaaat Dec 27 '14 at 19:23

Login page check username from two different tables

2 Answers2