Page 1 of 1

Multy-Level MySQL Query thru PHP

Posted: Tue Aug 26, 2008 6:13 am
by merianos
Hello all.

I like to create a directory for my web site based on MySQL Database.

The table looks like that

Code: Select all

 
RECORD_ID PARENT_ID RECORD_DATA
RID_0001   NULL          /
RID_0002   RID_0001    HOTELS
RID_0003   RID_0001    TICKETS
RID_0004   RID_0001    REND A CAR
RID_0005   RID_0002    5 Star Hotels
RID_0006   RID_0002    4 Star Hotels
RID_0007   RID_0002    3 Star Hotels
RID_0008   RID_0002    2 Star Hotels
...            ...              ...
 

The above example work perfectly.

Now by using PHP i quering the MySQL Database until Parent_ID is null from inside the function

Code: Select all

 
function [color=#FFAA00]fintTheParentRecord[/color](CurrentRecordID)
{
      $sqlQuery = "SELECT the parent record of the CurrentRecordID"
 
      ...... Some code is here
 
      if(the ParentID of the current query result is not null)
      {
         [color=#FFAA00]fintTheParentRecord(The ParentID of the current query result);[/color]
      }
      else
      {
           return something
      }
}
 
The above it work. I have already get results.

The question is :

If i have to go thru 20 or 30 levels up is that hard for the PHP and the Server that executes the MySQL and the PHP ? ? ?

Many many Thanks ! ! ! !

Re: Multy-Level MySQL Query thru PHP

Posted: Tue Aug 26, 2008 6:20 am
by marcth
It'd be best to do one SQL call to get all your data. This article appears to explain how to do SQL Trees in mySQL:

http://www.intelligententerprise.com/00 ... o1_1.jhtml

From the article:

Code: Select all

CREATE TABLE Personnel
(emp CHAR(10) NOT NULL PRIMARY KEY,
  lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
  rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
  CONSTRAINT order_okay CHECK (lft < rgt) );
1. Find an employee and all his/her supervisors, no matter how deep the tree.

Code: Select all

SELECT P2.*
   FROM Personnel AS P1, Personnel AS P2
  WHERE P1.lft BETWEEN P2.lft AND P2.rgt
    AND P1.emp = :myemployee;
 

Re: Multy-Level MySQL Query thru PHP

Posted: Tue Aug 26, 2008 6:25 am
by merianos
Thanks a lot ! ! !

It seems to be what I need ! ! !

If not then I will reply ! ! !