PDO Update 1 column multiple rows with array

Question

I am struggling to workout a good method to update one column of my wcx_options table.

The new data is sent fine to the controller but my function isn't working at all.

I assumed i could loop through each column by option_id updating with the values from the array.

The database:

wcx_options Database

I update the option_value column with the new information via a jQuery AJAX Call to a controller which then calls a function from the backend class.

So far i have the following code:

if(isset($_POST['selector'])) {

    if($_POST['selector'] == 'general') {
        if($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' && isset($_POST['token']) 
        && $_POST['token'] === $_SESSION['token']){

            $site_name = $_POST['sitename'];
            $site_url = $_POST['siteurl'];
            $site_logo = $_POST['sitelogo'];
            $site_tagline = $_POST['sitetagline'];
            $site_description = $_POST['sitedescription'];
            $site_admin = $_POST['siteadmin'];
            $admin_email = $_POST['adminemail'];
            $contact_info = $_POST['contactinfo'];
            $site_disclaimer = $_POST['sitedisclaimer'];
            $TimeZone = $_POST['TimeZone'];

            $options = array($site_name, $site_url, $site_logo, $site_tagline, $site_description, $site_admin, $admin_email,$contact_info, $site_disclaimer, $TimeZone);

            // Send the new data as an array to the update function
            $backend->updateGeneralSettings($options);
        }
        else {
            $_SESSION['status'] = '<div class="error">There was a Problem Updating the General Settings</div>';
        }
    }
}

This is what i have so far in terms of a function (It doesnt work):

public function updateGeneralSettings($options) {
    $i = 1;
    foreach($options as $option_value) {
        $where = array('option_id' => $i);
        $this->queryIt("UPDATE wcx_options SET option_value='$option_value' WHERE option_id='$where'");
    $i++;
    }
    if($this->execute()) {
        $_SESSION['success'] = 'Updated General Settings Successfully';
    }
}

CodeX · Accepted Answer · 2014-09-15 15:18:51Z

1

With the given DB-layout i'd suggest to organize your data as assiciative array using the db fieldnames, like:

$option = array(
        'site_name' => $_POST['sitename'],
        'site_url' => $_POST['siteurl'],
        // etc.
        'timeZone' => $_POST['TimeZone']
);

And than use the keys in your query:

public function updateGeneralSettings($options) {
    foreach($options as $key => $value) {
        $this->queryIt("UPDATE wcx_options SET option_value='$value' WHERE option_name='$key'");
    if($this->execute()) {
        $_SESSION['success'] = 'Updated General Settings Successfully';
    }
 }

}

(However, are you sure, you do not want to have all options together in one row?)

edited Sep 15, 2014 at 15:18

CodeX

3131 silver badge14 bronze badges

answered Sep 15, 2014 at 13:12

fast

8857 silver badges15 bronze badges

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

6 Comments

CodeX Over a year ago

I thought about storing the options in one row as an array, is that what you mean? or do you mean have a column for each value?

CodeX Over a year ago

This code does not work for me, i don't even get an error, the database does not update

fast Over a year ago

@CodeX I meant to have one column for each option - however, if options change dynamically your approach might be the way to go :)

fast Over a year ago

@CodeX I don't know the further context (e.g. queryIt()). Please check, if the created statements come out as needed (echo or log) and try the SQL in the console/admin tool.

CodeX Over a year ago

I will look into a few ways of doing this, thanks for your input

|

S.Pols · Accepted Answer · 2014-09-15 13:15:25Z

0

Change your query, you try to use an array as where condition. In the syntax you used that won't work. Just use the counter as where condition instead of define a $where variable. Try this:

public function updateGeneralSettings($options) {
    $i = 1;
    foreach($options as $option_value) {
        $this->queryIt("UPDATE wcx_options SET option_value='$option_value' WHERE option_id='$i'");
        $i++;
    }
    if($this->execute()) {
        $_SESSION['success'] = 'Updated General Settings Successfully';
    }
}

answered Sep 15, 2014 at 13:15

S.Pols

3,4592 gold badges24 silver badges44 bronze badges

3 Comments

CodeX Over a year ago

This works if i set the query as a variable ie. $query = "UPDATE wcx_options SET option_value='$option_value' WHERE option_id='$i'" and $this->queryIt($query);

CodeX Over a year ago

Not sure its the best approach though

S.Pols Over a year ago

It should work directly. What if you change WHERE option_id='$i'" in WHERE option_id=".$i?

Collectives™ on Stack Overflow

PDO Update 1 column multiple rows with array

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related