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 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    multi table inserts

    So...I have a form with these fields..

    name, date, time, information, and who.

    who is a check box field that has 7 boxes in it.

    basically i want to insert the name, date, time, info into a table. with the same click of the submit button i want to add the auto inc id value the first insert query creates, and place it into a separate table, along with the check box value.

    with that...i want to create a new row in the second table for each of the check boxes that are selected

    ---------------------------------------------------
    for example:

    if this was inputed into the form:

    name: this story
    date: today
    time: now
    info: some info about the story

    check box(out of 7 boxes): 1,3,6 were selected.
    ---------------------------------------------------

    first table insert entry:

    story_id(auto_inc) = '25'
    story_name = 'this story'
    story_time = 'today'
    story_date = 'now'
    story_info = 'some info about the story'



    Second table entries:

    check_id(auto inc) = '40'
    check_parent = '25'
    check_value = '1'

    check_id(auto inc) = '41'
    check_parent = '25'
    check_value = '3'

    check_id(auto inc) = '42'
    check_parent = '25'
    check_value = '6'


    I hope this makes sense...

  • #2
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    are you getting the information from a database?
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #3
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    the first insert would add the new entry into the table on the db..

    for the second..i wasnt sure if i had to do it separate or if i could do it at the same time...

    1) i couldnt think of a way to retrieve the id from the first table and insert into the second without pulling it from the db. I would LIKE to do it all at once but i dont know if it is possible..

    2) i dont know how to create multiple rows depending on how many check boxes have been checked

  • #4
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    yes it is possible,

    this contains a lot of junk and im going home now but something like:

    PHP Code:

    // If the submit button was clicked
        
    if (isset($_POST['SubmitOrder'])) {
        
    // Add new order in first to get the order ID
            
    db('open');
                
    $results query("INSERT INTO `webbook`.`ae_orders` (
                                    `OrdersID`
                                    , `OrderNumber`
                                    , `OrderDate`
                                    , `DateDelivered`
                                    , `InitialCode`
                                    , `DateOfEnquiry`
                                    , `CustomerON`
                                    , `TypeOfEnquiry`
                                    , `DateQuoted`
                                    , `DateChased`
                                    , `WhoBy`
                                    , `CustoemrID`
                                    , `CreatedBy`
                                    , `ContactID`
                                    , `Notes`
                                    , `Archived`
                                    , `Timestamp`)
                                    VALUES (
                                    NULL
                                    , '$_POST[OrderNumber]'
                                    , '$_POST[OrderDate]'
                                    , '$_POST[DateDelivered]'
                                    , '$_POST[InitialCode]'
                                    , '$_POST[DateOfEnquiry]'
                                    , '$_POST[CustomerON]'
                                    , '$_POST[TypeOfEnquiry]'
                                    , '$_POST[DateQuoted]'
                                    , '$_POST[DateChased]'
                                    , '$_POST[WhoBy]'
                                    , '$_POST[CustomerID]'
                                    , '$_POST[CreatedBy]'
                                    ,' $_POST[ContactName]'
                                    , '$_POST[Notes]'
                                    , 'n'
                                    , CURRENT_TIMESTAMP)"
    __LINE____FILE__);

        
    // Get the ID from inserted row above.
            
    $OrderID mysql_insert_id();

        
    // Start building te query for the sales items
            
    $BuildQuery "INSERT INTO `webbook`.`ae_sales` (
                                `SalesID`
                                , `ItemDescription`
                                , `CustomerPartNumber`
                                , `ANUMBER`
                                , `Quantity`
                                , `StatusID`
                                , `MinutesPerPart`
                                , `OPSID`
                                , `SubConSupplierID`
                                , `PowderCoatCost`
                                , `PaintColourID`
                                , `Fabrication`
                                , `OID`
                                , `InvoiceDate`
                                , `InvoiceBy`
                                , `SellPrice`
                                , `SupplierID`
                                , `CostPrice`
                                , `DueDate`
                                , `DeliveryNoteNum`
                                , `InvoiceNumber`
                                , `DeliveredAmount`
                                , `OrderID`
                                , `CreatedBy`
                                , `MaterialID`) 
                                VALUES "
    ;

    // Count the number of lines that was submitted
        
    $LineCount count($_POST['ItemDescription']);

    // Get the md5 of a blank line with the users ID and Order ID inserted to compare with later
        
    $BlankLineMD5 md5("( NULL , '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '"$OrderID"', '"$_SESSION['UserID'] . "', ''),");
        
    $x 0;
        while (
    $x $LineCount) {
        
    // build the line with data that was posted 
        
    if ($_POST['QTY'][$x] != ''){
            if (
    $_POST['ANumber'][$x] == ''){
            
    $ANResults query("SELECT * FROM `anumber`"__LINE____FILE__);
            
    $ANrow mysql_fetch_array($ANResults);
            
    $AANO $ANrow['ACODE'];
            
    $NANO NextAAnumber($ANrow['ACODE']);
            
    $ANUResults query("UPDATE `anumber` SET `ACODE` = '".$NANO."' WHERE `NEXTUP` = 'A'"__LINE____FILE__);
            }else{
                
    $AANO $_POST['ANumber'][$x];
            }
            
    $QueryLine "(NULL, '"$_POST['ItemDescription'][$x]. "', '"$_POST['CustomerPartNumber'][$x] ."', '"$AANO ."', '"$_POST['QTY'][$x] ."', '"$_POST['Status'][$x] ."', '"$_POST['MinutesPerPart'][$x] ."', '"$_POST['OPSID'][$x] ."', '"$_POST['SubConSupplierID'][$x] ."', '"$_POST['PowderCoatCost'][$x] ."', '"$_POST['PaintColourID'][$x] ."', '"$_POST['Fabrication'][$x] ."', '"$_POST['OID'][$x] ."', '"$_POST['InvoiceDate'][$x] ."', '"$_POST['InvoiceBy'][$x] ."', '"$_POST['SellPrice'][$x] ."', '', '"$_POST['CostPrice'][$x] ."', '"$_POST['DueDate'][$x] ."', '"$_POST['DeliveryNoteNum'][$x] ."', '"$_POST['InvoiceNumber'][$x] ."', '"$_POST['DeliveredAmount'][$x] ."', '"$OrderID ."', '"$_SESSION['UserID'] ."', '"$_POST['Material'][$x] ."') ,";
            
        
    // If the md5 of the line just created is the same as $BlankLineMD5
            
            //if (md5($QueryLine) == $BlankLineMD5) {
            // Then exit the loop
                //break;
            
    }
        
    // Otherwise add the line to our query
            
    $BuildQuery .= $QueryLine;
            
    $x++;
        }

    // Take the last character off the query because its a comma
        
    $BuildQuery substr($BuildQuery0, -1);

    // If $x is 0 then no lines must have been processed so dont run the query.
        
    if ($x != 0) {
            if (
    query($BuildQuery__LINE____FILE__)) {
                
    $MessageColour "Green";
                
    $Message "Order successfully added to the database";
            } else {
                
    $MessageColour "Red";
                
    $Message "An error occured when trying to insert your order.";
            }
        }
        
        
    db('close');
        
        } 
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #5
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I will try and sort that out haha...

    thanks though

  • #6
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i dont really know what im looking at...can some one explain it to me a little ?

  • #7
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    something like:


    PHP Code:
    // If the form was submitted
        
    if (isset($_POST['story_name'])) {
        
    // Add new story to get the story_id
            
    db('open');
                
    $results query("INSERT INTO `dbname`.`table1` (
                ,`story_name`
                ,`story_time`
                ,`story_date`
                ,`story_info`)
                VALUES (
                , '$_POST[story_name]'
                , '$_POST[story_time]'
                , '$_POST[story_date]'
                , '$_POST[story_info]')
                "
    );

        
    // Get the ID from inserted row above.
            
    $check_parent mysql_insert_id();

        
    // Start building the query
            
    $BuildQuery "INSERT INTO `dbname`.`table2` (
                                , `check_parent`
                                , `check_value`) 
                                VALUES "
    ;

    // Count the number of boxes that was submitted
        
    $LineCount count($_POST['check_value']);

        
    $x 0;
        while (
    $x $LineCount) {
        
    // build the line with data that was posted 
        
    if ($_POST['check_value'][$x] != ''){

            
    $QueryLine "('".$check_parent."', '"$_POST['check_value'][$x] ."') ,";

            }
            
    $BuildQuery .= $QueryLine;
            
    $x++;
        }

    // Take the last character off the query because its a comma
        
    $BuildQuery substr($BuildQuery0, -1);

    // If $x is 0 then no lines must have been processed so dont run the query.
        
    if ($x != 0) {
            if (
    query($BuildQuery__LINE____FILE__)) {
                
    $MessageColour "Green";
                
    $Message "Order successfully added to the database";
            } else {
                
    $MessageColour "Red";
                
    $Message "An error occured when trying to insert your order.";
            }
        }
        
        
    db('close');
        
        } 
    __________________ 
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • Users who have thanked Arcticwarrio for this post:

    cdoggg94 (02-26-2013)

  • #8
    New Coder
    Join Date
    Apr 2010
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    You can insert more than one row into a table with single query.

    The following query is okay:

    INSERT INTO example
    (example_id, name, value, other_value)
    VALUES
    (100, 'Name 1', 'Value 1', 'Other 1'),
    (101, 'Name 2', 'Value 2', 'Other 2'),
    (102, 'Name 3', 'Value 3', 'Other 3'),
    (103, 'Name 4', 'Value 4', 'Other 4');

    To refer the last created unique id, you can simply mention LAST_INSERT_ID().

    So your second query could be like:

    INSERT INTO example
    (check_parent, check_value)
    VALUES
    (LAST_INSERT_ID() , '1')
    (LAST_INSERT_ID() , '2')
    (LAST_INSERT_ID() , '3')

    HTH
    Hosting Reviews and Discounts: Bluehost Coupon and Hostmonster Coupon

  • #9
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    thats exactly what i just posted

    PHP Code:
    $check_parent mysql_insert_id(); 
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #10
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I got it to work like this..

    seems to be a bit less confusing...

    I don't know if its the best way to do it but it is working.

    Is there any major flaws ?

    Code:
    <?php
    mysql_query("INSERT INTO soccer_dates (date_id, date_name, date_date, date_time, date_content, date_email)
    VALUES ('', '".$_POST['name']."', '".$_POST['date']."','".$_POST['time']."','".$_POST['comments']."','')");
    
    $parent = mysql_insert_id();
    //$whoCat = print_r($_POST['who']);
    
    
    foreach($_POST['who'] as $option_key => $option_name)
    {
    mysql_query("INSERT INTO soccer_dates_who (who_id, who_parent, who_who)
    VALUES ('', '".$parent."', '".$option_name."')");
    }
    
    
    
    echo "<br /><br />Thank you for uploading<br /><br />";
    
    ?>

  • #11
    New to the CF scene
    Join Date
    Aug 2012
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for all the help

  • #12
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    721
    Thanks
    20
    Thanked 84 Times in 84 Posts
    its fine like that but for a heavy duty script that would create a lot of network traffic.
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month


  •  

    Posting Permissions

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