db_query() function in a foreach cycle - only the first query is getting executed (D6)

Question

I have a query, which I have to run in a foreach cycle.
I run the query with db_query() function.
My code looks similar to this one (of course, this is just an example code with example values):

$query = '
    UPDATE mytable SET some_fields = "%s"
    WHERE
    some_fields = "%s"AND
    other_field = "%s" AND 
    another_field = "%s"
    ';

foreach($blabla as $index => $value){
    $prev_value = 'these variables are set in the meantime too...';
    $other_field_value = 'blabla';
    $another_field_value = 'blablabla';

    if($this_is_my_condition){
        $query_result = db_query( $query,
            $value,
            $prev_value,
            $other_field_value,
            $another_field_value
        );

        $affected_rows = db_affected_rows();

        if( empty($query_result) or empty($affected_rows) ){
            $unsuccessful_queries[] = 'blablabla '.$index.', '.$value;
        }
    }
}

if(!empty($unsuccessful_queries)){
    $errorMsg = NULL;
    foreach($unsuccessful_queries as $index => $value){
        $errorMsg .= 'Setting '.$value.' parameter was unsuccessful!<br />'."\n";
    }
    throw new ParameterSettingException($errorMsg);
}

In theory, my query is OK, because it works for just one query perfectly with the same values (if it has to be run depending on my condition ( in the example above, $this_is_my_condition ) ), BUT it doesn't work for multiple queries, the rest of the queries are getting simply ignored: my $unsuccessful_queries array is full of the other to-be-updated stuffs that would have to be executed.
The values are correct (that's for sure, because these work with the same values, but with just ONE query!!).

What can be the source of the problem? Why can I run only ONE query, why are the others getting ignored?

Thanks in advance!

Answer 1

I'm not sure if checking the return of $query_result against empty() is meaningful, since the only indication query failed is if it returns FALSE. Same for $affected_rows, since the query may be successful but not necessary change any rows.

I'd suggest installing Devel module because it has a feature which records and prints all DB queries on a page load. This would enable you to see the actual query(ies).

Answer 2

Finally I found the solution. The reason of the problem was a stupid mistake.
Shortly: using var_export to convert a boolean value to string (false ==> "false") in every cycle step, and giving its each time converted return result to the db_query function as a parameter.

Longer explained:
There is an enum field in my own MySQL table, which looks like this:

`is_own` enum('true','false')

So true or false values were stored as a string, this way there were only these two possible values to set. It could have also been set as 0 or 1 (or enum('0', '1') is another possible solution), but this way I thought it will be simpler to read later, and I limited the opportunities to just two values.
To pass a boolean value to db_query(), firstly I had to convert it to a string, so for example the false value (as a boolean) had to be converted to "false" (as a string), not to be casted later to 0 (because if it's casted, the update doesn't affect any rows, since for example 'false' (as a string) isn't equal to 0 in a MySQL-query). To do this, I used the following code snippet (using var_export):

$is_own = false;

$is_own = var_export($is_own, true);

This normally works perfectly (so it converts boolean true to string "true"), BUT the problem was that for some stupid reason I put this conversion in the cycle like this:

$is_own = false;

// ....   

foreach($blabla as $index => $value){

    // here's the mistake...!
    $is_own = var_export($is_own, true); 

    // ....
    $query_result = db_query( $query,
        $value,
        $prev_value,
        $other_field_value,
        $is_own // <--- !!
    );
    // ....
}

so this way $is_own variable's value (false) was converted in every cycle step like this:

1. 'false'
2. '\'false\''
3. '\'\\\'false\\\'\''
4. '\'\\\'\\\\\\\'false\\\\\\\'\\\'\''
5. etc........

This way it's not a miracle that all of my UPDATE querys were executed successfully, but only the first resulted in some affected rows...
After some headbanging, I put my var_export function outside the cycle (before foreach), and now everything works perfectly. (This would be a bigger job to modify the field to INT values (0, 1), so I leave it this way.)

Anyway, thanks.