looping php mysql query till no result found

Question

I have this mysql query that i want to loop till there is no more result found in the table,

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

}

and what i want is loop the same query again and again like this:

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

}
}

this last code will loop the query twice but the problem i have is that i want to loop it till there is no more result found

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

<--! REPEAT SAME CODE IN HERE AGAIN AND AGAIN AND AGAIN --> }

looping 3 times:

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

$sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
 $res = mysql_query($sql_query);
 $ids=array();
 while($row = mysql_fetch_object($res)){
 $ids[]=$row->id;
}
 $ids=array_filter($ids);
 foreach($ids as $id){
 echo $id;

}
}
}

so how do i repeat the code infinite times till there is no result in the database

No I think he wants just to fetch all rowns on that table and the code just did it once... — Jorge Campos, Dec 20 '13 at 21:05
Yes jessica thats what i want, any way , example how to do it right? — user3117183, Dec 20 '13 at 21:07
So it sounds like you are trying to represent a tree structure in your database table. You probably need to think about your schema a little more. You should not need to recursively query a the table to get this data, but rather be able to get to the data in a single query if you have a properly designed table schema. See these links for more information: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ http://stackoverflow.com/questions/5916482/php-mysql-best-tree-structure — Mike Brant, Dec 20 '13 at 22:04

daiscog · Accepted Answer · 2013-12-20T22:00:35.377

0

A quick solution using recursion:

function getChildIds($id, &$count) { // Note that $count is passed in as a reference (the & before the $count)
    ++$count;
    echo $id;
    $sql_query="select id from rev_r_clients WHERE parent_client_id='$id'";
    $res = mysql_query($sql_query);
    $ids = Array();
    while($row = mysql_fetch_object($res)){
        if ($row->id) { // Checking for false-like values here to remove the need for array_filter
            $ids[] = $row->id;
        }
    }

    foreach ($ids as $next) { // using foreach instead of array_walk as we cannot pass a reference into array_walk without raising a warning (or fatal error, depending on PHP version)
        getChildIds($next, $count);
    }
}

$count = 0;
getChildIds($firstId, $count);
// $count now has the number of times the function was called
echo $count;

Note, though, that the mysql_* functions are deprecated and you should instead use mysqli or PDO.

edited Dec 20 '13 at 22:00

answered Dec 20 '13 at 21:12

daiscog

11,441
6
50
62

You did not echoed the id! – Jorge Campos Dec 20 '13 at 21:16
echo $id; is the first line of the function. – daiscog Dec 20 '13 at 21:21
Ooh sorry, missed it... but it should be inside the if... since at least one will not have data! and you will print it anyway right?! – Jorge Campos Dec 20 '13 at 21:28
is there a way to count the ids echoed? – user3117183 Dec 20 '13 at 21:29
1

@JorgeCampos Nope, the `if ($row->id)` check ensures the function doesn't get called if the $id retrieved is null (or zero). – daiscog Dec 20 '13 at 21:29
I can't view that link - it forwards to a register page. When/where do you need to echo the count? During each call to the method, or after it's all done? Just add in `echo $count` wherever you need it. – daiscog Dec 20 '13 at 21:50
i need to echo the count alone , can i do that? – user3117183 Dec 20 '13 at 21:56
Well, yes. Just remove `echo $id;` from the function, and add `echo $count` after the initial function call. – daiscog Dec 20 '13 at 22:00
Could you define "isn't counting right" please? I'm afraid I can't see what's wrong just by looking at a page full of numbers without knowing the source data, or the code used to produce the output. Note also that I've edited my answer to replace `array_walk` with a `foreach` (using `array_walk` may have caused counting issues as you can't pass an argument into it by reference). – daiscog Dec 20 '13 at 22:05
perfect , that did all i want :D , Thanks 1000000 times – user3117183 Dec 20 '13 at 22:15

looping php mysql query till no result found

1 Answers1