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.........!!

Huh??? If the result of your loop is 111112222233333 it would seem that the id's in secondtable are not unique. I'm even wondering if 111112222233333 could possibly be an output of that specific loop... — jeroen
– jeroen, Commented Nov 30, 2009 at 18:48
I know it's difficult to understand his question but at the same time many individuals using stackoverflow do not natively speak english. Please take this into consideration before downvoting. — Corey Ballou
– Corey Ballou, Commented Nov 30, 2009 at 18:53
cballou: I'm one of those individuals. That's not the problem in this case. Proving sample data for both tables and the expected output would have helped. Just take a look at the answers -- all of them are in the form "I don't understand what you want, but here's a guess." — Leonardo Herrera
– Leonardo Herrera, Commented Dec 1, 2009 at 14:25

Peter Bailey · Accepted Answer · 2009-11-30 18:48:13Z

1

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.

answered Nov 30, 2009 at 18:48

Peter Bailey

106k32 gold badges186 silver badges206 bronze badges

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

Comments

Greg B · Accepted Answer · 2009-11-30 18:42:44Z

0

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;
    }
}

answered Nov 30, 2009 at 18:42

Greg B

14.9k18 gold badges92 silver badges151 bronze badges

1 Comment

testkhan Over a year ago

it is giving me the same result and i am on the same previous position if gives me the follwoing result........ 12131415

Corey Ballou · Accepted Answer · 2009-11-30 18:45:31Z

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;
}

Alex · Accepted Answer · 2009-11-30 18:55:50Z

0

$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.

edited Nov 30, 2009 at 18:55

answered Nov 30, 2009 at 18:42

Alex

12.5k7 gold badges47 silver badges52 bronze badges

Comments

eaolson · Accepted Answer · 2009-12-01 04:59:09Z

0

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);

answered Dec 1, 2009 at 4:59

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Collectives™ on Stack Overflow

how can i controll while loop into another while loop

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related