CODEWITHSUNDEEP
phpmysqlsql
Hong
Hong

Reputation: 67

PHP Insert data from one table to another

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

Upvotes: 1

Views: 13365

Answers (2)

Mr. DOS
Mr. DOS

Reputation: 433

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

Upvotes: 4

Dan Bracuk
Dan Bracuk

Reputation: 20794

This can be done with straight sql.

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

Upvotes: 2

Related Questions