Codeigniter 3 blogging application: whenever a category is deleted, set cat_id of all posts in that category to 1

Question

I am working on a basic blog application with Codeigniter 3.1.8 and Bootstrap 4.

There is a posts and a categories table. At the moment, the application inserts a category id into the posts table, whenever a post is created (you can not add a post before choosing a category for it), but I have set no foreign key relationship between the two tables (for ease of development) and no cascading.

Now I want to set a special cascading relationship between the two tables: whenever a category is deleted, all posts in that category should have 1 in the cat_id column.

I have created an installation process for the application: after creating a database and providing its credentials to the application/config/database.php file, you can run the Install controller which will create all the necessary tables:

class Install extends CI_Controller {
    public function __construct()
    {
        parent::__construct();
    }

    public function index(){
        // Create all the database tables if there are none
        // by redirecting to the Migrations controller
        $tables = $this->db->list_tables();
        if (count($tables) == 0) {
            redirect('migrate');
        } else {
            redirect('/');
        }
    }
}

The migrations I use to create the posts table are:

public function up(){
  $this->dbforge->add_field(array(
  'id'=>array(
    'type'=>'INT',
    'constraint' => 11,
    'unsigned' => TRUE,
    'auto_increment' => TRUE
  ),

  'author_id'=>array(
    'type'=>'INT',
    'constraint' => 11,
    'unsigned' => TRUE,
  ),

  'cat_id'=>array(
    'type'=>'INT',
    'constraint' => 11,
    'unsigned' => TRUE,
  ),

  'title'=>array(
    'type'=>'VARCHAR',
    'constraint' => 255,
  ),

  'slug'=>array(
    'type'=>'VARCHAR',
    'constraint' => 128,
    'unique' => TRUE,
  ),

  'description'=>array(
    'type'=>'VARCHAR',
    'constraint' => 255,
  ),

  'content'=>array(
    'type'=>'TEXT',
  ),

  'post_image'=>array(
    'type'=>'VARCHAR',
    'constraint' => 255,
  ),

 'created_at'=>array(
    'type'=>'TIMESTAMP',
  ),

 'updated_at'=>array(
    'type'=>'TIMESTAMP',
  ),

));

$this->dbforge->add_key('id', TRUE);
$this->dbforge->create_table('posts');
$this->db->query('ALTER TABLE `posts` ADD FOREIGN KEY(`cat_id`) REFERENCES 'categories'(`id`) ON DELETE SET cat_id to 1;');

}

What shall I replace the last line with (it has illustrative purposes):

$this->db->query('ALTER TABLE `posts` ADD FOREIGN KEY(`cat_id`) REFERENCES 'categories'(`id`) ON DELETE SET cat_id to 1;');

in order to get the desired result?

UPDATE: my database does use InnoDB.

score 0 · Answer 1 · edited Jul 16 '19 at 18:45

0

You need to use

`ALTER TABLE `posts` ADD FOREIGN KEY(`cat_id`) REFERENCES 'categories'(`id`) ON DELETE SET DEFAULT 1`

However, be aware that this does not work with InnoDB. https://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

edited Jul 16 '19 at 18:45

Martin

22,212
11
70
132

answered Jul 15 '19 at 07:51

ascsoftw

3,466
2
15
23

What shall I use for InnoDB? – Razvan Zamfir Jul 16 '19 at 18:23
For InnoDB, this will need to be handled in CodeIgnitor. – ascsoftw Jul 17 '19 at 04:24

score 0 · Accepted Answer · answered Jul 15 '19 at 07:58

0

Here is an example using php

function_delete_category($id) {

     $this->db->trans_start();

     $this->db->where('cat_id', $id);
     $ct = $this->db->count_all_results('posts');

     if ($ct > 0) {

         $this->db->set('cat_id', 1);
         $this->db->where('cat_id', $id);
         $this->db->update('posts');

     }

     $this->db->delete('categories', array('id' => $id));

     $this->db->trans_complete();

     return $this->db->trans_stats();

}

answered Jul 15 '19 at 07:58

Alex

9,215
8
39
82

most definitely. just was an option – Alex Jul 15 '19 at 08:18
Could you comment your can so that I can understand it better before I use it? Thanks! – Razvan Zamfir Jul 23 '19 at 09:11
we need 4 things. the number of posts with the category id that is going to be deleted, a query to set all of those posts with that category id to 1, and then finally, a query to delete the category. first we check if the category has any dependencies i.e. if it is used `$ct>0` if it isn't we can just delete it, if it is used, then we update all posts with that category id to the new category. and again, finally, we delete the category. we wrap all this in a transaction because we want "all or nothing". if something fails, we don't want a *corrupted* database with mismatching foreign keys. – Alex Jul 23 '19 at 20:31

nice_dev · Answer 3 · 2019-07-18T08:58:34.527

0

Honestly speaking, you should have the cat_id column as the foreign key with a soft delete if the category gets deleted.
However, if this is so necessary, you can create a trigger for this, for MySQL to take care of the deletion and updation like below:

Trigger SQL:

delimiter //

create trigger update_post_categories_with_1
before delete on categories for each row

begin
    update posts set cat_id = 1 where cat_id = OLD.id;  
end//

delimiter ;

edited Jul 18 '19 at 08:58

answered Jul 18 '19 at 08:02

nice_dev

17,053
2
21
35

How would my migrations snippet look if *you* changed them to obtain the desired result? – Razvan Zamfir Jul 18 '19 at 08:04
@RazvanZamfir Not a codeigniter dev, but could add a method for create trigger on your own in the application's main `CI_DB_forge` class itself and call that method here and pass the trigger syntax string to it. In that method, you could just do `$this->db->query(...)`. This can help https://stackoverflow.com/questions/23750476/how-to-create-triggers-in-codeigniters-migration-library – nice_dev Jul 18 '19 at 09:08

Codeigniter 3 blogging application: whenever a category is deleted, set cat_id of all posts in that category to 1

3 Answers3