Recursive Method on mySQL table.

[Anon]

Hi,

I'm having a slight problem trying to figure out some logic. I have a mySQL table that contains 3 columns (rowid, parent, child). What I would like to be able to do is check this table before I add. The check needs to see if the parent I am about to add is already in the table 5 times. If it is, I need to go to the first child that has that parent, and check if that child is listed in the parent column 5 times, if it is, then I need to go back up to the original parent, get the second child, and check the same. (very confusing I know) but I'm sure this could be done with 2 functions.

1. Retrieve the children of the parent and store in an array.
2. Traverse through the array, for each child in the array, create an array of their children (by calling 1). Do this until the array is not 5 deep. Then return the child who's array is not 5 deep.

Any insight into this would be great. I had an Idea as I was typing this. Can you store an array in a mySQL column? If I can then It might be easier to code.

Thanks in advance for the help.

Justin

[terrylee]

Hi Justin,

my proposed solution in PHP code and SQL.

<?php
//The Parent value
$parent_value;

// Check if parent value in Parent Column >5 times
$sql_parent = "SELECT COUNT() FROM mySQLTable WHERE parent = $parent_value";
$db=mysql_connect();
$result = mysql_query($sql_parent,$db);
$row = mysql_fetch_row($result);
if($row[0] > 5) {
$sql_child = "SELECT child FROM mySQLTable WHERE parent = $parent_value";
$result = mysql_query($sql_child,$db);
while($rowC = mysql_fetch_row($result)) {
$child_value = $rowC[0];
$parent_check_sql = "SELECT COUNT() FROM mySQLTable WHERE parent = $child_value";
$resultD = mysql_query($parent_check_sql, $db);
$rowD = mysql_fetch_row($result);
if($rowD[0] < 5) break;
}

$desired_result = $child_value;
}
mysql_disconnect();
?>

Is that what you wanted? 🙂 My solution is different from your logic but you should get the same desired value.

Terry
PS I dun think the code is complete. but the general idea is there.

[Anon]

Any more come of this... did this work?