Databases
StackOverflow2013
Postsdbo
POPHP MySQL Insert / Select multiple one to Many
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP MySQL Insert / Select multiple one to Many
    text
    copied!<p>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...</p> <p>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.</p> <p>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).</p> <pre><code>$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'"; </code></pre> <p>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.</p> <pre><code>//$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); </code></pre> <p>Should I be doing this straight into the database, or should I pull the data out into variables first? If so, how?</p> <p>Many thanks in advance.</p> <p><strong>UPDATE</strong> 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.</p> <pre><code>// 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); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload