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

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

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

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 310.061930 milliseconds

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