CodeIgniter delete query does not work in foreach loop?-CodePudding

I have the following code to remove user accounts that have not verified their email address within one week. The query works as I expect, but the delete function only deletes the first record, and I cant figure out why. The print_r generates the full results as I expect, but the delete function is somehow ignored. Any ideas on how to make it so it deletes all relevant records?

public function databaseupdate ()  //REMOVES ALL UNVERIFIED EMAIL ACCOUNTS OLDER THAN 1 WEEK - REQUIRES MANUAL BUTTON PRESS BY ADMIN
            {
                $verified = 'no';
                $tables = array('register', 'profiles', 'profilesmember');
                $deletedate = date('Y-m-d', strtotime('-7 days'));
                $this->db->select('email');
                $this->db->where('is_email_verified', $verified);
                $this->db->where("DATE_FORMAT(created_date,'%Y-%m-%d') <='$deletedate'");
                $this->db->from('register');
                $query = $this->db->get();
                        
                foreach($query->result() as $row)
                    {   
                        $email = $row->email; 
                        $this->db->where('email', $email);
                        $this->db->delete($tables);
                    //  print_r($email);
                    }   
            }

CodePudding user response：

You are looping on the WHERE statement which will work for the first instance as you have observed, but subsequent ones, it will not, as you are adding WHERE statements like

Loop 1: WHERE email = email1

Loop 2: WHERE email = email1 AND email = email2

Loop n: WHERE email = email1 AND email = email2 AND ..... email = emailn

and it will just keep on building. Its the very same behaviour as you have used in your query above with the multiple WHERE statements.

So you need to perform a reset on the query.

$this->db->reset_query();

So your loop might look like...

foreach($query->result() as $row)
{   
    $this->db->where('email', $row->email);
    $this->db->delete($tables);
    $this->db->reset_query();  
}

Then you will get what you are expecting.

Loop 1: WHERE email = email1

Loop 2: WHERE email = email2

Loop n: WHERE email = emailn

While the above "Works" you should be considering what is you are trying to achieve here...

You want to create a list of Emails that you want to delete from a number of tables.

Just NOTE: We have no idea on how these tables are related etc so just be aware of the possibility of leaving "orphan" records. There are many ways to deal with this but its way outside this discussion.

An Option is to check you have some results, build the where query then perform the delete. So it's only "1 Trip" to the Database" and not "n Trips".

// Did we get any results to delete?
if($query AND $query->num_rows()) {

  foreach($query->result() as $row)
  {   
    // Build the WHERE Statement
    $this->db->or_where('email', $row->email);
  }
  $this->db->delete($tables);  // Execute the Delete.
}

So it's important to understand the various ways to do this... I.E there are others. But for this case, it should be fine (famous last words).

CodePudding user response：

Further to TimBrownlaw's answer using or_where also works:

foreach($query->result() as $row)
{   
    $this->db->or_where('email', $row->email);
    $this->db->delete($tables);
}