How to delete multiple MySQL rows...?

[chappill]

PHP Code:

 $slavesel = mysql_query("SELECT * FROM `slaves`");
$result = mysql_num_rows($slavesel);
$randomdel = rand(2,$result);

</div> Is some code I have for selecting the rows from a table, counting them and randomizing them between 2 and the result.
What I have after that is:

<div class="code_header">PHP Code:

$delete = mysql_query("DELETE FROM slaves AMOUNT '$randomdel'");

But this doesn't work... It was a guess at it actually happening and much to my non suprisment... It didn't work. Is there anything like this that may work (Something that will delete an amount of rows randomly so instead of starting at 1 all the time it may start at row 34288 if there were that many).
I was thinking keep what I have at the top then below that have:

<div class="code_header">PHP Code:

$rand2 = rand(1,$randomdel);

Then having:

<div class="code_header">PHP Code:

$delete = mysql_query("DELETE FROM slaves LIMIT $rand2, $randomdel");

All help will be much appreciated!

 

[sunils]

do you want to randomly delete the rows? and how much rows you want to delete. Sorry i cant understand your question.

 

[scopey]

I'm also lost... If you want to delete rows 1 to random number, you can use "DELETE * FROM slaves LIMIT 1, {$randomdel}" where the first number after the 'LIMIT' indicates the start, and the second indicates the amount.

 

[chappill]

I need it to randomly delete a random amount of rows... At the moment I now have:
$delete = mysql_query("DELETE FROM `slaves` LIMIT {$rand2},{$randomdel}");
Which seams to be working, if it works tomorrow I'll award scopey with 250 credits =)

Not working >.< please help...

 

[chappill]

Is this even possible?

 

[Salvatos]

I think that should work but I have no guarantee.

// First get the number of rows you have
$slavesel = mysql_query("SELECT * FROM slaves");
$result = mysql_num_rows($slavesel);
$randomdel = rand(2,$result); // Select your random amount of rows to delete
$limit_top = $result; // Set your upper limit
 
// The first row to delete is selected randomly
$limit1 = rand(0,$limit_top);
 
// If the amount of rows to delete added to the first limit exceeds the upper limit, set the upper limit as the last row to delete
if ($limit1 + $randomdel > $limit_top) {
$limit2 = $limit_top;
}
else {
$limit2 = $limit1 + $randomdel;
}
 
// Then proceed to deletion
$delete = mysql_query("DELETE FROM slaves LIMIT '$limit1','$limit2'");

PLEASE NOTE
I'm not sure but I think this line should be changed since MySQL begins counting at zero, but I'd like to have someone else confirm.

$limit_top = $result; // Set your upper limit

$limit_top = $result - 1; // Set your upper limit

 

[phpasks]

best option truncate table means all data lost in one query - load time minimum.

TRUNCATE TABLE tablename;

 

[natsuki]

You could use salvatos code and then have an iterator like $rows = total number of rows to delete
for ($i = 0; $i < $rows; i++; ) one then put the code in it where it will randomly choose rows to delete and do the deletion. If you truncate table though the whole table is dropped.

 

[seanliu]

i think you have to specify the row(s) to be deleted using the "where" command

$delete = mysql_query("DELETE FROM slaves WHERE id>$randomdel");

or something like that