CODEWITHSUNDEEP
phpmysqlsql
Kevin_TA
Kevin_TA

Reputation: 4675

Copy Fields From One Table to Another

I am looping through a set of values $id and on certain occasions I need to copy 2 fields of data from one table (product) to another (product_new). Both tables already exist and both contain identical fields 'product_description' and 'image'. I am trying to do this with:

$copy_query = "SELECT product_description, image INTO product_new FROM product WHERE product_id=$id";
$result = mysql_query($copy_query) or die('Could not copy the database data: ' . mysql_error() .  '<br>Query: ' . $copy_query);

The error is 'Undeclared variable: product_new'. I don't understand this though because I perform other operations with table product_new without issue. Any thoughts? Thanks.

UPDATE

Thanks for the link to the differences doc. That definitely helped, however I have one more issue. My current query is:

$copy_query = "INSERT INTO product_new (product_description, image)
    SELECT product.product_description, product.image
    FROM product
    WHERE product_id=$id";

but this is writing the data to a new row with product_id = 0. I need it to write to the row with product_id = $id.

Upvotes: 0

Views: 2140

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

MySQL doesn't support the SELECT INTO syntax.

Also, it's generally very inefficient to run queries inside of a loop. Instead, you can make a comma separated list of the ids in PHP and use:

$copy_query = '
    INSERT INTO product_new (product_description, image)
    SELECT product_description, image
    FROM   product
    WHERE  product_id IN (' . implode(',', $ids) . ')';

mysql_query($copy_query);

Where $ids is the array of your ids.

This solution needs only to be executed once (rather than many times within a loop).

EDIT: If you already have rows that exist in product_new and you want to update them, you can use:

$copy_query = '
    UPDATE product_new a
    JOIN   product b ON a.product_id = b.product_id
                    AND b.product_id IN (' . implode(',', $ids) . ')
    SET    a.product_description = b.product_description,
           a.image = b.image';

mysql_query($copy_query);

Upvotes: 5

drew010
drew010

Reputation: 69927

The reason is because MySQL doesn't support SELECT...INTO queries.

Instead use INSERT INTO...SELECT to achieve the same result.

See SELECT INTO Table Differences on the reference manual for more information.

Upvotes: 4

Related Questions