inserting data to two tables simultaneously in different fields

[deepshah2004]

hello I need urgent help how to insert data to two tables simultaneously is this possible or not and if it is how to I go about doing it........please help URGENTLY

[rhodesa]

i don't believe this is possible. why can't you use two queries?

 

p.s. - this should be in the MySQL forum

[phparray]

This is not possibly but I've seen where it could be useful. If your tables are relational you'll need to retrieve the key after the first insert then use it when inserting into the second table.  Granted I'm assuming the tables are related making what I just said useful.

[deepshah2004]

hello  thanks for your feedback I have a sales table,purchases table and stock table some of the sales and purchases table entries need to be entered into the stock table as well how to do this.

 

sales table fields:

 

sales id(primary key),date,item code,description,customer name,customer account number, quantity,unit price,amount,discount,net amount and terms

 

purchases table fields:

 

purchases id(primary key),date,item code,description,supplier name,supplier code,quantity,unit price,amount,freight charges,discount,net amount and terms

 

stock table fields:

 

 

stock id(primary key),date,sales id(foreign key),purchases id(foreign key), quantity in, quantity out, reorder level quantity,minimum order quantity,balance quantity.

 

let me know if the relation is there to implement what you suggested and how.

 

thanks

 

[phparray]

Looks like this could use some re-organizing. You never want to have duplicate data. This ends up making you chase your tail to keep the data identical on multiple tables. 

 

I would create tables for Manufacturers, Models, Items, Customer, Purchases - Items

 

Manufacturers should have an id and a name

 

Models should have an id, a foreign key referring to the Manufacturers Id, and a name 

 

Items should store each item code, unit price and have a foreign key referring to the Model Id. 

 

Customer should be all customer data like id, name, address

 

Purchases should only be information that relates to the purchase like amount paid, data paid, and a foreign key to customer id

 

Purchases - Items.  This table would have many purchase ids and many item ids with a foreign key for each.

 

Hopefully this will get you started. I always include fields used for reporting as well.  Something like a user id so I know who modified it, date created and date modified fields so I can tell when it was created and when it changed.