PHP Recursive query script

Question

I want to create a recursive query scripts that outputs the ID_Col2 which will be used as parameter for the next recursive query. Here my code which actually stops only at first cycle:

function recursiveQuery ( $ID, $numRows ) {                                         
  for($i=0;$i<=$numRows;$i++) {                                         
    $dbConnect = new MySqlConnect();
    $dbConnect->connect();      
    $query = $dbConnect->query("SELECT ID_Col1, ID_Col2 FROM wbs WHERE ID_Col1 = '" . $ID . "' AND ID_Col2 != '0' ORDER BY ID ASC");
    $n = mysql_num_rows($query);
    $result = $dbConnect->extractObject($query);    

    for($x=0;$x<count($result);$x++){
      $r = $result[$x]->ID_Col2;
      echo $r . '<br>';
      return recursiveQuery( $r, $n );
    }

    $dbConnect->disconnect();                                               
  }
}

echo recursiveQuery( '6765', '25' );

So how should I modify this script to make it works?

Doing database queries in a loop in a recursive function seems like a very bad idea. There surely is a better way to accomplish what you want to do, but you would have to specify what you want to do exactly. — jeroen
– jeroen, Commented Apr 28, 2014 at 13:51
Could you please clarify this a bit? What's inside wbs? What's the exact output you desire? — Sergiu Paraschiv
– Sergiu Paraschiv, Commented Apr 28, 2014 at 13:51
@Segiu Parashiv I have two columns with two IDs. The first column are fathers, the second are sons. Sons, can be also fathers. So, I want to use the ID_Col2 as parameter for the next query, in order to get all sons in a tree structure. — Escudo Bravo
– Escudo Bravo, Commented Apr 28, 2014 at 13:58

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You open Databases connections recursively... :

function recursiveQuery ( $ID, $numRows ) {
  $dbConnect = new MySqlConnect();
  $dbConnect->connect();   
  
  for($i=0;$i<=$numRows;$i++) {
    // [...]    

    for($x=0; $x < count($result); $x++){
      // [...]
      return recursiveQuery( $r, $n );
    }

    $dbConnect->disconnect();                                               
  }
}

I can't tell you how to make it work because it should not work.

You should Open/Close connections outside the recursive part of your code.

EDIT

Proper code :

function recursiveQuery ( $ID, $numRows, $dbConnect ) {
  for($i=0;$i<=$numRows;$i++) {
    // [...]    

    for($x=0; $x < count($result); $x++){
      // [...]
      // here you don't want to return but just to execute :
      recursiveQuery( $r, $n, dbConnect);
    }                                             
  }
}

function wrapFunc($ID,$numRows) {
  $dbConnect = new MySqlConnect();
  $dbConnect->connect();
  // nothing is returned by your function (echos directly in the code)
  recursiveQuery ( $ID, $numRows, $dbConnect);
  $dbConnect->disconnect();
}

wrapFunc('6765','25');

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 28, 2014 at 14:02

Apolo

4,0162 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sergiu Paraschiv Over a year ago

You are opening a new DB connection on each recursion. That's quite wrong.

Apolo Over a year ago

@SergiuParaschiv that's is exactly the problem I am showing here.

Escudo Bravo · Accepted Answer · 2014-04-29 14:43:10Z

0

Here I found the best answer for me: http://www.sitepoint.com/hierarchical-data-database/

answered Apr 29, 2014 at 14:43

Escudo Bravo

753 silver badges10 bronze badges

Collectives™ on Stack Overflow

PHP Recursive query script

2 Answers 2

EDIT

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related