使用另一个表中的一列作为我当前表MySQL的参考

Question

I have 2 tables. First table (user) containing 3 columns:

| user_id | username | password |
---------------------------------
|  253    | John     | 12345678 |
|  180    | Katrina  | jellyfis |

Second table (quiz score) containing 3 columns:

| score   |   rank   | answered |
---------------------------------
|  6520   |   56     |   51     |
|  9846   |   2      |   102    |

In this particular scenario, John has a score of 6520 points while Katrina has a score of 9846. Given that the user_id is in one table whilst score is in another table, is there a specific way I can establish a connection between these 2 columns?

Say for example, I've created a simple profile display in PHP where details about the user (score, rank and answer) are being shown. I want to display the score of that user while both of those columns are in different tables.

Hope I make myself clear enough. For those who want the reason I'm doing this; it's because I'm working on a project that involves using 2 different tables based on the user's profile and the user's score.

Answer 1

Add user_id column to the second table.

You will have to add FOREIGN KEY (user_id) REFERENCES firstTableName(user_id) in your create table statement. Please refer manual for more details.

Your second table will look like

| user_id | score   |   rank   | answered |
-------------------------------------------
|  253    |  6520   |   56     |   51     |
|  180    |  9846   |   2      |   102    |

You can then join the two tables on user id column to get the details.