如何在数据库中连接两个表？

Question

i have a database with 2 tables:

Table user:

[user_id (pk),username,password,email]

and

Table posts:

[post_id (pk),username_posted,post]

I have created a login-registration system with the first table, and I want, when my user logs in, to post a text by pressing a button. In the second table, I want to store the post_id, the username of the person who posted, and of course the post he made. How can i connect these two tables?

Answer 1

In a relational database, rows in two tables are "connected" by storing common values in columns.

user:
user_id  username  email
-------  --------  -----
      5  jack      
      6  jim       
      7  johnny               

post:
post_id  post_username  post 
-------  -------------  -------
     11  jack           hi 
     12  jack           whassup
     14  johnny         hey

The rows in these two tables are related ("connected") to each other, by the values stored in the username column and the post_username column.

(We could store the value of the user_id column from the user table in the post table, to the same effect. But we'll work with the schema you have.)

As an example of how to add a new row to the post table and "connect" that to an existing row in the user table:

 INSERT INTO post (post_id, username, post)
 VALUES (15, 'jim', 'not much');