1

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!

0

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).

  • Thanks for your answer. The value FALSE is considered to be empty, look at php.net: php.net/manual/en/function.empty.php . Empty function covers more checking options than I would simply check if the returned value is false, that's why I'm using it - if the query was not executed correctly, the empty($query_result) will return true. But if the query was executed correctly, BUT there were no affected rows, I also consider it an unsuccessful query. The truth is that I'm using Drupal's functions in my own EXTERNAL processing file, not as a module, so I can't use Devel. – Sk8erPeter Oct 20 '11 at 11:18
  • Later I will create it as a module, but I didn't have time to do it yet, but Drupal's built-in functions (like the db layers' functions, e.g. db_query() and session handling and others) work perfectly in my own external processing file too (which means it's only "lazy coupled" with Drupal this way). But the main problem is that db_query() can't be executed correctly in a foreach cycle, like as my table was locked or I don't really know the main reason... :( – Sk8erPeter Oct 20 '11 at 11:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.