How to use INSERT...SELECT to insert data form one table to another based on form input

Question

I have situation when i want to add data from column formCompany, formPlace in tabel1 based on input from form into table2 column formCompany, formPlace. Something like this:

$formCompany = $_POST['formCompany']; $formPlace = $_POST['formPlace']; $formOffer = $_POST['formOffer']; $sql="INSERT INTO table2 (formCompany, formPlace, formOffer) values ("SELECT table1.formCompany, table1.formPlace from table1 where formCompany LIKE '%$formCompany%' AND formPlace LIKE '%$formPlace%'", '$formOffer')"

The sample code is vulnerable to SQL injection, which is a very serious security risk. To fix this hole, use prepared statements, passing the values as parameters to the statement rather than interpolating them directly into the string. The site you save may just be your own. — outis
– outis, Commented Aug 29, 2011 at 10:47

Johan · Accepted Answer · 2011-08-29 11:44:23Z

First off

Fix the SQL injection hole
Use PDO, or

$formCompany = mysql_real_escape_string($_POST['formCompany']); $formPlace = mysql_real_escape_string($_POST['formPlace']); $formOffer = mysql_real_escape_string($_POST['formOffer']);

The syntax for insert select does not include the keyword 'VALUES'

$sql="INSERT INTO table2 (formCompany, formPlace, formOffer) SELECT table1.formCompany, table1.formPlace, '$formOffer' " //All insertions happen here ^^^^^^^^^^^^^^^^^^ ." FROM table1 WHERE formCompany LIKE '%$formCompany%' AND formPlace LIKE '%$formPlace%' ";

Now it should work.

Thank you very much. The syntax is ok and the code is also ok. That's save me a lot of time.

Vladimir · Accepted Answer · 2011-08-29 10:34:25Z

INSERT INTO table2 (fromCompany, fromPlace, fromOffer) SELECT table1.fromCompany, table1.fromPlace, '$fromOffer' AS t1fromOffer FROM table1 WHERE table1.formCompany LIKE '%$formCompany%' AND table1.formPlace LIKE '%$formPlace%'

Thank you, for your help. This code and syntax is very helpfull

genesis · Accepted Answer · 2011-08-29 10:35:19Z

$sqlSelect="SELECT table1.formCompany, table1.formPlace from table1 where formCompany LIKE '%$formCompany%' AND formPlace LIKE '%$formPlace%'";

fetch it and use it in your INSERT query

or

$sql="INSERT INTO table2 (formCompany, formPlace, formOffer) SELECT table1.formCompany, table1.formPlace, '$formOffer' from table1 where formCompany LIKE '%$formCompany%' AND formPlace LIKE '%$formPlace%'"

Collectives™ on Stack Overflow

How to use INSERT...SELECT to insert data form one table to another based on form input

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related