inserting data into multiple tables

[justlearning]

Hi,

I am just learning both MySQL and PHP, and have a question about a project I am working on. The first (and main) table for this project has the basic information about an refund request, including an autoincrement unique ID. Another table includes the products and uses the ID as a reference. The third table is the history of everything that happens to the request. For example, the information may be as follows.

Credit table
ID - autoincrement
status - requested, needs authorization, processed, etc
agent - who entered the request
customer - person requesting the refund
other customer info
order table
ID - reference to Credit table
Item - item ID for amount requested
quantity - number purchased
price - price of each item
(total information is calculated on above fields)
history table
ID - reference to credit table
seq - id for each history log
old status - the previous status
new status- the status after the update to the record
agent - agent making the change
notes - a description of what was done.

I have the PHP form that collects the data to be entered. My question is, since the first table is an autoincrement, what is the best way to insert a record into the Credit table, pull the ID that was just entered into the credit table and insert that ID into the order and history tables?

Thanx,
JustLearning

[justlearning]

Ever noticed that you can beat your head against a wall looking for an answer, and then, shortly after you ask for help, you find what you were looking for by yourself? It happens to me all the time.

There seems to be two different ways to do this. One is the PHP mysql_insert_id() function and the other is the MySQL LAST_INSERT_ID() function. Looks like I will have to play around to see which one works best in my application.

Thanx.

[Jeewhizz]

I find that using the MySQL method is easier, as i have had some problems using the php functioin in the past. Here's the code to retrieve it


$query = "SELECT last_insert_id()";
$results = mysql_db_query($db,$query,$connect);
list($id)=mysql_fetch_row($results);


$id now contains the last autoincrement number!

Jee

[AryaputrA]

How if i have a form and when user submits it, few fields from the form goes to a table and the others go to diff tables. Do i have to save all the form field like using :

// Setting variable from a form
$name = $_POST['name'];


and then use multiple sql insert to insert data to their respective tables? and , do i have to do the same when editing it ?

There's only one same field in all the table which is name. I'll use that field to display data.