Ok I've created a board. In mysql I have messages and topics.

In topics table i've got one interesting field: first_message_id
And in messages I need topic id.

So messages something like this:
id_msg, id_topic, blah blah blah...
and topics:
id_topic, first_message_id blah blah blah.......

The problem is that I cannot create topic, coz I don't have first message id and I cannot create id_msg coz I don't have id topic!!!!!!!!!!!!

I googled and found function: mysql_insert_id(); (displays last auto increment, well kinda useful)
So with such info I could create message without topic and then create topic and use mysql_insert_id() or no? This would work (I think)

Erm I know then selecting something I can join tables. Can I SOMEHOW do that by INSERTING? All my tries were fatal failures So if noone can help me I guess I will use my method nr 1. (Erm at least I know how to make it work )

This is where typically "transactions" come in. If you are using mysql you have to have a InnoDB format. Since you are talking about running into errors with your keys, I assume you do have InnoDB selected. Great.

Transactions are simply doing multiple steps in one logical chunk. If any one of the steps fail, the entire chunk fails.

What you are talking about with execution order is right. You are certainly thinking correctly. Transactions will help you with this. In the transaction you would first start using a "begin" statement, followed by two or more query statements. In your situation you will have a query that inserts a message, followed by another than inserts the topic, and a third that links the two in your conjunction table... that is your table with the id_msg and id_topic fields in it.

If all steps are correct and don't fail, you finish with a "commit" statement which then saves all the changes in one swoop. If any of those statements fail, you can call a "ROLLBACK" statement which undos all the steps. It undoes all the steps because you don't want to insert a message and have it not related to a topic (aka orphan records).

So the following website will show you how to do this process. Just remember that when inside the transaction (between a begin and a commit/rollback) the order in which you insert items still exists. You were right thinking to insert into message, insert into topic, then lastly insert into the table in between using the last insert id.

Enjoy the site below...

Transactions in MySQL (assumes InnoDB format)

Erm it sounds hard...
Couldn't I just insert a message, take mysql_insert_id() and create topic. If topic fails, then delete message using mysql_insert_id() ?

How do vbulletin, smf or phpbb handle this?

well first transactions are not as hard as you think. The only difference between a transaction and a regular group of queries is that you call begin before you do your queries and either commit or rollback after your queries based on if there was an error.

You don't need transactions, but then of course you run the risk of having records that don't match up in tables and can cause problems. You can manually check if each row was added successfully and if so continue with adding it to another table or having to issue queries to undo what you did if there was a problem.

Either way is up to you and doing it without transactions can get very bothersome and long since you have to insert row, check if it was successful, insert another, check if it was successful, if not issue another query to remove the first row and see if that was successful etc etc. I am sure you get my point.

mysql_insert_id will have problems of its own and isn't always the row you just inserted (keep in mind that someone else could have inserted a row into the message table in the middle of you adding a message and then a topic. Thus last ID will be their id, not yours. Keep this in mind when you suddenly see someone elses messages appearing in someones topic.

But do as you like, I am just telling you the most proper way to do such a task. Good luck to you.

So after creating table width TYPE=InnoDB; I can use in query new statemens (BEGIN and COMMIT) ?

First I tried to create a version of it which would at least work, but failed:
Chek this out.

This only works then inserting a topic then topic table is empty. If topic already exists, only message creates and updates the id_topic to same as id_msg. (coz second query fails)

Erm back to transactions. Can you give me an example in code or human language how to do this (approximately).
P.S. So using mysql_insert_id() is bad idea at all?

This post has been edited by Dancia: 14 Jul, 2008 - 08:56 AM

I still need help ~