Delete Order By?

[augathra]

Hello, I wanted to DELETE rows from my table in order by their ID. If its possible...

Here's what I tried, my error said it was around 'ORDER BY message_id ASC LIMIT 23'

My query:
mysql_query("DELETE FROM messages ORDER BY message_id ASC LIMIT '$subtraction'") or die(mysql_error() . $error);

It's supposed to delete about 20-25 rows.

Thanks

[augathra]

ohhh, i believe its gotta be in a while loop...but im not sure how to set it up....so if neone wants to..feel free.

[markl999]

The syntax for a delete is:
DELETE FROM foo WHERE something=whatever

So if you want to delete the rows with id's 1-23 then you could do :
DELETE FROM messages WHERE message_id < 24

[augathra]

I tried this, but no success (still get the same error, can DELETE queries be ordered?):

$SQL = "DELETE FROM messages WHERE message_id > '0' ORDER BY message_id ASC LIMIT 1";
while($num <= 25 && $num > 0) {
mysql_query($SQL) or die(mysql_error() . $error);
$num = $num -1;
} 

[markl999]

No, you can't order by deletions, there would be no point .. they are just being deleted not returned

[augathra]

Do you know how I could select the highest id?

[markl999]

You can use MAX() to get the highest id, eg:
SELECT MAX(member_id) AS themax FROM members;

[r937]

No, you can't order by deletions

actually, you can

admittedly, this is egregiously non-standard sql, but still, i can see the logic behind why they did it

If the DELETE statement includes an ORDER BY clause, the rows are deleted in the order specified by the clause. This is really useful only in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them in timestamp order, and deletes the first (oldest) one:

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

ORDER BY can be used with DELETE beginning with MySQL 4.0.0.

-- http://dev.mysql.com/doc/mysql/en/DELETE.html

[someonewhois]

Woah, funkah. There's a new one. You can tell why he's the only nominee/winner for the database award

[augathra]

Thats basically what I have isnt it?

[someonewhois]

Do you have MySQL 4?

[markl999]

You are still missing the WHERE clause.
/me goes to read the strange DELETE ORDER BY syntax

[r937]

are you on at least 4.0.0?

[augathra]

GRR!!! MySQL version is 3.25 or so.... BTW is this really old? or moderately old....lol

[someonewhois]

Moderately old. I think I might still have 3.23 on my development localhost, my server has 4.1 though. Are you on a shared host or just testing locally?

[augathra]

shared =(, I like my provider, they have excellent support (apollohosting.com). I may be switching in a few months when shared wont be able to support my site.

Thank you for all the replies, everyone.

[someonewhois]

If you need reliable hosting, with good support, you can PM me. (Whenever you're ready to switch). It's a dedicated server with just me and a few friends on it, and I answer e-mail support myself - without the first-level BS.

If this helped, why not vote for me?

[augathra]

Does your server have:

RedHat Linux
10+ GB Disk Space
150-500 GB Transfer
PHP 4.3.4
MySQL - 4.0+ preferred, 3.25 will work
At least 3 MySQL DBs (with lots of storage per)
Webmail - "Unlimited" email accounts
Cpanel
phpMyAdmin
Unlimited Subdomains
Daily Backups
Dedicated IP address
More or so the basic things, like CRON & FTP access

lol

[augathra]

Anyone of you know JS? I have a <div> with overflow thats supposed to scroll to the bottom. It goes to the bottom sometimes, and sometimes it goes to the middle. Its a chat script i'm working on.

If anyone thinks they could help, please tell me and i'll PM you the URL to the chat. (You will need to make an account, its free neways)