inserting data from a form variable selection

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ktell
Forum Newbie
Posts: 5
Joined: Fri Nov 29, 2002 1:12 am

inserting data from a form variable selection

Post 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.");
?>
f1nutter
Forum Contributor
Posts: 125
Joined: Wed Jun 05, 2002 12:08 pm
Location: London

Post 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.
User avatar
BigE
Site Admin
Posts: 139
Joined: Fri Apr 19, 2002 9:49 am
Location: Missouri, USA
Contact:

Post 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.
ktell
Forum Newbie
Posts: 5
Joined: Fri Nov 29, 2002 1:12 am

Thanks F1Nutter

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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';
ktell
Forum Newbie
Posts: 5
Joined: Fri Nov 29, 2002 1:12 am

Thanks f1nutter

Post by ktell »

Thanks very much f1nutter.
Got it working with while loop and your input.
Post Reply