mysql_fetch_assoc() Problems

vener

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ajax_creditordebtororder_edit.php on line 327

weird thing is if i change the $retval variable to $xxx, it works

foreach($id as $value){
              if ($value!=''){
                $sql = "SELECT * FROM csim_$creditordebtorS" . "orderitem WHERE billid = '$value'";
                //debug($sql);
                $retval = DBQuery($sql);
                if (!$retval){ systemlog($sql . '-' . mysql_error()); header("location:$creditordebtor"."order_update.php?msg=3[]Error Occured, Please Retry");}
                while ($row = mysql_fetch_assoc($retval)) { //line 327
                  $sql = "INSERT INTO csim_$creditordebtorS" . "invoiceitem (ordr, refid, billid, product_code, description_1, description_2, $inoutnumber, unit_price, discount, dprice, dpricec, item_total) VALUES ('$order', '$value','$billid', '{$row['product_code']}', '{$row['description_1']}', '{$row['description_2']}', '{$row[$inoutnumber]}', '{$row['unit_price']}', '{$row['discount']}', '{$row['dprice']}', '{$row['dpricec']}', '{$row['item_total']}') ";
                  //debug($sql);
                  $retval = DBQuery($sql);
                  if (!$retval) { systemlog($sql . '-' . mysql_error()); header("location:$creditordebtor"."order_update.php?msg=3[]Error Occured, Please Retry");}
                  $order++;
                }

          }
        }

SunsetKnight

vener;10910609 wrote:

$sql = "SELECT * FROM csim_$creditordebtorS" . "orderitem WHERE billid = '$value'";

Not sure if this is part of the problem, but I can see a problem with this query. First off, is "csim_$creditordebtorS" the name of your table inside your DB?

If so, php might be getting confused with the $ symbol.

Also, where you are concatenating the query, there needs to be a space between the table and the given alias.

vener

SunsetKnight;10910612 wrote:
Not sure if this is part of the problem, but I can see a problem with this query. First off, is "csim_$creditordebtorS" the name of your table inside your DB?

If so, php might be getting confused with the $ symbol.

Also, where you are concatenating the query, there needs to be a space between the table and the given alias.

The query is correctly generated, the query goes though(if not, the line 326 will fire), I just have problems with line 327.
PS Changing the query to all string gives the same result

As I said,changing the variable name from $retval to $xxx will make the script work for some reason.

SunsetKnight

Try replacing:

 $retval = DBQuery($sql);

With:

 $retval = mysql_query($sql);

vener

SunsetKnight;10910617 wrote:
Try replacing:
 $retval = DBQuery($sql); 
With:
 $retval = mysql_query($sql); 

still the same

laserlight

vener wrote:
As I said,changing the variable name from $retval to $xxx will make the script work for some reason.

Try changing all occurrences of $retval to $xxx, and then it will fail again.

The reason is simple: by assigning to $retval in the inner loop, you can no longer correctly call mysql_fetch_assoc($retval) in the outer loop. As such, you need to change one set of occurrences of $retval, which explains why a change to $xxx worked (though of course $xxx is a poor name).

A possibly even better solution is to redo your SQL statements such that you do not need to execute SQL statements in a loop, especially since the execution of SQL statements can be relatively expensive.

vener

Noticed and workaround written. Thanks for the help

laserlight

You're welcome 🙂
Remember to mark this thread as resolved, though I still think that you would be better off changing your code to execute just one SQL statement.

SunsetKnight

laserlight;10910623 wrote:
Try changing all occurrences of $retval to $xxx, and then it will fail again.

The reason is simple: by assigning to $retval in the inner loop, you can no longer correctly call mysql_fetch_assoc($retval) in the outer loop. As such, you need to change one set of occurrences of $retval, which explains why a change to $xxx worked (though of course $xxx is a poor name).

A possibly even better solution is to redo your SQL statements such that you do not need to execute SQL statements in a loop, especially since the execution of SQL statements can be relatively expensive.

Why is this? It seems like it should work because $retval is assigned to the same query, shouldn't that pull the next record and be tested in the while loop? Or if anything pull the same record over and over?

laserlight

SunsetKnight wrote:
Why is this? It seems like it should work because $retval is assigned to the same query, shouldn't that pull the next record and be tested in the while loop? Or if anything pull the same record over and over?

You cannot just say that since this appears on both lines in question:

$retval = DBQuery($sql);

that it executes the same query in both cases. The contents of $sql differ between the two lines. If indeed $sql remained the same, then you would be right to conclude that an infinite loop should happen. As it is, the inner loop set $sql to an insert statement, so there is no result to retrieve.

bradgrafelman

laserlight suggested using only one query, with no loop, because anytime you see a SQL query (usually a SELECT) followed by multiple queries in a while() loop it's often a giant red flag waving and shouting "I'M NOT OPTIMIZED AT ALL!" :p

EDIT: Nvm, he posted as I was typing.

SunsetKnight

laserlight;10910634 wrote:
You cannot just say that since this appears on both lines in question:
$retval = DBQuery($sql);
that it executes the same query in both cases. The contents of $sql differ between the two lines. If indeed $sql remained the same, then you would be right to conclude that an infinite loop should happen. As it is, the inner loop set $sql to an insert statement, so there is no result to retrieve.

Ahh okay, I over looked that reassignment of the $sql variable, now that makes sense.

Thanks

Weedpacket

I second (third) laserlight's and bradgrafelman's observation about major red flags. I'd even go further and say that if the application is taking stuff out of the database only to put it back in again (which is happening here) then that's already a red flag: the application is doing work that should have been delegated to the database.

The database activity in this code could be achieved with a single INSERT ... SELECT statement; the database's own error log should contain details of exactly what failed if something did.