Codeigniter cannot delete rows from database table using join method

Question

I want to delete from ‘table1’ those rows where (user_id = 5) but I should check if those posts’ (title = title1 in table2). I use Codeigniter and I get this error while trying to delete: ‘Deletes are not allowed unless they contain a "where" or "like" clause.’ Could you please help me to check what is wrong with my code below.

table1:

enter image description here

table2:

enter image description here

public function delete($title, $user_id){ 

    $this->db->select('table1.*');
    $this->db->from('table1','table2');   
    $this->db->where('table1.user_id', $user_id); 
    $this->db->where('table2.title', $title);
    $this->db->join('table2','table1.post_id=table2.post_id');

     $query = $this->db->get();   

        if ($query && $query->num_rows() > 0) {

    $this->db->delete('table1.*');
    $this->db->from('table1','table2');   
    $this->db->where('table1.user_id', $user_id); 
    $this->db->where('table2.title', $title);
    $this->db->join('table2','table1.post_id=table2.post_id');
    return true;
            } 
    else {
    return false;
    }

   }

Use subqueries: http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record — Joel Harkes, Oct 27 '14 at 14:13

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

Make use of subqueries.

example

#Create where clause
$this->db->select('id');
$this->db->from('table2');
$this->db->where('table2.title', $title);
$where_clause = $this->db->get_compiled_select();

#Create main query
$this->db->where('table1.user_id', $user_id); 
$this->db->where("`id` NOT IN ($where_clause)", NULL, FALSE);
$this->db->delete('table1');

References

stolen from here: https://stackoverflow.com/a/16303021/1275832
about subqueries: http://www.mysqltutorial.org/mysql-subquery/
compiled select: https://github.com/NTICompass/CodeIgniter-Subqueries

edited May 23 '17 at 12:16

Community

1
1

answered Oct 27 '14 at 14:16

Joel Harkes

10,975
3
46
65

Thanks for your answer. Is there another way to to do it? because I did not find get_compiled_select() method in Codeigniter documentation. – EducateYourself Oct 27 '14 at 15:24

score 0 · Answer 2 · answered Oct 28 '14 at 09:54

After running first query you will get the set of user that you have to delete. Run this set throw foreach loop for getting id of user and posts that you have to delete to an array.

  $user_array = array();
  $post_array = array();

  foreach($query->result() as $query) 
  {
      $user_array[$query->user_id] = $query->user_id;
      $post_user[$query->post_id] = $query->post_id;
  }

And then

  this->db->where_in('user_id', $user_array)->delete('table1');
  this->db->where_in('post_id', $post_array)->delete('table2');

I know that this is not the best decision. But i think that this is the most understandable.

score 0 · Answer 3 · answered Jan 08 '17 at 14:04

You may use the following code to delete data from tables becasue codeigniter ignore join when you delete multiple data from multiple tables.

$sql = "DELETE t1 FROM table1 t1
  JOIN table2 t2 ON t1.thing_id = t2.id
  WHERE t2.otherthing_id = ?";
$this->db->query($sql, array($id));

score 0 · Answer 4 · answered Jan 08 '17 at 14:42

JOINS are used to fetch data from database not used to delete data if you want to delete data from multiple table using single query then you need to use cascading in MySQl using that you can delete data also from other tables that are related to current table.

Codeigniter cannot delete rows from database table using join method

4 Answers4

example

References

Linked