Converting query results into an insert statement..

[enatefox]

Hi,
Sorry if this was asked previously-- I did not see a similar question.
I am building a product entry system with PHP5 and MySQL where a user enters any of 3 types of data and gets a result. The result then needs to be able to be inserted into another table.

It needs to flow like this:
user searches > results shown > user clicks to insert it into database

Problem:
How do I get the values from the SELECT query to populate the INSERT query when it's outside of the IF statement?

... How do I get the values into the ELSE part of the statement?

Here's some Englishized code to make it easier to understand:

// $s = what type, $d = value, $i = inserting?
// $s and $d will be set if searching, $i alone if inserting:
if(isset['s'] && isset['d'] && !isset['i']) { 
	$query = "SELECT * FROM products WHERE $d LIKE '%$s%' LIMIT 0,3"; 
	mysql_query($query) or DIE("whoops");
	echo the column results...
}

// now to insert the selected product into the user's inventory
else if(!isset['s'] && !isset['d'] && isset['i']) { 
	$query = "INSERT INTO products (products_id,scanned_usr,scanned_dte,ndc_num,control_code,rxotc_code,package_size,units_pkg,unit_dosage_flag,dosage_code,unit_of_measure,product_desc,strength,mfg_num,wac_price,arcos_flg,upc,user_active,original_package_size,original_units_pkg,package_weight) VALUES"; 

(select * from products where products_id = ???)

. $scanned_usr
. $scanned_dte
etc. etc. etc.
}

** I know the query is rather vulnerable to injections. I'd love some feedback on how I could improve that too, but maybe another post...

Background (if interested):

I am having clients scan inventory of drugs. First the product has to exist, then it has to match and then finally, the user will specify quantity, condition, etc. The majority of the INSERT statement will be user-submitted but the key details need to come from the SELECT query (product id, etc.)

[nemonoman]

The SQL format is like this:

INSERT INTO table1
(column1, column2)
SELECT columnA, columnB
FROM table2
WHERE x=y

You can intermix constants like this:
INSERT INTO table1
(textcolumn, numbercolumn, column1, column2)
SELECT 'Some Constant Text', 9999, columnA, columnB
FROM table2
WHERE x=y

Here's a php example:

$userid=$_POST['user'];
$selection=$_POST['product'];
$selection=$_POST['quantity'];
$result=mysqlquery("
INSERT INTO shoppingcart
(user, product, quantity, productUPC, productPrice, orderLineAmount)
SELECT '$userid',productid, '$quantity', UPC, price, price*$quantity
FROM product
WHERE product.id=$selection");

[enatefox]

Thanks, I've done that with MSSQL queries and I guess I overlooked that aspect.

The problem I'm having (now) is that I need to run a SELECT to populate the product table so a user can confirm the input. This is run inside an IF/ELSE to determine if there was a product entered.

How can I grab the results run inside the IF and insert them in the ELSE IF?

I know that PHP is backwards to C so that I declare the global instance inside the IF/ELSE instead of at the declaration but would that still catch the variables? I think my best bet is to register it to the session...

Pseudo-code:
IF(isset('prod_id')) {
SELECT ....
register('col1')
register('col2')
...
ELSEIF('isset('doinsert')) {
global col1
global col2

[nemonoman]

The form is:
if(isset($prodid)){}

But you can just find if it's been posted:
if($_POST['prodid']){}

[enatefox]

Thanks again. For those of you with a similar issue, this is what I did. Took me a minute or two so I thought I'd share it:

I'm using an AJAX page included in a member's area that queries a separate page and displays the results inline. That second page checks the action value in a hidden box from the form and runs accordingly (since the user won't be able to refresh the page or anything).

Javascript determines the value of the hidden box so if searching, then the box = 'dosearch' if it's inserting then it's 'doinsert' and the PHP checks that value to pass it to an IF/ELSE statement.

And I'm using the SELECT to populate the INSERT part so that skips the need for session variables, et all.

PAGE 1:
query ? (x=productid & action=dosearch)

=>

Page 2:
what's action's value?
IF action = dosearch {SELECT...}
ELSE IF action = doinsert {INSERT..VALUES (SELECT...)}