SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Delete problem

  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Montgomery, TX
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete problem

    I've got a problem with a delete operation and I can't figure it out. I've got a table called "online" and I store the visitor's ip each time a page is loaded. This code finds users that have not had activity in the last hour and attempts to delete their record. For some reason, the delete isn't working, but isn't giving an error either. Inside of the if statement, I can see that the correct records are trying to be deleted and I can even take the sql statement into phpMyAdmin and run it and the record will be deleted. Anyone see what might be wrong here?

    Thanks,

    Dave

    PHP Code:
      require "db.php";
      
    $connections mysql_query("SELECT * FROM online");
      while (
    $connection mysql_fetch_array($connectionsMYSQL_ASSOC)) {
        
    $id $connection["id"];
        
    $ip $connection["ip"];
        
    $last_activity $connection["last_activity"];

        
    $date date("YmdHis");
        
    $timeDiff $date $last_activity;

        if (
    floor($timeDiff/60) >= 60){
          
    $result mysql_query("DELETE FROM online WHERE id = $id");
          if (
    $result == "false") echo "error";
        }
      } 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it appears you are selecting the entire table, then looping through it and issuing a delete for each row that needs to be deleted

    this is inefficient in two ways: returning more rows than you need, and issuing a database command inside a loop

    the better approach would be to just issue one single database command that deletes all the rows, and only the rows, that you need deleted

    Code:
    delete from online
     where last_activity <= date_sub(now(), interval 1 hour)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Montgomery, TX
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    I can't believe I was doing that. Thanks for straightening me out!

    David


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
  •