PHP Insert data from one table to another

Question

I was trying to insert the data from one table to another, and this is the error message I get:

Error: INSERT INTO content2 (d1, d2, d3) VALUES (John, Doo, 24);

Unknown column 'John' in 'field list'Error: INSERT INTO content2 (d1, d2, d3) VALUES (Mary, Moe, 36);

Unknown column 'Mary' in 'field list'Error: INSERT INTO content2 (d1, d2, d3) VALUES (Julie, Dooley, 42)

Unknown column 'Julie' in 'field list'Error: INSERT INTO content2 (d1, d2, d3) VALUES (John, Doo, 24);

Unknown column 'John' in 'field list'Error: INSERT INTO content2 (d1, d2, d3) VALUES (Mary, Moe, 36);

Unknown column 'Mary' in 'field list'Error: INSERT INTO content2 (d1, d2, d3) VALUES (Julie, Dooley, 42);

Unknown column 'Julie' in 'field list'

Here is my php code:

//Get Connection require_once('connect.php'); //Get Data $sql = "SELECT d1, d2, d3 FROM content"; $result = mysqli_query($con, $sql); if (mysqli_num_rows($result) > 0) { // output data of each row while($row = mysqli_fetch_assoc($result)) { $row1 = $row["d1"]; $row2 = $row["d2"]; $row3 = $row["d3"]; //Insert Data to another table $sql = "INSERT INTO content2 (d1, d2, d3) VALUES ($row1, $row2, $row3);"; if (mysqli_multi_query($con, $sql)) { echo "New records created successfully"; } else { echo "Error: " . $sql . "<br>" . mysqli_error($con); } //echo "id: " . $row["d1"]. " - Name: " . $row["d2"]. " " . $row["d3"]. "<br>"; } } else { echo "0 results"; }

I couldn't figure out what the problem are. Please Help

VALUES ($row1, $row2, $row3) to VALUES ('$row1','$row2', '$row3') — Abhik Chakraborty
– Abhik Chakraborty, Commented Jul 9, 2015 at 16:08
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. NEVER put raw user data directly into a query. — tadman
– tadman, Commented Jul 9, 2015 at 16:10
Better option would be to use prepared statements to keep erroneous or nefarious supplied information from breaking your database or allowing sql injection attacks — Orangepill
– Orangepill, Commented Jul 9, 2015 at 16:11
@Orangepill Generally yes, absoltely, but as Dan points out in his answer, this is something the database can easily tackle on its own. — tadman
– tadman, Commented Jul 9, 2015 at 16:11
@tadman true... just didn't see that solution past the gaping security hole :) — Orangepill
– Orangepill, Commented Jul 9, 2015 at 16:17

Mr. DOS · Accepted Answer · 2015-07-09 16:13:22Z

When PHP sends your insertion query to MySQL, it ends up looking like this:

INSERT INTO content2 (d1, d2, d3) VALUES (John, Mary, Julie);

Because there are no quotation marks around “John”, “Mary”, and “Julie”, MySQL thinks you're referring to other column names. The quick and dirty solution would be to add quotation marks to your query, but as @tadman says, you should not ever be using this style of query, and should instead use bind_param to add your variables to the query.

However, if all you want to do is copy from one table to another, as @Dan Bracuk says, you can do this with a single query:

INSERT INTO content2 (d1, d2, d3) SELECT d1, d2, d3 FROM content

Dan Bracuk · Accepted Answer · 2015-07-09 16:10:56Z

This can be done with straight sql.

insert into content2 (d1, d2, d3) select d1, d2, d3 from content

Collectives™ on Stack Overflow

PHP Insert data from one table to another

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related