php嵌套while循环。 内环只能工作一次[关闭]

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

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.

Place this inside first loop:

$sql2 = "select category_id1,product_id  from product";
$res2 = mysql_query($sql2);

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