weird mysql_fetch_array error

Question

   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(NULL,'$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 !!

Variables aren't expensive. Make a new one for the internal query. — Paul Tomblin
– Paul Tomblin, Commented Mar 16, 2011 at 18:23

Svisstack · Accepted Answer · 2011-03-16 18:22:18Z

4

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

answered Mar 16, 2011 at 18:22

Svisstack

16.7k8 gold badges69 silver badges101 bronze badges

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

Comments

Josh · Accepted Answer · 2011-03-16 18:23:01Z

3

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

answered Mar 16, 2011 at 18:23

Josh

1,8043 gold badges17 silver badges31 bronze badges

Comments

a1ex07 · Accepted Answer · 2011-03-16 18:23:05Z

1

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

answered Mar 16, 2011 at 18:23

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Comments

Marc B · Accepted Answer · 2011-03-16 18:22:28Z

0

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.

answered Mar 16, 2011 at 18:22

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

weird mysql_fetch_array error

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related