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

PHP Code:

$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 :

PHP Code:

// 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.