Stack Exchange
php nested while loop. Inner loop works only once
This is the code . Any help ??
<?php  

    //connect to the database 
        $con=mysql_connect("localhost","root","") or die("Error in connection");
        mysql_select_db("riffadb",$con)or die("Error in database");
    // 
        $sql1 ="select category_id,category_code from category";
        $res1 = mysql_query($sql1); 

        $sql2 = "select category_id1,product_id  from product";
        $res2 = mysql_query($sql2);
        $i=0;
        $j=1000;
        while($row1 = mysql_fetch_array($res1))
        {
            echo $row1['category_code'];
            echo '<br/>';
            while($row2 = mysql_fetch_array($res2))
            {   
                if($row1['category_code'] == $row2['category_id1'])
                {   
                    $sql3 = "update product set category_id = ".$row1['category_id']." where product_id = ".$row2['product_id'];
                    echo '<br/>';
                    mysql_query($sql3);
                }
            }
            $i++;
        }
        echo $i;
    ?>


How do you know inner loop is running only once? Please explain more

Update query is working only for the first category id

Can't you do a join to get items like ON category_id1 = category_code then use one while loop?

I think you need to re-query res2 inside the outer loop, or at least reset the pointer.  The second time through the outer loop you have nothing left in res2, since you've already reached the end
In addition, with would be a lot more efficient if you setup the res2 query inside the outer loop and add a where clause so that you are only matching the category ID from the current row in res1.
Hope this helps.

Try This: 
<?php  

//connect to the database 
    $con=mysql_connect("localhost","root","") or die("Error in connection");
    mysql_select_db("riffadb",$con)or die("Error in database");
// 
    $sql1 ="select category_id,category_code from category";
    $res1 = mysql_query($sql1); 


    $i=0;
    $j=1000;
    while($row1 = mysql_fetch_array($res1))
    {
        echo $row1['category_code'];
        echo '<br/>';
          // execute second query under the first loop
         $sql2 = "select category_id1,product_id  from product";
         $res2 = mysql_query($sql2);

        while($row2 = mysql_fetch_array($res2))
        {   
            if($row1['category_code'] == $row2['category_id1'])
            {   
                $sql3 = "update product set category_id = ".$row1['category_id']." where product_id = ".$row2['product_id'];
                echo '<br/>';
                mysql_query($sql3);
            }
        }
        $i++;
    }
    echo $i;
?>


This would re-query the database on each iteration of the outer loop. It's better to reset the pointer with mysql_data_seek($res2, 0);

Place this inside first loop:
$sql2 = "select category_id1,product_id  from product";
$res2 = mysql_query($sql2);


Maybe better to use something like mysql_data_seek($res2, 0) after the loop and remove the two lines you have in your post instead of querying the database each time?
