visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
304 experts, 1182 registered users, 1626 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


The DDRK link is back online.
We got a one week service interruption, sorry.
(23-30/01/2010)

Databases :: MySql :: MYSQL Equivenlant for Oracle 'LEVEL'
By: definitivecoder U.S.A.  Date: 24/04/2003 00:00:00  English  Points: 130 Status: Answered
Quality : Excellent
I understand that in Oracle there is a keyword "LEVEL". Given a pattern such as personId = parentId, LEVEL accepts as arguments a personId/parentId and the number of levels to go down.

For example, say my personId = 15 and my parentId = 10. Also, assume that I have a child whose personId = 20. Using the LEVEL function I pass in 10 and 2. This would return the following records:
where personId = 10
where parentId = 10
where parentId = 15
where parentId = (all my sibling's personIds)

(my parent, his/her children, and their children's children) Make sense?

Having said that, I was wondering if MYSQL has anything equivelant to Oracle's LEVEL. My problem is that given a personId, I want to retrieve that person's children and all their children's children.

ABRV. DB SCHEMA:
P_ID
P_FIRSTNAME
P_PARENT_ID

Thanks for any direction you can give me.
By: VGR Date: 24/04/2003 08:11:00 English  Type : Answer
no

not standard

this has to be programmed in your front-end script
By: VGR Date: 24/04/2003 08:53:00 English  Type : Comment
until MySql has stored procedures, that is :D
By: snoyes_jw Date: 24/04/2003 13:28:00 English  Type : Comment
agree.

You can get children and grandchildren in one query by using inner joins, but you have to know how many levels down you want and construct the query manually.
By: snoyes_jw Date: 24/04/2003 14:00:00 English  Type : Assist
Just because it seems so popular to ask this question, here's a little code to do it in PHP:

<?php
// table name = "family";
// id field = "id";
// my current id = 1
// parent id field = "parentId"
// levels down to search = 3
$allMyChildren = recursiveDescent("family", "id", 1, "parentId", 3);

echo "<pre>\n";
print_r($allMyChildren);
echo "</pre>";


function recursiveDescent($table, $idField, $myId, $parentField, $levels)
{
$levels--;
$sql = "SELECT * FROM $table WHERE $parentField = '$myId'";
$result = mysql_query($sql) or die(mysql_error());
$offspring = array();
while ($row = mysql_fetch_assoc($result))
{
array_push($offspring, $row);
if ($levels > 0)
{
$chillins = recursiveDescent($table, $idField, $row[$idField], $parentField, $levels);
if (count($chillins) > 0)
{
// Use this line to get a 1-D array - everybody is at the same level
array_splice($offspring, count($offspring), 0, $chillins);
// Use this line instead to get an array of arrays - children are top level arrays, grandchildren are arrays grouped in an array, great-grandchildren are arrays grouped in arrays grouped in arrays, etc.
//array_push($offspring, $chillins);
}
}
}
return $offspring;
}
?>
By: VGR Date: 24/04/2003 16:43:00 English  Type : Comment
very nice (if it works, but I trust you :D )
By: snoyes_jw Date: 30/04/2003 09:35:00 English  Type : Comment
ping
By: snoyes_jw Date: 13/05/2003 17:49:00 English  Type : Comment
No comments in a while. Did you find an answer? If you solved it yourself, be sure to post in the Community Support area for a delete/refund.

Do register to be able to answer

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 123.944040 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page