PDO update query pushes submit input value on all columns

Question

I have a PDO update query gets the $_POST (or any other key-value array) and writes up the UPDATE query in respect to the inputs given.

I have an exclude array that I can specify keys to not include in the SQL query, such as the submit key and value of the form (action_update_survey, in this case.).

I create the SQL query by iterating through the array via foreach to firstly create the query and insert the parameter placeholders and secondly to bind the parameters to the parameter placeholder within the query.

Here is my code:

    function save_survey($post){    
        global $pdo; 
        $exclude_names = array('action_update_survey');

        $wp_userid = get_current_user_id();

        $update_survey_query = "UPDATE kwc_surveysessions SET ";
        foreach($post as $key=>$value){
            if(!in_array($key, $exclude_names)) $update_survey_query .= $key." = :".$key.", ";
        }
        $update_survey_query = rtrim($update_survey_query, ", ")." WHERE wp_userid=:wp_userid";
        $update_survey = $pdo->prepare($update_survey_query);       
        print_r($update_survey_query);

        foreach($post as $key=>$value){
            if(!in_array($key, $exclude_names)){
                $update_survey->bindParam($key, $value);
            }
        }
        $update_survey->bindParam("wp_userid", $wp_userid);
        $update_survey->execute();
    }

After executing my function following a post, all text columns in my database are set to the value 'Save', which is the value of the submit input, of name *action_update_survey*, which is strange, because it should be excluded from both foreach loops, which assign the keys and values.

Printing the PDO query before executing shows that there's been no setting of the excluded input anywhere in my query:

UPDATE kwc_surveysessions SET s1q1 = :s1q1, s1q2 = :s1q2, s1q7 = :s1q7, s1q8 = :s1q8, s1q9 = :s1q9, s1q10 = :s1q10, s1q11 = :s1q11, s2q6 = :s2q6, s3q7 = :s3q7 WHERE wp_userid=:wp_userid

Any idea what would be causing the submit input to push its value into all my fields?

Well, After having read half of your post, I can tell you what you are doing is wrong. Just the fact that you are using `global` is enough evidence to discard your approach. — samayo, Oct 12 '13 at 18:04
How so? assuming my $pdo object is correct, I've always used a global variable to pass my object to my functions, without any problems. — Prusprus, Oct 12 '13 at 18:06
I am not going to go to details. But, trust me. Never use Globals. — samayo, Oct 12 '13 at 18:15
http://stackoverflow.com/questions/5166087/php-global-in-functions — samayo, Oct 12 '13 at 18:18

score 1 · Accepted Answer · answered Oct 12 '13 at 18:05

1

The most probable cause is that bindParam() passes values by refference.

Try using an array like this:

arr = array();
foreach($post as $key=>$value){
    if(!in_array($key, $exclude_names)){
        arr[$key] = $value;
    }
}
$update_survey->execute($arr);

and use "arr" to execute the query.

answered Oct 12 '13 at 18:05

Udan

5,213
1
26
34

That was it! I always forget about checking if my values are passed by reference. So I assuming what was happening is that my $value variable in my second loop was indefinitely set to the last value passed in the first loop, which is 'Save'. Is this because the key=>value format of the foreach loop uses references too? – Prusprus Oct 12 '13 at 18:08
$key and $values are available only in the respective loop and would be a waste of resources not to use same memory allocation for them. I'm not very knowledgeable with php's insides but that is how I would do it. – Udan Oct 12 '13 at 18:14

PDO update query pushes submit input value on all columns

1 Answers1