1

I am trying to move all data from one table to another and that bit on it's own works fine, the part I am having trouble with is then deleting the data from the old table. The MYSQL query I hvae put together runs fine as an SQL query on PHPMyAdmin but for some reason gives me an error about syntax at line 2 if i run it from my webpage. Please see below for the query from the website.

if($old == 'Yesterday'){
$movequery = "INSERT INTO yesthistory select * from history; 
TRUNCATE TABLE history";

if (!mysqli_query($con2, $movequery))
{
    echo("Error description: " . mysqli_error($con2));
}
  • 1
    `mysqli_query()` can only preform one query at a time. You're doing two `INSERT` and `TURNICATE`. – Qirel Apr 08 '17 at 07:09
  • You can create a `TRIGGER` or use `mysqli_multi_query` or loop the queries. Food for thought: http://stackoverflow.com/questions/14715889/strict-standards-mysqli-next-result-error-with-mysqli-multi-query/22469722#22469722 – mickmackusa Apr 08 '17 at 07:40

2 Answers2

0

It is because of you are performing 2 operations seperated by semiclon in a single query
query should be this

$movequery1 = "INSERT INTO yesthistory select * from history";
$movequery2 = "TRUNCATE TABLE history";

then run these queries one by one.

Tanuj Yadav
  • 1,259
  • 13
  • 21
0

use the event scheduler to run a job every day, based on the WHERE clause in your statement:

CREATE EVENT archive_history 
   ON SCHEDULE EVERY 1 DAY
   COMMENT 'archives claims older than their expiry date'
DO 
BEGIN
   INSERT INTO yesthishistory SELECT * FROM history WHERE ....
   DELETE FROM history WHERE ....
END

If it only runs once per day (because you don't have any finer time resolution in your expiry logic), you won't have massive performance slow-down every time you run a modification.

Pradeep
  • 39
  • 9