complicated inserts

Question

I have to do something like this

insert into object (name, value, first_node) values ('some_name', 123, 0)
@id = mysql_last_insert_id()
insert nodes (name, object_id) values ('node_name',@id)
@id2 = mysql_last_insert_id()
update object set first_node=@id2 where id=@id

Is it possible to make it simpler? What if I want to insert more pairs (object, node) with resonable efficency?

Answer 1

You should use a third table to make your relationships:

• OBJECT(ID,NAME,VALUE)
• NODES(ID, NAME)
• OBJ_NOD(NODE, OBJECT)

You can make your inserts this way:

insert into object (name, value) values ('some_name', 123)
@oid = mysql_last_insert_id()
insert nodes (name) values ('node_name')
@nid = mysql_last_insert_id()
insert obj_node (node,object) values (@nid, @oid)

You can then use joins to get the node for a particular object (or objects for a node):

SELECT O.ID AS OID, O.NAME AS ONAME, O.VALUE, N.NAME AS NNAME, N.ID AS NID
FROM OBJECT AS O
JOIN OBJ_NOD ON OBJ_NOD.OBJECT = O.ID
JOIN NODES AS N ON N.ID = OBJ_NOD.NODE
WHERE O.ID = @object_id; —- or 'N.ID = @node_id' if you want to get elements from a node id