[RESOLVED] Weird

[..:RUDI:..]

Basically I want to prune a table in the database, leaving only the top 5 posts.

Doing this:

mysql_query("DELETE FROM shoutbox LIMIT 5, 500"); 

Errors this:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 500' at line 1

Is there a certain way to prune a table or does anybody have an answer??

Rudi

[..:RUDI:..]

That code actually wouldn't work anyway, so yeah, anyone have an idea of how to do this??

The rows I want to keep would be the bottom 5.

[..:RUDI:..]

Oooh Oooh not so resolved anymore...

[..:RUDI:..]

This doesn't work:

mysql_query("DELETE FROM shoutbox WHERE date NOT IN (SELECT date FROM shoutbox ORDER BY date DESC LIMIT 5)"); 

and it returns this:
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Only problem is... that's exactly what I want it to do!!

[CornedBee]

Do you have any control over the server? Perhaps you can upgrade MySQL. This kind of subquery is supported starting with version 4.1, which is really stable.

If not, you'll have to first perform the select, and then build a list of IDs in PHP code, then create the delete query from that. In that case, I'd recommend using NOT IN and fetching the IDs of the entries you want to keep - it'll keep the list shorter.

[..:RUDI:..]

Do you have any control over the server? Perhaps you can upgrade MySQL. This kind of subquery is supported starting with version 4.1, which is really stable.

Hmm - MySQL - 4.1.19-standard-log

[CornedBee]

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html says:
"Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific."

http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

Hmm, perhaps the combination with LIMIT is not supported. I cannot find any mention of this in the docs, though. But I found this:
"In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);"

http://dev.mysql.com/doc/refman/4.1/...trictions.html

Perhaps doing it in PHP code is indeed the simplest way.

[..:RUDI:..]

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html says:
"Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific."

http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

Hmm, perhaps the combination with LIMIT is not supported. I cannot find any mention of this in the docs, though. But I found this:
"In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);"

http://dev.mysql.com/doc/refman/4.1/...trictions.html

Perhaps doing it in PHP code is indeed the simplest way.

Hmm, would you have any code that could do this??

[CornedBee]

No, but the pseudo PHP code would look like this:

$sql = "SELECT id FROM ... WHERE you want to keep the row";
$ids = execute_sql_and_fetch_each_id_into_an_array($sql);
$idlist = join(', ', $ids);
$sql = "DELETE FROM ... WHERE id NOT IN ($ids)";
execute($sql);

[..:RUDI:..]

static $i = 1;
$sql = mysql_query("SELECT * FROM shoutbox ORDER BY date DESC LIMIT 5");
while ($record = mysql_fetch_assoc($sql) ) {
    $i++;
    $ids[$i] = $record['date'];
} 
$idlist = join("', '", $ids);
$sql1 = "DELETE FROM shoutbox WHERE date NOT IN ('".$idlist."')";
mysql_query($sql1); 

Thanks

[CornedBee]

Small modification.

$sql = mysql_query("SELECT * FROM shoutbox ORDER BY date DESC LIMIT 5");
$ids = array();
while ($record = mysql_fetch_assoc($sql) ) {
    $ids[] = $record['date'];
} 
$idlist = join("', '", $ids);
$sql1 = "DELETE FROM shoutbox WHERE date NOT IN ('".$idlist."')";
mysql_query($sql1); 

[..:RUDI:..]

Small modification.

$sql = mysql_query("SELECT * FROM shoutbox ORDER BY date DESC LIMIT 5");
$ids = array();
while ($record = mysql_fetch_assoc($sql) ) {
    $ids[] = $record['date'];
} 
$idlist = join("', '", $ids);
$sql1 = "DELETE FROM shoutbox WHERE date NOT IN ('".$idlist."')";
mysql_query($sql1); 

Tried that but for some reason it just kept printing Array, Array, Array etc.

[CornedBee]

Huh? That shouldn't happen. What do you mean, "printing"?

Another modification:

"SELECT date FROM shoutbox ..."

No need to transfer more data from the DB to the web server than necessary.