5 replies to this topic

[gnomme]

Hi Guys!

I need to do a PHP script that moves data between to tables. When Table1 has more than 10 rows, php moves those extra rows to another table, deleting them from its source.

Something like this:

while (rows on table1 > 10 ){
copy row to table2;
delete row;
}

Can anyone help me please? Thanks

[BlaineSch]

Well you'd need to either use a trigger or run a bit of code often. Note this is untested.

<?PHP
// connect here
// You will need to edit the query to match the correct criteria since you didn't provide any details I made them up
$results = mysql_query("SELECT id, name, email FROM users WHERE id > 10");
while($row=mysql_fetch_assoc($results)) {
    mysql_query("INSERT INTO usersBackup (name, email) VALUES ('{$row['name']}','{$row['email']}')");
    mysql_query("DELETE FROM users WHERE id={$row['id']}");
}

[gnomme]

BlaineSch said:

Well you'd need to either use a trigger or run a bit of code often. Note this is untested.

<?PHP

// connect here

// You will need to edit the query to match the correct criteria since you didn't provide any details I made them up

$results = mysql_query("SELECT id, name, email FROM users WHERE id > 10");

while($row=mysql_fetch_assoc($results)) {

    mysql_query("INSERT INTO usersBackup (name, email) VALUES ('{$row['name']}','{$row['email']}')");

    mysql_query("DELETE FROM users WHERE id={$row['id']}");

}

This does not work. The ID is incremental...

[BlaineSch]

I mentioned you'd need to change it a bit depending on how you had your data. If 'id' is all you have to go by, I'd select them all and don't do it for the first 10th items.

<?PHP
// connect here
// You will need to edit the query to match the correct criteria since you didn't provide any details I made them up
$results = mysql_query("SELECT id, name, email FROM users ORDER BY id DESC");
$i = 0;
while($row=mysql_fetch_assoc($results)) {
    if($i >= 10) {
        mysql_query("INSERT INTO usersBackup (name, email) VALUES ('{$row['name']}','{$row['email']}')");
        mysql_query("DELETE FROM users WHERE id={$row['id']}");
    }
    $i++;
} 

[grisha]

If extensive PHP use is not required you can do it in two queries. One will copy all the data to the other table using the INSERT ... SELECT MySQL statement. Then simply truncate, or drop the old table if it isn't used.

[gnomme]

nop...I only want to delete the rows that exceeds my condition ( more than 10)