Save a copy of records before truncate

[pilotjourney]

I am trying to save a copy of the records in tables_71 before I remove them and when the following executes it truncates tables_71 but does not save the records into tables_73

any ideas? Thank you.


$sql = "INSERT INTO 'mos_philaform_tables_73' SELECT * FROM 'mos_philaform_tables_71'";

mysql_query($sql);

$sql = "TRUNCATE TABLE `mos_philaform_tables_71`";
mysql_query($sql);

[markl999]

Probably down to your quotes:

$sql = "INSERT INTO 'mos_philaform_tables_73' SELECT * FROM 'mos_philaform_tables_71'";

Should be:
$sql = "INSERT INTO `mos_philaform_tables_73` SELECT * FROM `mos_philaform_tables_71`";

or just:
$sql = "INSERT INTO mos_philaform_tables_73 SELECT * FROM mos_philaform_tables_71";

[kleineme]

Does mos_philaform_tables_73 already exist? Put a
echo mysql_error();
after mysql_query and see what it says.

[pilotjourney]

Yes mos_philaform_tables_73 does exist.

When I ran the correct syntax it did add the record to _tables_73 - but when it ran again it did not add another record to _tables_73

Each time the script runs I need records to be added to _tables_73 - does the insert statement do that .. I thought it did

[kleineme]

What does mysql_error say?

Do you try to insert the same records over and over again? If you have a PRIMARY KEY on your table then the INSERT will fail because it tries to insert keys that already exist. You may try to use

INSERT IGNORE INTO mos_philaform_tables_73 SELECT * FROM mos_philaform_tables_71;

Or you could put a

TRUNCATE TABLE mos_philaform_tables_73;

in front of your insert.

[pilotjourney]

Well the strangest thing is that this code does not always seem to work. Which of course means something is going on that I am missing.

$sql = "INSERT INTO mos_philaform_tables_73 SELECT * FROM mos_philaform_tables_71";

mysql_query($sql) or print(mysql_error());

$sql = "TRUNCATE TABLE `mos_philaform_tables_71`";
mysql_query($sql) or print(mysql_error());

The truncate part works but the insert does not work. But it did a couple times.

[kleineme]

You have to truncate the table before the INSERT. Your INSERT fails because you try you insert records with primary keys which are already there. TRUNCATE empties your table so that afterwards you can INSERT anything you want.

[pilotjourney]

but if I trucate first there will not be any records to insert? Maybe change the primary key on the other table?

[kleineme]

You have to TRUNCATE the table in which you want to insert rows, which is, of course, mos_philaform_tables_73!