将值存储在两个MySQL表中

Question

I have a scenario in which I am not sure about what to do.

I have a website where a user can update their status. I am allowing the use of hash tags so a possible user post might look like:

Went for a great hike today!! #hiking

Now, I intend to store the post in a table appropriately named "POSTS" which is structured like this:

post_id | user_id | text | date

Now, when a user submits the form which holds the post text I run a script to create an array to get all of the hash tag terms the user used and then store them in an array. So then I can loop through that array and insert the tags into the aptly named "TAGS" table. Now the structure of this table is this:

tag_id | post_id | user_id | tag

The only problem with this is that I do not know the post_id of the post until after I insert the data into the "POSTS" table (post_id is the primary key and is auto increment). Now, I was thinking I could just SELECT the last row of data from the "POSTS" table for that user (after I insert the post), and then in turn use the returned post_id for my query that inserts the tag data into the "TAGS" table. This seems like not the best way? My question is:

Is this the best solution or is there a better way to go about this scenario?

I am brand new to Stack Overflow, so don't please down vote me. Comment and tell me what I am doing wrong and I will learn and ask better questions.

Thanks

Answer 1

Have a new column in both tables - unique_id - which holds a string you generate in code before querying the database. That way you have an id to tie posts and tags together before submission. I use this method all the time for similar applications.

Only issue is uniqueness, but there a variety of ways to generate unique ids (I normally use a mixture of timestamps and hashing).