SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete Order By?

    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

  2. #2
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this, but no success (still get the same error, can DELETE queries be ordered?):
    PHP Code:
    $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;


  5. #5
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, you can't order by deletions, there would be no point .. they are just being deleted not returned

  6. #6
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you know how I could select the highest id?

  7. #7
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use MAX() to get the highest id, eg:
    SELECT MAX(member_id) AS themax FROM members;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by markl999
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Woah, funkah. There's a new one. You can tell why he's the only nominee/winner for the database award

  10. #10
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats basically what I have isnt it?

  11. #11
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have MySQL 4?

  12. #12
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are still missing the WHERE clause.
    /me goes to read the strange DELETE ORDER BY syntax

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are you on at least 4.0.0?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GRR!!! MySQL version is 3.25 or so.... BTW is this really old? or moderately old....lol

  15. #15
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  16. #16
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  17. #17
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  18. #18
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  19. #19
    SitePoint Guru augathra's Avatar
    Join Date
    Jul 2004
    Location
    united states
    Posts
    826
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •