PHP: Saving multiple input fields in rows based on ID or date of row

Question

I'm not entirely certain how descriptive that title is, but:

In Wordpress I have a custom table of numerical data which gets added to monthly (via a spreadsheet import). As such, I'm using $wpdb to pull the contents of that table, which gets returned as an array of stdClass objects:

Array
(
    [0] => stdClass Object
        (
            [id] => 553
            [assocID] => 1
            [title] => Afghanistan
            [weight_political] => 8.2
            [indicator_political] => 0.0
            [weight_security] => 9.5
            [indicator_security] => 0.0
            [weight_criminal] => 9.5
            [indicator_criminal] => 1.0
            [weight_governance] => 9.0
            [indicator_governance] => 0.0
            [overall_score] => 9.2
            [datamonth] => 2013-08-18
            [active] => 1
        )

    [1] => stdClass Object
        (
            [id] => 369
            [assocID] => 1
            [title] => Afghanistan
            [weight_political] => 8.4
            [indicator_political] => 0.0
            [weight_security] => 9.5
            [indicator_security] => 0.0
            [weight_criminal] => 9.3
            [indicator_criminal] => 1.0
            [weight_governance] => 9.0
            [indicator_governance] => 0.0
            [overall_score] => 9.2
            [datamonth] => 2013-07-05
            [active] => 1
        )

)

This is fine and gives me exactly what I want, which I can then echo out as a series of input fields to edit (see my jsFiddle for example output).

However, what I can't get my head around (and I'm pretty sure I'm just being super-stupid here and missing something obvious) is saving the appropriate data back to the appropriate database row.

I've considered setting the input names as arrays (i.e. <input name="weight_security[]"...), but it's clearly missing a reference. I want the August 2013 data (id 553) to be saved to the August 2013 row; I just can't figure out where or how to implement that reference.

In this example there's only 2 rows, but there could be as many as 14 once this is live, and will be added to monthly, so the ability to modify multiple rows simultaneously is key.

Any help is greatly appreciated.

Take a look at the $wpdb->update() method: codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows — Jeremy Harris
– Jeremy Harris, Commented Sep 10, 2013 at 15:54
Sorry @cillosis, I think you misunderstood the question; the fact that I'm using Wordpress is incidental. I know how to use $wpdb->update(), but I'm displaying 14 rows of 9 identically-named input fields. I want to know how best to associate each row of inputs with with appropriate database table row. — indextwo
– indextwo, Commented Sep 10, 2013 at 15:59

indextwo · Accepted Answer · 2013-09-11 18:52:56Z

To answer my own question: the key was in multidimensional arrays for the input names; so, instead of multiple fields like:

<input name="weight_political" value="8.2" />
<input name="indicator_political" value="0" />

I decided to go up a level and wrap these field names in an array called geodata and, crucially, preface that with an array key that was the ID of the row that needed updating, thus:

<input name="geodata[553][weight_political]" value="8.2" />
<input name="geodata[553][indicator_political]" value="0" />

Then, when parsing the posted data, I can loop through the geodata array to get the row ID (as the key), then the data as an associative array within that:

<?php
if ($_POST["geodata"]) {

        foreach ($_POST["geodata"] as $rowID=>$valuesArray) {

            $queryArray = array();

            foreach ($valuesArray as $key=>$value) {
                $queryArray[] = $key . ' = '  . $value;
            }

            $queryString = implode(', ', $queryArray);

            $wpdb->query("UPDATE $table_name SET $queryString WHERE id = $rowID");

        }
    }
?>

Also:

To try to be as efficient as possible (since an initial 9x14 grid would return 126 values to update, many of them needlessly), I wrote a small jQuery function to detect when an input field was changed; if so, then its name was changed from data[id][identifier] to geodata[id][identifier] - that way, $_POST['geodata'] would only send the values that needed to be updated, rather than the whole batch lot. jsFiddle below, for those who are interested.

http://jsfiddle.net/hkVTn/1/

Collectives™ on Stack Overflow

PHP: Saving multiple input fields in rows based on ID or date of row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest