MySQL orphans again

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
satikas
Forum Newbie
Posts: 19
Joined: Sat Mar 28, 2009 9:14 am

MySQL orphans again

Post by satikas »

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:

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)
when doing:

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());
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:

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
Id be so grateful if I managed to get it work already.
Post Reply