multi table inserts

cdoggg94 · 02-22-2013, 04:32 PM

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

Arcticwarrio · 02-22-2013, 04:40 PM

are you getting the information from a database?

cdoggg94 · 02-22-2013, 04:45 PM

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

Arcticwarrio · 02-22-2013, 04:48 PM

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($BuildQuery, 0, -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');
    
    }

cdoggg94 · 02-22-2013, 04:53 PM

I will try and sort that out haha...

thanks though

cdoggg94 · 02-22-2013, 05:42 PM

i dont really know what im looking at...can some one explain it to me a little ?

Arcticwarrio · 02-25-2013, 04:57 PM

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($BuildQuery, 0, -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');
    
    } 
__________________

alemcherry · 02-26-2013, 10:02 AM

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

Arcticwarrio · 02-26-2013, 11:15 AM

thats exactly what i just posted

PHP Code:


			
$check_parent = mysql_insert_id();

cdoggg94 · 02-26-2013, 04:52 PM

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 />";

?>

cdoggg94 · 02-26-2013, 04:57 PM

Thanks for all the help

Arcticwarrio · 02-26-2013, 04:58 PM

its fine like that but for a heavy duty script that would create a lot of network traffic.