[Resolved] MySQL: DELETE FROM problems

[The Hobo]

SQL Statement: DELETE FROM vbsNews_news ORDER BY postedOn LIMIT 1
Error: You have an error in your SQL syntax near 'ORDER BY postedOn LIMIT 1' at line 1

I double-checked the tablename and fieldname and they are both correct (capitalization and all). So what am I doing wrong?

I also tried throwing in a DESC/ASC and WHERE statement to see if it needed those, but no luck.

What I'm trying to do is delete ALL the records in a table EXCEPT for the last X amount (most recent X added).

So where did I screw up?

[CornedBee]

Hmm...
It looks nearly exactly like the example from the MySQL docs, except for the missing WHERE clause.

If an ORDER BY clause is used (available from MySQL 4.0), the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.

The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.

What's your MySQL version?

[The Hobo]

Hmm...3.23.something.

But I'm guessing that available from MySQL 4.0 means that ORDER BY isn't available?

If so, how can I delete all but the last 5 records added? I thought simply using LIMIT would do it, but MySQL seems to place new records before the old ones after some have been deleted.

[CornedBee]

I fear you must first write a select query:
SELECT postedOn FROM vbsNews_news ORDER BY postedOn DESC LIMIT 5
Get the earliest of those 5, put its postedOn in a variable and do the query
DELETE FROM vbsNews_news WHERE postedOn < $posted

[The Hobo]

Bugger. I really don't want to have to increase the requirements for my program and isolate users, just so I can have ORDER BY.

This is what I came up with (based on your suggestion):

// if setup to delete old items:
if ($CONFIG['arcdelete'] == 'delete') {
    // Get the last item that we want to save:
    $sql = "SELECT id FROM " . $CONFIG['tblPrefix'] . "news ORDER BY postedOn DESC ";
    $sql .= "LIMIT " . ($CONFIG['numitems'] - 1) . ", 1";
    // Note: $CONFIG['numitems'] gives number of items to save
            
    $news = mysql_query($sql) or die(displayerror(mysql_error()));
    if ($item = mysql_fetch_array($news)) {
            $ID = $item['id'];

            // Delete old items:
            $sql = "DELETE FROM " . $CONFIG['tblPrefix'] . "news WHERE id < '$ID'";
            mysql_query($sql) or die(displayerror(mysql_error()));
             
    }

    mysql_free_result($news);
} 

Is this about as efficient as I can get, or is there something else I can do to optimize it?

Thanks for your help.

[Chroder]

Mysql 4.0 is stable now and has been for some time, most hosts should have been updated by now.

[The Hobo]

Originally posted by Chroder
Mysql 4.0 is stable now and has been for some time, most hosts should have been updated by now.

Still, why alienate any users whose hosts haven't updated? AIM 5.5 has been out for some time, but it's a POS and I refuse to update from 4.8.

Okay, then...is there a way I can detect the MySQL version through PHP?

That way I can do:

if ($MySQL >= "4.0") {

} else {

}

[The Hobo]

And, by the way, my host (hostingmatters) has not updated to MySQL 4.0 yet, because many users complained of scripts (such as message boards and guestbooks) failing to work after they did update.

So they reverted back to 3.23.whatever.

[phpman]

$res  = @mysql_query("SELECT VERSION() as version") or die(mysql_error());
  $mysql_version  = preg_replace('/[\.\-a-zA-Z]/', '', mysql_result($res, 0, 'version'));

//returns it like so 3226. 

but I am confused. why don't you just keep the date in the table and delete news postings that are so many day old? seems easier to me.

[phpman]

Originally posted by Chroder
Mysql 4.0 is stable now and has been for some time, most hosts should have been updated by now.

many host have not updated. look at php, a lot of hosts are still on 4.1 or lower. they just don't see the reason to.

[Chroder]

Yeah, I realized that - was confuzzled with a previous release Many are still on 3.23.58

[The Hobo]

Originally posted by phpman

$res  = @mysql_query("SELECT VERSION() as version") or die(mysql_error());
  $mysql_version  = preg_replace('/[\.\-a-zA-Z]/', '', mysql_result($res, 0, 'version'));

//returns it like so 3226. 

but I am confused. why don't you just keep the date in the table and delete news postings that are so many day old? seems easier to me.

The option is to only show X number of news items at a time. If an option is also selected to delete old items, it deletes everything but the X number, since they are not being displayed.

And thanks for the code.

[The Hobo]

And just so you know, I do have a feature that deletes based on date. But I figured some people (who don't post news items often) might not want that, since eventually, they would have no news items if they didn't update quickly.

So I have the ability to keep (archive), the ability to delete what is not show, and the ability to delete based on age.

Any other feature ideas like those, or do I have all bases covered?

[phpman]

that explains it sounds like you have all of them.