Delete sectors and their descendants - PHP

Question

Delete sectors and their descendants - PHP

Navigation

#1 by (4 votes)
#2 by (1 votes)

3

I have the following table structure:

CREATE TABLE IF NOT EXISTS 'setores' (
  'set_cod' int(10) NOT NULL AUTO_INCREMENT,
  'set_base' int(10) NOT NULL,
  'set_setor' varchar(50) NOT NULL,
  'set_data' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'set_status' enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY ('set_cod')
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

In this case, I can add 3 levels of sectors, as below:

Wherethisblueisthemainsector,grayrepresentsthesubsectorandwhiterepresentsathirdsector.

Well,myquestionis:HowdoIgetridofthemainsectorbyexcludingallothersectors?WhatifIdeleteonlythesubsector?Isthereanywaytodothisusingleftjoin?

Tolistasbelow,Ididthefollowing:

#BuscaSetorespublicfunctionbusca_setores(){$this->db->where("set_base", '0');
    $this->db->order_by("set_setor", "ASC");
    $consulta = $this->db->get('setores')->result();

    foreach($consulta as &$valor){

        $this->db->where("set_base", $valor->set_cod);
        $valor->subsetor = $this->db->get('setores')->result(); 

        foreach($valor->subsetor as &$subsetor){
            $this->db->where("set_base", $subsetor->set_cod);
            $subsetor->subsubsetor = $this->db->get('setores')->result();
        }

    }

    return $consulta;   
}

Can you adapt this reality, to set the exclusion by sector?

php mysql sql

asked by anonymous 08.08.2016 / 21:46

2 answers

1

Another possibility is to solve this with a trigger . This approach is harder to maintain, but does not need to have the table's self-relationship (but you can choose to have it anyway).

DELIMITER $
CREATE TRIGGER 'apagar_subsetores' BEFORE DELETE ON 'setores' FOR EACH ROW
BEGIN
    DELETE FROM setores WHERE set_base = old.set_cod;
END$
DELIMITER ;

08.08.2016 / 22:21

textContent Material Designer Angular JS What is the best way to capture keyboard shortcuts?

score 4 · Accepted Answer

One idea is to use ON DELETE CASCADE in the foreign key (from the table to itself):

CREATE TABLE IF NOT EXISTS 'setores' (
  'set_cod' int(10) NOT NULL AUTO_INCREMENT,
  'set_base' int(10) NOT NULL,
  'set_setor' varchar(50) NOT NULL,
  'set_data' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'set_status' enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY ('set_cod')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

ALTER TABLE 'setores'
    ADD CONSTRAINT 'relaciona_pai' FOREIGN KEY ('set_base')
    REFERENCES 'setores' ('set_cod')
    ON DELETE CASCADE ON UPDATE CASCADE;

The SQL for the deletion would be this:

/* Vamos supor que 5 é o código do setor pai que você quer excluir. */
DELETE FROM setores WHERE set_base = 5;

And note that in the code above I used ENGINE=InnoDB instead of ENGINE=MyISAM . This is important because MyISAM does not support the concept of foreign keys. Although MySQL maintains them in the table definitions, the corresponding integrity constraints are not respected by MyISAM, which ignores them completely.