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.");
?>
inserting data from a form variable selection
Moderator: General Moderators
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.
Hope this points you in the right direction.
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
<?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ї'column']";
mysql_query($sql_2,$connection);
}
?>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
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
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
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 hereCode: 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
Thanks very much f1nutter.
Got it working with while loop and your input.
Got it working with while loop and your input.