weird mysql_fetch_array error

   if($_GET['confirm']){
        $coupon_id = $_GET['confirm'];
        $query = mysql_query("SELECT * FROM purchases WHERE coupon_id = '$coupon_id'");
            while($rows = mysql_fetch_array($query)){
                $user_id = $rows['user_id'];
                $query = mysql_query("INSERT INTO purchases_confirm VALUES(NUL开发者_如何学运维L,'$coupon_id','$user_id' ");
                if($query){
                    echo "inserting new values to database....done !";
                }
            }
        exit;
        }

and it outputs : Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in...the weird thing that if i execute the query from the command line or phpmyadmin ,it works !!

You can't reassign $query variable inside while with mysql_fetch_array using $query.

Should be:

   if($_GET['confirm']){
        $coupon_id = $_GET['confirm'];
        $query = mysql_query("SELECT * FROM purchases WHERE coupon_id = '$coupon_id'");
            while($rows = mysql_fetch_array($query)){
                $user_id = $rows['user_id'];
                $query2 = mysql_query("INSERT INTO purchases_confirm VALUES(NULL,'$coupon_id','$user_id' ");
                if($query2){
                    echo "inserting new values to database....done !";
                }
            }
        exit;
        }

It's because in you're while loop, you are overwriting the $query variable. You need to change the $query variable to something else. I generally use $sub_query So it looks like this:

if($_GET['confirm']){
$coupon_id = $_GET['confirm'];
$query = mysql_query("SELECT * FROM purchases WHERE coupon_id = '$coupon_id'");
while($rows = mysql_fetch_array($query)){
    $user_id = $rows['user_id'];
    $sub_query = mysql_query("INSERT INTO purchases_confirm VALUES(NULL,'$coupon_id','$user_id' ");
    if($sub_query){
        echo "inserting new values to database....done !";
    }
}
exit;
}

You are changing $query inside the loop :$query = mysql_query("INSERT INTO purchases_confirm VALUES(NULL,'$coupon_id','$user_id' "); and on the next iteration you are trying mysql_fetch_array($query). Use a different variable for insert : $another_quer = mysql_query("INSERT INTO purchases_confirm VALUES(NULL,'$coupon_id','$user_id' ");

Obviously the query's failing. You lack any form of error checking, so you're essentially throwing away your chance to catch the error. Try rewriting the code like this:

$query = mysql_query("...") or die(mysql_error());

which'll spit out the exact mysql error that caused the query to fail. Remember, even if the SQL statement itself is perfectly valid, there are many many other reasons why the query can fail. By failing to check for those potential error conditions, you've basically painted yourself into a corner.