1

How might I go about doing an insert into a database across multiple, connected tables? Is it exactly the same as when doing a select statement with joins? Would it like something like this? Or can you not do joins on inserts? If not joins, how else can I connect the tables on an insert so that all values go into appropriate fields in appropriate tables?

$query = "INSERT INTO stories, wp_users.ID, writing (user_ID, SID, story_name, story_text)   
VALUES ('$user_ID', "$the_SID, '$story_name','$story_text'
LEFT JOIN writing ON stories.SID = writing.SID
LEFT JOIN wp_users ON stories.ID = wp_users.ID
WHERE stories.SID = $the_SID");
)";
user5000
  • 129
  • 1
  • 9
  • 2
    Joins don't exist for inserts. Which is the _main_ table, the parent of the other related tables? You must first insert a row to the parent, retrieve its new id if necessary for the relationships, then insert to the related tables. – Michael Berkowski Jul 29 '14 at 01:58
  • 2
    And consider wrapping the operations in a transaction. http://stackoverflow.com/questions/19153986/how-to-use-mysql-transaction-in-wordpress – Michael Berkowski Jul 29 '14 at 01:59
  • I suppose the main table is stories, although the writing table is almost as important. users I use just to get author name. So you suggest I do two separate inserts then? I can then do an insert WHERE after? And I am reading about transactions now. Thanks. – user5000 Jul 29 '14 at 02:29
  • 1
    There is no such thing as a `WHERE` clause for an insert either - that would only apply to UPDATEs. Typically the pattern is 1) begin transaction 2) insert into the parent table 3) retrieve the last insert id (using whatever function your API (wordpress) prefers) and 4) perform another insert using the retrieved id to establish the relationship 5) commit the transaction. – Michael Berkowski Jul 29 '14 at 02:34
  • Thanks for the steps, I'll give it a go with transactions. ALL steps you list are are wrapped within the same transaction (within START TRANSACTION; and COMMIT;)? I was going to have the same question about updates since that is what I will be trying to do much more of. For those I just use regular updates with with where clauses (no joins no?), correct? – user5000 Jul 29 '14 at 02:46

1 Answers1

0

In ORACLE, you can use insert all into table1 values (...) into table2 values (...), that is one sql and can insert into two or more tables. However, in mysql, it does not support insert all, than i suggest transactions instead.

iatboy
  • 1,295
  • 1
  • 12
  • 19