CODEWITHSUNDEEP
phpmysql
sujith
sujith

Reputation: 57

update second table with the autoincremented id of the first table

This is my first post on the site. I'm a newbie with mysql and php so please pardon any mistakes and kindly point them out. I have a table to store hiring details:

table_hiring
hiringid int(20) AUTOINCREMENT => (primary key);
hiringname varchar(80);
hiringdate bigint(20);
... ...

I have another table to store the tools and their hire prices which has:

table_tools
tool_id int(10)AUTOINCREMENT => (primary key);
tool_description varchar(100);
tool_price double;

Each hire request would be saved in the hiring table. My problem is that each hire request can have multiple tools(Its a ground hire. so the hirer can request a hurdles set, a shotput set and a javelin set and so on.). I was stumped with the problem of storing multiple values in a single field. I did some research and decided to have another table

table_tool_hire
tool_hire_id() => (primary key from table_hiring);
tool_id() => (primary key from table_tools);
tool_hire_date bigint(20);

The problem is that everytime there is a hire request, according to the hirer's request i need to populate table_hiring and table_tool_hire in one query and the hire Id needs to be the matching in both the tables.

How do I insert the values into table_hiring and simultaneously get the autoincremented id value of that hire to be updated into the table_tool_hire table?? Please help...Thanks in advance...

Upvotes: 2

Views: 89

Answers (2)

jcho360
jcho360

Reputation: 3759

please take a look to this forum,

Get all insert ids in a transaction

you can use variables an store there the last insert id, 

INSERT INTO messages(`message`) VALUES ('message1');
@message1:=last_insert_id();
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo1');
@message2:=last_insert_id();

if you make

select @message1;

the result would be 1234, so you have

@message1 => 1234
@message2 => 1235

Upvotes: 0

user610217
user610217

Reputation:

For PHP, this is found with mysql_insert_id(). For .NET, take a look at this.

Upvotes: 1

Related Questions