Recursive function php with ibase querys

Question

I have a function that builds a path through the id of a certain file the function works perfectly until it throws an ibase_query error. Here is the function:

function Build_Path($id,$database){
$path_query = "SELECT PATH_NAME,FATHER_ID,VOLUME_ID FROM PATHS WHERE PATH_ID = ".$id;
$query = ibase_query($database, $path_query);
while ($names = ibase_fetch_object($query)) {
    $path_volume_id = $names->VOLUME_ID;
    $name = $names->PATH_NAME;
    $father_id = $names->FATHER_ID;
    if ($name == "") {
        $volumes = ibase_query($database, "SELECT VOLUME_NAME FROM VOLUMES WHERE VOLUME_ID = " . $path_volume_id);
        while ($get_volume_name = ibase_fetch_object($volumes)) {
            return $get_volume_name->VOLUME_NAME;
        }
        ibase_free_result($volumes);
    } else {
        return Build_Path($father_id, $database) . "/" . $name;
    }
}
ibase_free_result($query);
ibase_close($database);
}

The error is only thrown after the path number 12999 and it's says:

ibase_query(): too many open handles to database

The error is thrown once and then the query does not recieve any result although i have 1500000+ paths in my database.

Tschallacka · Accepted Answer · 2017-06-30T08:39:34.153

3

Try this recursive function

WITH RECURSIVE hierarchy (PATH_ID, PARENT_ID, PATH_NAME) as (
   SELECT PATH_ID, PARENT_ID, PATH_NAME
   FROM PATHS
   WHERE PARENT_ID = -1

   UNION ALL

   SELECT PATH_ID, PARENT_ID, PATH_NAME
   FROM PATHS f
     JOIN hierarchy p ON p.PATH_ID = f.PARENT_ID
)
SELECT *
FROM hierarchy

EDIT discovered firebird is actually a database engine. learn something everyday

- original answer -

Why don't you use a join?

SELECT VOLUMES.VOLUME_NAME FROM PATHS WHERE PATH_ID = :ID
left join PATHS as PATH_1 on PATHS.FATHER_ID = PATH_1.PATH_ID
left join PATHS as PATH_2 on PATH_1.FATHER_ID = PATH_2.PATH_ID
left join PATHS as PATH_3 on PATH_2.FATHER_ID = PATH_3.PATH_ID
left join PATHS as PATH_4 on PATH_3.FATHER_ID = PATH_4.PATH_ID
left join PATHS as PATH_5 on PATH_4.FATHER_ID = PATH_5.PATH_ID
left join PATHS as PATH_6 on PATH_5.FATHER_ID = PATH_6.PATH_ID
left join PATHS as PATH_7 on PATH_6.FATHER_ID = PATH_7.PATH_ID
left join PATHS as PATH_8 on PATH_7.FATHER_ID = PATH_8.PATH_ID
left join PATHS as PATH_9 on PATH_8.FATHER_ID = PATH_9.PATH_ID
left join PATHS as PATH_10 on PATH_9.FATHER_ID = PATH_10.PATH_ID
JOIN VOLUMES on (
     VOLUMES.VOLUME_ID = PATHS.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_1.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_2.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_3.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_5.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_6.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_7.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_8.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_9.VOLUME_ID
     OR VOLUMES.VOLUME_ID = PATH_10.VOLUME_ID
)
WHERE VOLUMES.VOLUME_NAME IS NOT NULL and VOLUMES.VOLUME_NAME != ""

edited Jun 30 '17 at 08:39

answered Jun 29 '17 at 11:19

Tschallacka

27,901
14
88
133

its a bit complicated because the table paths have each folder as a single row and than have the father id of the folder before the current one and so on until it reaches the one that has no father id – Mika Jun 29 '17 at 11:22
and how about a stored procedure? https://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – Tschallacka Jun 29 '17 at 11:23
how deep can the paths go? how many nesting levels? and is the underlying engine mysql? – Tschallacka Jun 29 '17 at 11:24
it goes up to 10 rows @Tschallacka – Mika Jun 29 '17 at 11:26
Look at the join I made. If you could provide me with an sqlfiddle setup with sample data I could finetune it a bit more. But this basically tries to left join up to 10 levels, then fetch the results where volume_name is not null. But without a dataset and knowing the database engine it's a bit hard to hash out. – Tschallacka Jun 29 '17 at 11:37
i see what u did there, but the other thing is that not all the files have the same amout of folders in the path.. – Mika Jun 29 '17 at 11:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147918/discussion-between-tschallacka-and-mika). – Tschallacka Jun 29 '17 at 11:38

Recursive function php with ibase querys

1 Answers1

- original answer -