将值存储在两个MySQL表中

问题描述：

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

我有一个场景，我不知道该怎么做。 p>

我有一个网站，用户可以在其中更新其状态。我允许使用哈希标签，因此可能的用户帖子可能如下： p>

今天去了一次很棒的徒步旅行!! #hiking p>

现在，我打算将帖子存储在一个名为“POSTS”的表中，其结构如下： p>

  post_id  |  user_id | 文字|  date 
  code>  pre> 
 
 现在，当用户提交包含帖子文本的表单时，我运行一个脚本来创建一个数组，以获取用户使用的所有哈希标记术语 然后将它们存储在一个数组中。
然后我可以循环遍历该数组并将标记插入到恰当命名的“TAGS”表中。 现在这个表的结构如下： p> 
 
 
  tag_id |  post_id |  user_id |  tag 
  code>  pre> 
 
 唯一的问题是我在将数据插入“POSTS”表之后才知道帖子的post_id（post_id是 主键并且是自动增量）。
现在，我想我可以从该用户的“POSTS”表中选择最后一行数据（在我插入帖子后），然后依次使用返回的post_id为我的 将标记数据插入“TAGS”表的查询。 这似乎不是最好的方法？ 我的问题是： p> 
 
 
这是最好的解决方案还是有更好的方法来解决这个问题？ p> 
 
 
我是Stack Overflow的新手 所以不要请投票给我。 评论并告诉我我做错了什么，我会学习并提出更好的问题。   p> 
 
 
谢谢 p> 
  div>

答

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).

答

You can get last insterted ID very simply: mysql_insert_id() if you don't use PDO or using function lastInsertId() if you do.

答

This sort of depends on which version of mysql you're using and how you want to organize your code.

Option 1. Do exactly what you've said. PHP would contain the code to manage the database and how data is stored into the database. The only drawback that I see in what you've outlined is if there's an issue with dealing with the hashtags, then possibly you would have a post that is inserted to the database, but the hash part did not successfully complete. For certain applications (like a bank account), this may not be acceptable and this is what database transactions are for.

Option 2. Another way to handle this would be to write a mysql stored procedure that does both the insert and handling the hash tags. The stored procedure could also wrap the whole thing in a transaction so that your database is consistent. Note that this requires a version of mysql that supports stored procedures. The bad side of doing this is that you would have to write in mysql, which is different from PHP.

Both mysql and PHP can handle this application logic/datastore logic. It is a matter of how you want to organize the code. I would prefer keeping the layers distinct. Even if you are to do this in PHP, at least have a separate class that deals with the database and not do anything else. When your code gets bigger, having a separate class or module or namespace that manages these types of code really makes them easier to change and to test.

将值存储在两个MySQL表中

相关推荐