如何将一行MySQL数据库表链接到另一个表中的另一行

Question

I know it makes little sense... and i'm new to using MySQL... What i'm trying to do here is, link one tables row to another tables row... for an example there are two tables.. one table is for user registration and same table is used for login as well... and the next table is for user posts.. like status updates and all...

here is how i want it...

user_log_info:-
id ( primary )
firstname
lastname
username
email
password

posts:-
id ( primary )
userposts
posted_by
date_post

so as you can see, i want the user_log_info tables username to be automatically copied to posts posted_by row... And i have no idea how i can archive this...

Answer 1

You haven't given nearly enough information to give a full answer, but I'll do my best with what you've given.

Tables

+-----------------+ +-----------------+
| users_log_info  | | posts           |
+-----------------+ +-----------------+
| int ID (primary)| | int ID (primary)|
+-----------------+ | int posted_by   |
                    +-----------------+

(I left off fields that are irrelevant to what you seem to want to do, I'm just simplifying it)

posted_by is an unofficial foreign key, or referencing the primary key of another table.

To insert, what you can do is along the lines of this:

INSERT INTO posts(...., posted_by) VALUES (...., user.ID)

Where .... is referencing all of your other information to insert

Then, to find information on someone who posted something:

SELECT * FROM users_log_info WHERE ID = Post.posted_by

Or if you want to find all posts by a user:

SELECT * FROM posts WHERE posted_by = user.ID

So, if Bob, who is User ID 3 wants to post "Hi", you might be able to do:

INSERT INTO posts(content, posted_by) VALUES('Hi', bob.ID)

And then when you are outputting the post you might do this:

post = (however you choose the post to put on the page)
userPosted = SELECT * FROM users_log_info WHERE ID = post.posted_by

print post.content + " posted by: " userPosted.Name

Essentially, the field "posted_by" is, to "posts" an arbitrary number, but you know that it links to, or references, a user. It does that by referencing "ID", which is the primary key of users_log_info, so that when you want to get information from users_log_info, is all you need to do is select the entry which has the ID that corresponds to "posted_by". I do recommend naming it something like posterID, however, for easier identification.