Page 1 of 1

inserting data from a form variable selection

Posted: Sat Nov 30, 2002 11:24 am
by ktell
Stumped on this one and after hours of struggle and would very much appreciate any help.

Have a form that posts to a php file that contains the code below.
I want to select state_province_price from table_name2 where origin_state_province is determined by the user set $origin_state_province in the form and destination_state_province is determined by the user set $destination_state_province in the form.
Then I want to insert state_province_price into table_name.state_price.

Here is what i have come up with so far.
<?
$table_name = "email_quotes";
$table_name2 = "pair_price";

$sql = "INSERT INTO $table_name


( state_price)
VALUES
(\"SELECT state_province_price
FROM $table_name2 WHERE origin_state_province = $origin_state_province AND destination_state_province = $destination_state_province\")
";

$result = @mysql_query($sql,$connection) or die("Couldn't execute query.");
?>

Posted: Sat Nov 30, 2002 1:22 pm
by f1nutter
I believe that MySQL currently does not support queries within queries. Later versions will, but not yet.

You will need to run the SELECT query first and 'save' your results, before passing to the INSERT query. You could read the results into an array or place INSERT inside a while loop.

Code: Select all

&lt;?php
$sql_1 = "SELECT state_province_price
FROM $table_name2 WHERE origin_state_province = $origin_state_province
AND destination_state_province = $destination_state_province";

$result = mysql_query($sql_1,$connection)
 or die("Couldn't execute query." .mysql_error());

while ($row = mysql_fetch_assoc($result))
{
 $sql_2 = "INSERT INTO $table_name ( state_price) VALUES $row&#1111;'column']";

 mysql_query($sql_2,$connection);
}

?&gt;
Hope this points you in the right direction.

Posted: Sat Nov 30, 2002 1:53 pm
by BigE
Sure it does, its reffered to as INSERT SELECT. The manual page for it can be found at http://www.mysql.com/doc/en/INSERT_SELECT.html. Now as far sub selects... that doesn't come into MySQL until version 4. Anyway, check out that manual page, that should help.

Thanks F1Nutter

Posted: Sun Dec 01, 2002 12:44 am
by ktell
Appreciate your rapid feedback.
Yes,,it does get me headed the right direction using WHILE loop. Yet dont understand what column name to use. I have tried placing state_province_price where "column" exists in your sample. But I get error that reads parsing error, expecting `T_STRING` or `T_VARIABLE` or T_NUM_STRING`

Any ideas?

Thanks Much

Posted: Sun Dec 01, 2002 1:01 am
by volka
you have to escape the special meaning of " in a double quoted string.

Code: Select all

$failing = "the parser recognizes " as end of the string";
// ^ parse error. notice the change of color here

Code: Select all

$working = "the parser interpretes " as a double quote sign with the stringliteral";
$alsoworking = 'in a single qouted string " has no special meaning, but $var will not be replaced by its content';

Thanks f1nutter

Posted: Sun Dec 01, 2002 2:30 am
by ktell
Thanks very much f1nutter.
Got it working with while loop and your input.