Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jun 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question inserting data into multiple tables

    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

  • #2
    New Coder
    Join Date
    Jun 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool ever noticed?

    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.
    ~~~ Pretend that I have a long and creative signature. ~~~

  • #3
    Regular Coder
    Join Date
    May 2002
    Location
    London, England
    Posts
    367
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Jeewhizz - MySQL Moderator
    http://www.sitehq.co.uk
    PHP and MySQL Hosting

  • #4
    Regular Coder
    Join Date
    May 2003
    Location
    Malaysia
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    The Aryan Horde
    Be like the wise and not otherwise
    Self Ignorance Is Death


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •