[SOLVED] Db related question

[mike1313]

OK so here's my question.

 

If I have 50 rows in my table.

 

How would I go about writing a little code that will find the first 25 rows and delete them?

[revraz]

What do you consider the first 25 rows?  Is there a id or primary key you want to do it by?

[mike1313]

The primary key is id but let me better explain I want to delete all but the last 25 rows. So if I were to have 100 rows I would want only rows 75-100 remaining.

[kjtocool]

It all depends on the ID.

 

Assuming you used an auto_incrementing ID, starting at 1, which also serves as a primary key, and you know the ID you want to not delete, you could do something like this:

 

DELETE FROM table_name

WHERE column_id > 74

 

If you don't know the ID, you should first do a statement to select the last inserted value, then subtract 26.

[cooldude832]

<?php
$q = "Select count(Id) from `Table` Order By ID DESC";
$r = mysql_query($q) or die(mysql_error());
$records = mysql_result($r, 0);
$save = 25;
$limit = $records-$save;
$q = "Select Id from `Table` Order By ID ASC Limit ".$limit;
$r = mysql_query($q) or die(mysql_error());
if(mysql_num_rows($r) >0){
$q = "Delete from `Table` Where ";
$comma = 0;
while($row = mysql_fetch_array($r)){
    if($comma == 0){$comma = 1;}
    else{$q .= " ||";}
    $q .= "Id = '".$row['Id']."' ";
}
$r = mysql_query($q) or die(mysql_error());
echo mysql_affected_rows($r)." Records Deleted.";
}
?>

untested but should be okay

 

[fenway]

That would be quite slow.... why not flip it upside down and use a LIMIT OFFSET to skip the first 25 (which represent the last 25)?  Of course, you'll need some sort of order by clause.

 

@vcooldude832: Oh, and it's 1,2,3-trimethylbenzene a.k.a. hemellitol.

[cooldude832]

not really that slow, it ain't pulling a ton of a data, so long as it isn't deleting  more than a few hundred records for being a maintenance hting its not horrible, but could be better.

[kjtocool]

Or

 

<?php
$databaseConnect = mysqli_connect("localhost", "username", "password", "database");
$result = mysqli_query($databaseConnect, "SELECT LAST_INSERT_ID()");

$last_row_to_delete = $result - 26;
$query = "DELETE FROM table_name WHERE column_id >= $last_row_to_delte";

if (mysqli_query($databaseConnect, $query))
     echo "Success";
else
     echo "Failure";
?>

 

[toplay]

I agree with fenway.

 

If this doesn't work:

 

DELETE FROM table_name

WHERE id < (SELECT id FROM table_name ORDER BY id DESC LIMIT 24,1)

;

 

Split it into two queries.

 

Get the id of the 25th row from the end of the table:

 

SELECT id FROM table_name ORDER BY id DESC LIMIT 24,1

;

 

Then use that value returned by the above select to delete all rows that have an ID less than that. Which will keep the last 25 rows in the table:

 

DELETE FROM table_name

WHERE id < value_from_above_query

;

 

 

[kjtocool]

If the table is auto_incremented, does it not make more sense to simply get the last incremented value, and use it to get the exact values to not delete?

[toplay]

If the table is auto_incremented, does it not make more sense to simply get the last incremented value, and use it to get the exact values to not delete?

 

That would work only if the ID's are in sequence with no gaps (deletes). My last post works either way because it does not rely on the assumption that the auto_incrmented ID's are always in sequence (with no gabs).

 

I intentionally see what the 25th row from the end ID really is, then use that value to delete everything less than that ID value.

 

Let's say there are these last 10 row ID's in a table:

100

98

97

96

90

89

87

86

85

84

 

Notice there have been some rows deleted (88, 91-95, 99). If I wanted to delete everything except the last 5 rows, then taking highest ID 100 - 5 rows to keep will give me ID 95, which if I delete everything less than that (95) will leave me with 4 rows (96-98, 100) left in table instead of the 5 last rows I really want left. See the problem now? The approach I posted would grab the 5th row from the end (ID 90) and delete everything less than that leaving exactly the last 5 rows (90, 96-98, 100).

 

EDIT:

 

kjtocool, the poster, mike1313, wants to keep the last 25 rows in a table and I think you have it backwards with showing code to delete based on greater than or equal to (>=).

 

 

 

 

[kjtocool]

The primary key is id but let me better explain I want to delete all but the last 25 rows. So if I were to have 100 rows I would want only rows 75-100 remaining.

 

I was going on what he said here, where he makes it sound like he wants only the last 25 rows.  You are right though, my code only is faulty.

[mike1313]

Yeah but what would be the best way to get the value because I've tried mysql_result(); and it doesn't seem to work correctly.

[toplay]

Yeah but what would be the best way to get the value because I've tried mysql_result(); and it doesn't seem to work correctly.

 

Let the manual be your friend...use one of these fetches:

 

http://us2.php.net/manual/en/function.mysql-fetch-assoc.php

 

http://us2.php.net/manual/en/function.mysql-fetch-array.php

 

http://us2.php.net/manual/en/function.mysql-fetch-object.php

 

There's examples in the manual pages and in this forum post.