外键SQL与用户表的关系

Question

I am trying to learn about Foreign keys and being able to associated data from one table to another. So I have a users table and a cards table.

The user table has 'user_id', 'username' & 'email'. The card table has 'card_id' & 'name'.

what I am looking to do is associate a card form the card table to the users. so for example if the card table has Card1 inside and user1 wants that card (or more) assocaited with them how would I use the foreign key to do this.

Here is how I am selecting and showing my users at the moment:

    <?php

        $sql = "SELECT user_id, username, email_address FROM user";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {

            // output data of each row
            while($row = $result->fetch_assoc()) {
            echo  
                $row["username"] . "<br>",
                $row["email_address"] . "<br><br>";
            }

        } else {
            echo "0 Members";
        }

        $conn->close();
    ?>

I understand inside of the user table I will need to another another cell 'card_id' but not sure on which data type this should be?

Answer 1

Based on your question and on your following comment:

so the cards table is reffering to playing cards. A user can collect the cards so they become a part of the users deck essentially. I want a way for the card to be associated to the user.

I believe you can achieve that in 2 ways, depends on further scenarios in your game/script.

Scenario 1: In case one card can be belong to only one user. Add a new field to the cards table.

`user_id` INT(11)

When a user collect a card, just update that field so it will store the user's id.

Scenario 2: In case one card can be belong to many users. Create a new table called users_cards:

users_card
- id (INT)
- user_id (INT)
- cart_id (INT)

When a user collect a card, insert a new record to that table with both the card's id and the user's id.