PHP MySQL Insert / Select multiple one to Many

Question

I've been struggling with this one for a while, and have searched various sources (books / the web / this site) but not found a solution yet. I haven't been using PHP / MySQL for that long, so I may not know the relevant vernacular to be able to target my search. Anyway...

I have a basic order management system. The customer can enter multiple lines for an item they want tested, each line having the item to be tested, and the suite of tests required. I then need to expand each of these items into its constituent tests for the test lab to manage its workflow. I need to be able to tie the test back to the order item, and the order.

I have an order table where the overall order is captured with an order_id field (auto-incremented) and some other information (who placed the order etc). I then populate an order item table with the order_id from the order table and each item / test suite combination that the customer wants, each order item has an order_item_id (auto incremented).

$sql1="INSERT INTO tbl_order(od_date, od_customer_id)
    SELECT ct_date, ct_customer_id 
    FROM tbl_cart
    WHERE ct_customer_id='$customerID' AND ct_session_id='$sid'";
mysql_query($sql1);
// get order_id
$orderid=mysql_insert_id();

// save order details to tbl_order_item
$sql2="INSERT INTO tbl_order_item(cat_code, pd_code, pd_description, pc_code, smpl_type, test_suite_name)
    SELECT cat_code, pd_code, pd_description, pc_code, smpl_type, test_suite_name 
    FROM tbl_cart
    WHERE ct_customer_id='$customerID' AND ct_session_id='$sid'";
mysql_query($sql2);

//  update date fields
$sql3="UPDATE tbl_order SET od_date='$date' WHERE ct_date='0000-00-00 00:00:00'";
$sql4="UPDATE tbl_order SET od_username='$username' WHERE od_username=''";
mysql_query($sql3);
mysql_query($sql4);

// set the order id in the order item and lab item tables
$sql5="UPDATE tbl_order_item SET od_id='$orderid' WHERE od_id='0'";

That all works, however I'm now stuck when I try and take each each order item and expand it into its relevant tests. I have created a table which shows the test_suite / test_name combination, but I can't get the output inserted into the lab_item table.

//$sql6="UPDATE tbl_lab_item SET od_id='$orderid' WHERE od_id=''";
mysql_query($sql5);

// Expand Suite into tests in lab item table
$sql7="INSERT INTO tbl_lab_item(od_id,test_name)
    VALUES ('$orderid',(SELECT test_name FROM tbl_suite_select  WHERE tbl_order_item.test_suite_name=tbl_suite_select.test_suite_name))";
mysql_query($sql7);

Should I be doing this straight into the database, or should I pull the data out into variables first? If so, how?

Many thanks in advance.

UPDATE I've solved the problem now. Thanks for the input, if anyone is interested, this is was the query that eventually solved it. It was all hinged on calling the right table / cell in the Select for the final table. I was missing a field, so it didn't have a reference.

//  Insert individual tests into tbl_lab_item
$sql6="INSERT INTO tbl_lab_item(od_item_id, test_suite_name,test_name)
    SELECT tbl_order_item.od_item_id,tbl_order_item.test_suite_name,tbl_suite_select.test_name
    FROM tbl_order_item,tbl_suite_select
    WHERE tbl_order_item.test_suite_name=tbl_suite_select.test_suite_name";
mysql_query($sql6);

The mysql_* functions will be deprecated in PHP 5.5. It is not recommended for writing new code as it will be removed in the future. Instead, either the MySQLi or PDO and be a better PHP Developer. — Jason McCreary
– Jason McCreary, Commented Jan 16, 2013 at 16:03
Hi Jason - thanks for that. I'd read about the mysqli change, but wanted to get a working code set first, and then update it. I guess it's the trouble with needing it finished a week ago :-\ — Jason
– Jason, Commented Jan 16, 2013 at 16:14
"and then update it" No, it is a safe bet that you won't update anything later. Call it experience or intuition. — feeela
– feeela, Commented Jan 16, 2013 at 16:43
Normally I would agree :), however in this case I'll be forced to as the future versions of PHP will mean I have to. — Jason
– Jason, Commented Jan 16, 2013 at 17:03

alexwells · Accepted Answer · 2013-01-16 16:47:23Z

I'm not sure I understand the question fully, but here are some improved queries (using pdo). I think the main point I added was the join statement in the last query. I also combined some of your previous queries to add the rows in one go (instead of inserting than updating). I didn't test anything.

$sqlStmt = $pdo->prepare("
    INSERT INTO tbl_order
    (   od_date
    ,   od_customer_id
    ,   od_date
    ,   od_username)
    SELECT
        ct_date
    ,   ct_customer_id
    ,   ? AS od_date
    ,   ? AS od_username
    FROM tbl_cart
    WHERE ct_customer_id = ?
    AND ct_session_id = ?
");

$sqlStmt->execute(array(
    $date,
    $username,
    $customerID,
    $sid
));

$orderid = $pdo->lastInsertId();

$sqlStmt = $pdo->prepare("
    INSERT INTO tbl_order_item
    (   cat_code
    ,   pd_code
    ,   pd_description
    ,   pc_code
    ,   smpl_type
    ,   test_suite_name
    ,   od_id)
    SELECT 
        cat_code
    ,   pd_code
    ,   pd_description
    ,   pc_code
    ,   smpl_type
    ,   test_suite_name 
    ,   ? AS od_id
    FROM tbl_cart
    WHERE ct_customer_id = ?
    AND ct_session_id = ?
");
$sqlStmt->execute(array(
    $orderid,
    $customerID,
    $sid
));

$sqlStmt = $pdo->prepare("
    INSERT INTO tbl_lab_item
    (   od_id
    ,   test_name)
    SELECT
        ? AS od_id
    ,   tss.test_name
    FROM tbl_suite_select tss
    INNER JOIN tbl_order_item toi
    ON toi.test_suite_name = tss.test_suite_name
    WHERE toi.od_id = ?
");
$sqlStmt->execute(array(
    $orderid,
    $orderid
));

Thanks for this. I don't know anything about PDO (yet), but I'll have a look at this and see if it works.

Adder · Accepted Answer · 2013-01-16 16:38:40Z

0

This should transform your code into working code:

$sql7="INSERT INTO tbl_lab_item(od_id,test_name)
       SELECT '$orderid', test_name FROM tbl_suite_select 
       WHERE tbl_order_item.test_suite_name=tbl_suite_select.test_suite_name";

I don't quite get whether the condition applied with where is sufficient, though.

answered Jan 16, 2013 at 16:38

Adder

5,8982 gold badges35 silver badges60 bronze badges

1 Comment

Jason Over a year ago

Hi - thanks for this. Unfortunately it doesn't work, although I've spotted another issue. The $orderid doesn't come from the tbl_suite_select, but I thought using brackets would get around this. I do still need to identify the source table in the query though. I've tried so many variations now I'm losing track. Thanks though.

Collectives™ on Stack Overflow

PHP MySQL Insert / Select multiple one to Many

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related