CODEWITHSUNDEEP
phpmysql
akash99
akash99

Reputation: 41

Updating values of child if their parents is updated in a single table php

I have a person table with PersonId,FatherId fields.

| PersonId |  FatherId    |
| 1        |      0       | 
| 2        |      1       | 
| 3        |      1       |  
| 4        |      2       |  
| 5        |      3       |   
| 6        |      2       |  
| 7        |      6       |   
| 8        |      7       |

If I update personId 2 then all 2,4,6,7,8 should be updated pls suggest a solution.

Upvotes: 2

Views: 205

Answers (4)

Purushottam
Purushottam

Reputation: 844

Look's like you want to update all records on hierarchy basis. For example,suppose if you are assigning an attribute to someone, all child of that person would inherit the same attribute. If correct than you must need to go with PHP solution, use of Trigger in mysql may be some tricky. You can try this one,

Assumption : You want to assign subfamily to a person.

 <?php

// To manage heierchy data for subfamily
$subfamily_id = '100';
$person_id = '2'; // In your case

$array[] = $person_id;   
$arr = [];

// Do-while loop to assign Subfamily in heierchy
    do {
        foreach($array as $key => $val){
            $sql = 'SELECT person_id FROM person WHERE father_id = "'.$val.'" AND '.$subfamily.' = 0';
            $result = $conn->query($sql);
            $data = $result->fetchAll(PDO::FETCH_ASSOC);
            foreach($data as $k => $v){
                $arr[] = $v['person_id'];
            }
            $sql_query = "UPDATE person SET ".$subfamily." = ".$subfamily_id." WHERE father_id = ".$val.' AND '.$subfamily.' = 0';
            $statement = $conn->prepare($sql_query);
            $statement->execute();
        } 
        unset($array);
        $array = $arr;
        unset($arr);
    } 
    while (count($array) > 0);

// Assign Subfamily to individual
$sql_next = "UPDATE person SET ".$subfamily.' = '.$subfamily_id." WHERE person_id = ".$person_id;
$statement = $conn->prepare($sql_next);
$statement->execute();

Upvotes: 0

subrahmanya bhat
subrahmanya bhat

Reputation: 598

Try with creating triggers before updating the table.

CREATE TRIGGER 'trigger_name' BEFORE/AFTER INSERT/UPDATE/DELETE ON 'database.table' FOR EACH ROW BEGIN - trigger body //write what you want to update here. END;

If you want to update the same table with same value as personId for 2, then

UPDATE Person set  PersonId = 3 where PersonId IN(2,4,6,7,8)

Upvotes: 0

Boris Schegolev
Boris Schegolev

Reputation: 3701

This is not feasible in MySQL. Triggers are not allowed to update the same table.

With MySQL it has to be done by application itself. Good luck!

Upvotes: 2

hummingBird
hummingBird

Reputation: 2555

So, in case you'd like to manually do it, use this.

UPDATE person SET FatherId = NEWIDVALUE 
WHERE FatherId = OLDVALUE;
-- now update the actual id
UPDATE person SET PersonId = NEWIDVALUE
WHERE PersonId = OLDVALUE
LIMIT 1; -- if your Id values for person are indeed unique

Upvotes: 0

Related Questions