tomda
msg:1580564 | 12:56 pm on Apr 5, 2006 (gmt 0) |
This is how I would do it. 1/ Parent ID
2/ Do a query and create an array called Child_array (child ids for all parents, except for the one above)
3/ Do queries using Child_array and create another array called Subchild_array (sub-child ids having Child_array as parent)
4/ Do a query using Subchild_array and create another array called Subsubchild_array (sub-sub-child ids having Subchild_array as parent) 5/ Compile Child_array, Subchild_array and Subsubchild_array 6/ Loop in the array and do your delete query (1 query per id) My 2cents, hope it helps
|
web_india
msg:1580565 | 1:03 pm on Apr 5, 2006 (gmt 0) |
tomda, thanks for your prompt reply.
I can follow the logic of the above posts. it is appreciated :)
but as you can see I am bit new to all this and don't know much about arrays. is there a way to do this in phpmyadmin?
or if not, can you give me some pointers from where I can learn about arrays. I searched a lot on mysql DELETE syntax but I could only find references to WHERE statements as a condition (but couldn't really find how to do the above)
|
aspdaddy
msg:1580566 | 1:32 pm on Apr 5, 2006 (gmt 0) |
Does the version used not support delete cascades like this:
ALTER TABLE [Parent] ADD
CONSTRAINT [ParentChildLink] FOREIGN KEY
([ChildID]
) REFERENCES [Child] (
[ID]
) ON DELETE CASCADE
|
|
If it does just let the database handle it. The script way will always screw up your database one day and the nested delete statments can slow down or hang the server. [edited by: aspdaddy at 1:34 pm (utc) on April 5, 2006]
|
tomda
msg:1580567 | 1:33 pm on Apr 5, 2006 (gmt 0) |
I am not good in writing complex Mysql queries, so I can't help you to do the above with one or two queries or using Myadmin. But I can give you the basic to do what I suggest above using PHP and arrays. Note that this method require numerous queries.
// PARENT TO KEEP
// **************
$parent_to_keep = "2"; // CREATE CHILD ARRAY
// ******************
$child_array=array();
$sql= "select id from table where parent!= $parent_to_keep";
$res=mysql_query($sql) or die ("Fail child_array");
while($pres=mysql_fetch_row($res)) {array_push($child_array,$pres[0]);}
// COUNT NUMBER OF ELEMENTS IN ARRAY
$nb_child_array=count($child_array);
// SHOW CHILD ARRAY
print_r ($child_array); // CREATE SUBCHILD ARRAY
// *********************
$subchild_array=array();
// LOOP THROUGH CHILD_ARRAY
for($i="0"; $i<$nb_child_array; $i++) {
// QUERY FOR EACH CHILD_ARRAY ELEMENT
$sql= "select id from table where parent = $child_array[$i]";
$res=mysql_query($sql) or die ("Fail subchild_array");
while($pres=mysql_fetch_row($res)) {array_push($subchild_array, $pres[0]);}}
// COUNT NUMBER OF ELEMENTS IN ARRAY
$nb_subchild_array=count($subchild_array);
// SHOW CHILD ARRAY
print_r ($subchild_array); // CREATE SUBSUBCHILD ARRAY - SAME AS ABOVE
// ************************
$subsubchild_array=array();
// LOOP THROUGH CHILD_ARRAY
for($i="0"; $i<$nb_subchild_array; $i++) {
// QUERY FOR EACH SUBCHILD_ARRAY ELEMENT
$sql= "select id from table where parent = $subchild_array[$i]";
$res=mysql_query($sql) or die ("Fail subsubchild_array");
while($pres=mysql_fetch_row($res)) {array_push($subsubchild_array, $pres[0]);}}
// COUNT NUMBER OF ELEMENTS IN ARRAY
$nb_subsubchild_array=count($subsubchild_array);
// SHOW CHILD ARRAY
print_r ($subsubchild_array); /* AT THIS POINT, YOU SHOULD HAVE THREE ARRAYS - MAKE SURE IT IS CORRECT BEFORE DELETING YOUR ROWS FROM YOUR DATABASE. PLEASE DO A BACKUP IN CASE YOU DO SOMETHING WRONG
*/ /* REMOVE THIS TO DELETE ROWS // DELETE CHILD_ARRAY
for($i="0"; $i<$nb_child_array; $i++) {
$sql="DELETE FROM `table` WHERE id = '$child_array[$i]' LIMIT 1;
$res=mysql_query($sql) or die ("Error"); } // DELETE SUBCHILD_ARRAY
for($i="0"; $i<$nb_subchild_array; $i++) {
$sql="DELETE FROM `table` WHERE id = '$subchild_array[$i]' LIMIT 1;
$res=mysql_query($sql) or die ("Error"); } // DELETE SUBSUBCHILD_ARRAY
for($i="0"; $i<$nb_subsubchild_array; $i++) {
$sql="DELETE FROM `table` WHERE id = '$subsubchild_array[$i]' LIMIT 1;
$res=mysql_query($sql) or die ("Error"); } */
|
|
Again, not tested but it should work.
Do a test and report back Tomda
|
tomda
msg:1580568 | 1:35 pm on Apr 5, 2006 (gmt 0) |
Oups, as Aspdaddy said, try his method before testing mine because you never know what a script will output, especially if you are new to arrays. [edited by: tomda at 1:40 pm (utc) on April 5, 2006]
|
web_india
msg:1580569 | 1:39 pm on Apr 5, 2006 (gmt 0) |
thanks tomda and aspdaddy. aspdaddy, I think CASCADE is the statement I needed :)
I'll give it a try and report back . Thanks
|
web_india
msg:1580570 | 5:08 pm on Apr 5, 2006 (gmt 0) |
aspdaddy, it seems REFERENCES works only with InnoDB Storage engines, is it true?
Mine is MyISAM. I've tried the option you gave above but I keep getting syntax errors
|
web_india
msg:1580571 | 1:11 am on Apr 6, 2006 (gmt 0) |
I tried converting it to InnoDB type but it still couldn't work :(
|
|
