move data from table to another table

[vietboy505]

I want to move the row from OLD TABLE to NEW TABLE where:
the OLD TABLE, status == 'S' & the dateModify is over a week, move that row (all data to the NEW TABLE).
Then delete that row on the OLD TABLE.
I try the code below, nothing happen, no data is output. I try to change to +1 day, it's still same but it should display the data a day old.

How can I fix that?


<?php include("config.php"); ?>
<?php
//INFO from mySQL
//id INT NOT NULL AUTO_INCREMENT,
//PRIMARY KEY(id),
//name VARCHAR(30),
//status VARCHAR(1),
//dateModify TIMESTAMP,
$overWeek=date('Y-m-d H:i:s', strtotime('+1 week'));
$statusCheck="S";
mysql_select_db($dbnameNAME) or die(mysql_error());

$result = mysql_query("INSERT INTO $tableNEW
(name, status, dateModify)
SELECT name,status,dateModify FROM $tableOLD WHERE status = '$statusCheck' AND dateModify >= '$overWeek' " )
or die($errCon . mysql_error());

?>

<?php

mysql_select_db($dbnameNAME) or die(mysql_error());
$result2 = mysql_query("SELECT * FROM $tableNEW ")
or die(mysql_error());

while($row2 = mysql_fetch_array($result2)) {

echo $row2['name'];
echo $row2['status'];
echo $row2['dateModify'];

}

//free up memory
mysql_free_result($result2);
?>

[vietboy505]

Here is the new code, but it doesn't work;


<?php include("config.php"); ?>
<?php
$overWeek=date('Y-m-d H:i:s', strtotime('-1 week'));
$statusCheck="S";
mysql_select_db($dbnameNAME) or die(mysql_error());

$result = mysql_query("INSERT INTO $tableNEW
(name, status, dateModify)
SELECT name,status,dateModify FROM $tableOLD WHERE status = '$statusCheck' AND dateModify >= '$overWeek' " )
or die($errCon . mysql_error());

?>

<?php

mysql_select_db($dbnameNAME) or die(mysql_error());
$result2 = mysql_query("SELECT * FROM $tableNEW ")
or die(mysql_error());

while($row2 = mysql_fetch_array($result2)) {

echo $row2['name'];
echo $row2['status'];
echo $row2['dateModify'];

}

//free up memory
mysql_free_result($result2);
?>


I change $overWeek=date('Y-m-d H:i:s', strtotime('-1 week')); . It did show some data.

But I want all data before that $overweek, not after, I try the less equal sign. It doesn't work.
And how I would delete that row from the old table once done insert into a the new table?

[alexpayne]

completly untested. but maybe this will help.

<?php include("config.php"); ?>
<?php
$overWeek=date('Y-m-d H:i:s', strtotime('-1 week'));
$statusCheck="S";
mysql_select_db($dbnameNAME) or die(mysql_error());

//NEW CODE

$query = mysql_query("SELECT * FROM tableold WHERE status = '$statusCheck' AND dateModify >= '$overWeek'") or die(mysql_error());
while($row = mysql_fetch_array($query)){
$name = $row["name"];
$status = $row["status"];
$dateModify = $row["dateModify"];
$query2 = mysql_query("INSERT into tablenew (name,status,dateModify) values ('$name', '$status', '$dateModify')") or die(mysql_error());

//DELETE THE OLD ROW? option 1 (this would be safer)
if($query2){
$id = $row["id"]; //replace this with the unique field in tableold
mysql_query("DELETE FROM tableold WHERE No = $id") or die(mysql_error());
}
//^^^^^^^^^^^^^^^^^^^
}
//DELETE THE OLD ROWS? option 2
mysql_query("DELETE FROM tableold WHERE status = '$statusCheck' AND dateModify >= '$overWeek'") or die(mysql_error());
//^^^^^^^^

?>

<?php

mysql_select_db($dbnameNAME) or die(mysql_error());
$result2 = mysql_query("SELECT * FROM $tableNEW ")
or die(mysql_error());

while($row2 = mysql_fetch_array($result2)) {

echo $row2['name'];
echo $row2['status'];
echo $row2['dateModify'];

}

//free up memory
mysql_free_result($result2);
?>