how can i controll while loop into another while loop

Question

Suppose I have a while loop like:

$sql = mysql_query("SELECT * FROM tablename");
while($row = mysql_fetch_array($sql)){

  $id = $row["id"];
  $sql_2 = mysql_query("SELECT * FROM secondtable WHERE id != $id ");
  while($ro = mysql_fetch_array($sql_2)){
   $id2 = $ro["id2"];
   echo $id2;
   }

}

then if first query return 5 results i.e 1-5 and second query returns 3 results than if i want to echo out second query it gives me like this..........

111112222233333

than how can i fix to 123 so that the second while loop should execute according to number of times allowed by me........!! how can i do that.........!!

Answer 1

$sql = mysql_query("SELECT * FROM tablename");
$tmp = array();
while($row = mysql_fetch_array($sql)){

    $id = $row["id"];
    if(!in_array($id, $tmp)) {  
        $sql_2 = mysql_query("SELECT * FROM secondtable WHERE id != $id ");
        while($ro = mysql_fetch_array($sql_2)){
            $id2 = $ro["id2"];
            echo $id2;
        }
        $tmp[] = $id;
    }
}

Saving all queried $id's in an array to check on the next iteration if it has already been queried. I also think that GROUPing the first query result would be a better way.

Answer 2

Do you want to explicitly limit the number of iterations of the inner loop?

Have you considered using a for loop?

$sql = mysql_query("SELECT * FROM tablename");

while($row = mysql_fetch_array($sql)){
    $id = $row["id"];
    $sql_2 = mysql_query("SELECT * FROM secondtable WHERE id != $id ");

    for($i=0; $i<3; $i++){
       $ro = mysql_fetch_array($sql_2);
       $id2 = $ro["id2"];
       echo $id2;
    }
}

Answer 3

Your first while loop is iterating over all 5 results, one at a time.

Your second while loop is iterating over each of the 5 results, producing it's own set of results (i.e. 3 results for each of the 5 iterations, totaling 15 results).

I believe what you are trying to do is exclude all IDs found in your first loop from your second query. You could do that as follows:

$sql = mysql_query("SELECT * FROM tablename");
$exclude = array();
while($row = mysql_fetch_array($sql)) {
    array_push($exclude, $row['id']);
}

// simplify query if no results found
$where = '';
if (!empty($exclude)) {
    $where = sprintf(' WHERE id NOT IN (%s)', implode(',', $exclude));
}

$sql = sprintf('SELECT * FROM secondtable%s', $where);
while($row = mysql_fetch_array($sql_2)) {
   $id2 = $row["id2"];
   echo $id2;
}

Answer 4

I'm not sure I 100% understand your question - it's a little unclear.

It's possible you could solve this in the query with a GROUP BY clause

$sql_2 = mysql_query("SELECT id FROM secondtable WHERE id != $id GROUP BY id");

But that would only work if you need just secondtable.id and not any of the other columns.

When you say "number of time allowed by me" do you mean some sort of arbitrary value? If so, then you need to use a different loop mechanism, such as Greg B's solution.

Answer 5

I agree with Leonardo Herrera that it's really not clear what you're trying to ask here. It would help if you could rewrite your question. It sounds a bit like you're trying to query one table and not include id's found in another table. You might try something like:

SELECT * FROM secondtable t2
WHERE NOT EXISTS (SELECT 1 FROM tablename t1 WHERE t1.id = t2.id);