0

In my database there are 3 tables:

Posts:
-id
-title
-body
-date

Tags:
-id
-title

Posts_Tags:
-id
-post_id
-tag_id

When an user insert a post, my php code does 2 queries:

  1. Insert the post in the Posts table
  2. Insert a row for every tags associated with that post in the Posts_Tags table.

Is this the correct approach ?

Is there a way to simplify or to do 1 single query ?

xRobot
  • 25,579
  • 69
  • 184
  • 304
  • The basic logic sounds correct. Can you show us some code as well? – Jens Wegar May 16 '13 at 13:09
  • basicly no, this question would be useful; [SQL Server: Is it possible to insert into two tables at the same time?](http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time) – rcpayan May 16 '13 at 13:13
  • the code is not still available :( – xRobot May 16 '13 at 13:37

1 Answers1

2

Is correct approach provided you do it into a transaction (atomic operation); that is, if you insert a post and there's an error into tags insertion (or in tag association insertion), also post will not be inserted

Update

After xRobot comment, I'd updated my answer with this

Community
  • 1
  • 1
DonCallisto
  • 29,419
  • 9
  • 72
  • 100
  • Transactions are indeed the most robust method for doing this. Any other method runs the risk of leaving your database in an inconsistent state where one insert worked but the other failed for some reason. A transaction means that if either insert fails you can rollback the database and give an error message – Anigel May 16 '13 at 13:19
  • So do I have to use the transactions ? – xRobot May 16 '13 at 13:23
  • I am using myisam tables, so how can I do ? – xRobot May 16 '13 at 13:37