Remove assigned users from database table [on hold]

126
September 06, 2017, at 4:12 PM

I have a table:

| staff_id | leader_id |   team_role  | importance |
+          +           +              +            +
| 1001037  |  1000321  |      HOD     |      1     |
| 1001037  |  1000322  |      HOD     |      1     |
| 1001037  |  1001037  |  Supervisor  |      2     |
| 1001094  |  1001037  |    Checker   |      3     |
| 1001075  |  1001037  |    Checker   |      3     |
| 1001096  |  1001075  | Squad Leader |      4     |
| 1000393  |  1001094  | Squad Leader |      4     |
| 1000465  |  1001094  | Squad Leader |      4     |
| 1000585  |  1001075  | Squad Leader |      4     |
| 1000664  |  1000585  | Team Member  |      5     |
| 1000583  |  1000585  | Team Member  |      5     |
| 1000570  |  1000465  | Team Member  |      5     |
| 1000316  |  1000465  | Team Member  |      5     |

In php it look like this:

If I want to remove user with staff_id = 1001075 I need to remove all assigned to this user ids. So it will be ids where 1001075 is leader_id. I am able to this but I also need to remove users going down to the tree so where leader_id= 1001075 is will be 1000585 Then I need to remove users where leader_id= 1000585

In the end I need to delete these users with staff_id:

1001075
1001096
1000585
1000664
1000583

How can I do that?

Answer 1

There are two ways to do this:

  • Recursively query for all the ids to find out all the children and delete the rows one by one. Have a look at this answer for approaches to recursively query the ids.

  • Run an ALTER TABLE script and make leader_id a foreign key, referencing to staff_id of the same table, and define with ON DELETE CASCADE. By this way, when you delete a leader row, it will delete all the subsequent rows, e.g.:

    ALTER TABLE table ADD CONSTRAINT fk_leader_id FOREIGN KEY (leader_id) REFERENCES table(staff_id) ON DELETE CASCADE;

I would prefer the second approach.

READ ALSO
Retrieve Data from phpmyadmin online 000webhost to Visual Basic Applications

Retrieve Data from phpmyadmin online 000webhost to Visual Basic Applications

Is there any example using php connect with visual basic applications between (000webhost)phpmyadmin?

232
Ubuntu migrate package PHP 7.0 and 7.1

Ubuntu migrate package PHP 7.0 and 7.1

Hi I am new with Ubuntu and have some problem with PHP1

296
MySQL - #1005 - Can't create table 'university.Enrolment' (errno: 150) (Details…)

MySQL - #1005 - Can't create table 'university.Enrolment' (errno: 150) (Details…)

I don't know why it won't let me create the table enrolmentI only need to reference student, uniadmin and course and I have created them above enrolment

108
Join query for these issue

Join query for these issue

problem is that only those records are fetch which is in donor table rest are not showi want to show data always when right side match not found

138