How can I update the amount to add 500 ,if the parentid have left and right children
example in my data:
1 is the parent of 5 and 6
5 is the parent of 7 and 8
7 is the parent of 9 and 10
so 7,5 and 1 will be updated there amount of 500…Is this possible ?
CREATE TABLE `mytree` (
`pid` INT(11) NOT NULL,
`memd` INT(11) NOT NULL,
`position` CHAR(1) NOT NULL,
`amount` DECIMAL(10,2) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;
pid memd position amount
0 1 0.00
1 5 L 0.00
1 6 R 0.00
5 7 L 0.00
5 8 R 0.00
7 9 L 0.00
7 10 R 0.00
9 11 L 0.00
All I could think of would be to iterate through the table along the lines of the recursive function you posted elsewhere and update using that information. I am not experienced enough to do this in SQL, perhaps @jemz you should ask in the SQL area.
(Edited to add the word “not” which I missed out above. Makes me sound less like a bighead).
@guido2004, @Drummin, @droopsnoot,. I’m just confuse to use my recursive function on how can I check if this parentid have both left and right children then update the parentid amount,then go up to also his parent then update also there amount and so on…just like in my example when parentid 7 added new member 10 now 7 have left and right children,then 7 will be given 500 amount,then his parent 5 will be given 500 and the parent of 5 which is 1 will be given 500 also.
I think this recursive function will be call after I succesffully added new member.
public function update_amount($parent,$level,$rec = array()){
try {
$cmd = $this->connection->prepare('update mytree set amount = amount + 500 where parentid = ?');
$cmd->execute(array($parent));
while ( $row = $cmd->fetch(PDO::FETCH_ASSOC)) {
$rec= $this->update_amount($row['memberid'], $level + 1,$rec);
}
$cmd = null;
return $rec;
}
catch(PDOException $ex){
return $ex->getMessage();
}
}
CREATE TABLE `mytree` (
`pid` INT(11) NOT NULL,
`memd` INT(11) NOT NULL,
`position` CHAR(1) NOT NULL,
`amount` DECIMAL(10,2) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
;
pid memd position amount
0 1 0.00
1 5 L 0.00
1 6 R 0.00
5 7 L 0.00
5 8 R 0.00
7 9 L 0.00
7 10 R 0.00
9 11 L 0.00
I’m just confuse to use my recursive function on how can I check in SQL statement if this
parentid have both left and right children then update the parentid
amount,then go up also to his parentid then update also there amount and
so on…just like in my example when parentid 7 added new member 10, now
7 have left and right children,then 7 will be given 500 amount,then his
parent 5 will be given 500 and the parent of 5 which is 1 will be given
500 also.
public function update_amount($parent,$level,$rec = array()){
try {
$cmd = $this->connection->prepare('update mytree set amount = amount + 500 where parentid = ?');
$cmd->execute(array($parent));
while ( $row = $cmd->fetch(PDO::FETCH_ASSOC)) {
$rec= $this->update_amount($row['memberid'], $level + 1,$rec);
}
$cmd = null;
return $rec;
}
catch(PDOException $ex){
return $ex->getMessage();
}
}
I want to check if the parent has left and right child then update the parentid with amount of 500 then go up to tree update also there parentid to 500 amount. parent 7,5 and 1 receive amount 500 ,when parent 7 completed the left and right.
Yes, but I think you’re going to need to track IF the person was given the 500 because as it stands, they will get the bump every time the script is run. Something like field received 1 or 0. Something like this.
I think value is passed in the array() as the argument to execute(). Always confuses me too, I explicitly use bindParam rather than this method. I thought this was PDO though.
