CodeIgniter delete query does not work in foreach loop?

Question

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);
                    }   
            }

TimBrownlaw · Accepted Answer · 2022-02-19 05:19:13Z

1

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).

edited Feb 19, 2022 at 5:19

answered Feb 19, 2022 at 1:04

TimBrownlaw

5,5373 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin K Over a year ago

Thanks this worked perfectly! Swapping out where() for or_where() also works!

TimBrownlaw Over a year ago

@KevinK I added some more "Stuff" in my answer. I'll leave it at that...

Kevin K · Accepted Answer · 2022-02-19 02:48:18Z

0

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);
}

answered Feb 19, 2022 at 2:48

Kevin K

1511 silver badge12 bronze badges

Collectives™ on Stack Overflow

CodeIgniter delete query does not work in foreach loop?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related