MySQL orphans again
Posted: Sun Jan 10, 2010 12:01 pm
Hi, I once had this problem and thought that I solved it.
I made a script that was supposed to delete all orphaned items.
Yet it leaves some behind, so I started looking for a new solution.
I ve once again spent hours on this and found a pretty good solution:
(Provided by ralph l mayo)
http://www.codingforums.com/archive/ind ... 47680.html
I did all the steps but I cant add data into the table, because it results:
when doing:
I did everything exactly except added some extra fields to parent and ID.
Also found this script, which is supposed to delete orphans, but I have no idea how to modify it to work with my ID and parent (ID values) system:
Id be so grateful if I managed to get it work already.
I made a script that was supposed to delete all orphaned items.
Yet it leaves some behind, so I started looking for a new solution.
I ve once again spent hours on this and found a pretty good solution:
(Provided by ralph l mayo)
http://www.codingforums.com/archive/ind ... 47680.html
I did all the steps but I cant add data into the table, because it results:
Code: Select all
Cannot add or update a child row: a foreign key constraint fails (`np19508_TIGER`.`bookmarks`, CONSTRAINT `bookmarks_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `bookmarks` (`ID`) ON DELETE CASCADE)Code: Select all
mysql_query("INSERT INTO bookmarks (`parent`,`ename`,`name`,`position`,`logo`,`url`,`forced`) VALUES
('$parent','$ename','$name','$position','$logoname','$url','no')") or die(mysql_error());Also found this script, which is supposed to delete orphans, but I have no idea how to modify it to work with my ID and parent (ID values) system:
Code: Select all
This will find records in $t1
that refer to non-existant rows in $t2. Just fill in the variables.
SELECT t1.$key
FROM $t1 AS t1
LEFT JOIN $t2 AS t2
ON t1.$foreign_key = t2.$key
WHERE t2.$key IS NULL