Updating a MySQL table column with php array

Question

I've got the following array $weight and I want to store these values to the weight column in mysql table.

Array
(
    [0] => 12
    [1] => 14
    [2] => 16
    [3] => 9
    [4] => 7
)

The database table structure is,

+----------------------------+--------+------+
| Indicators                 | Weight | Rank |
+----------------------------+--------+------+
| Elevation                  |      0 |    0 |
| Distance from the sea      |      0 |    0 |
| Natural Coastal Protection |      0 |    0 |
| Water Bodies               |      0 |    0 |
| Precipitation              |      0 |    0 |
+----------------------------+--------+------+

I'm expecting the result as,

+----------------------------+--------+------+
| Indicators                 | Weight | Rank |
+----------------------------+--------+------+
| Elevation                  |     12 |    0 |
| Distance from the sea      |     14 |    0 |
| Natural Coastal Protection |     16 |    0 |
| Water Bodies               |      9 |    0 |
| Precipitation              |      7 |    0 |
+----------------------------+--------+------+

Tried the following query, but it doesn't seem to insert.

foreach($weight as $value)
    {
    $insert_weight="UPDATE geographic_main 
    SET Weight='$value' 
    WHERE Indicators=('Elevation','Distance from the sea',
    'Natural Coastal Protection','Water Bodies', 'Precipitation') ";

I'm not sure if I'm right in the WHERE part. Please suggest. }

To be safe, you should also store a better key in your array. Otherwise if that table gets rearranged for whatever reason, your numbers won't line up anymore and you'll be inserting bad data. — Marc B
– Marc B, Commented Oct 17, 2011 at 17:37
It seems to me that you have several problems. First of all I don't see the mysql_query($insert_weight) statement. Besides, if you would done a thing like what you are doing in the WHERE you have to use the IN operator. Anyway this would not do what you hope. — Aurelio De Rosa
– Aurelio De Rosa, Commented Oct 17, 2011 at 17:38

Clive · Accepted Answer · 2011-10-17 17:38:49Z

6

You need to do each update individually, at the moment it looks like you're doing a hybrid of that and trying to update all the rows as once. You're going to need something to identify which record you're trying to update so I'd recommend this:

$weights = array(
  'Elevation' => 12,
  'Distance from the sea' => 14,
  'Natural Coastal Protection' => 16,
  // etc.
);

foreach ($weights as $key => $val) {
  $sql = "UPDATE geographic_main SET Weight = '$val' WHERE Indicators = '$key'";
  mysql_query($sql);
}

answered Oct 17, 2011 at 17:38

Clive

36.9k8 gold badges89 silver badges113 bronze badges

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

5 Comments

Sabharish Over a year ago

Isn't there any other way? Coz, the array $weight is dynamic in this case.

Clive Over a year ago

'Fraid not, if you're updating a row in a table you're going to need something to identify that row. As you don't have an ID column you'll have to use the string value in Indicators as your identifier.

Sabharish Over a year ago

Got it. Is it possible to convert the current array keys to the strings as in the table?

Clive Over a year ago

Not 100% sure what you mean but something like this maybe: $new_weights = array('Elevation' => $weights[0], 'Distance from the sea' => $weights[1], //etc);

Punit Over a year ago

@Sabharish , the weight is dynamic but the sequence it's arranged is same as the indicators columns values.

Peter · Accepted Answer · 2011-10-17 17:53:34Z

3

you can add "index" or "id" column to table or use this code

$index = 0;
$q = mysql_query("SELECT Indicators FROM geographic_main");
while(list($indicator) = @mysql_fetch_row($q)) {
   mysql_query("UPDATE geographic_main SET Weight='".$weights[$index++]."' WHERE Indicators='$indicator'");
}

but this is not the right way

answered Oct 17, 2011 at 17:53

Peter

17k9 gold badges55 silver badges77 bronze badges

1 Comment

aniban Over a year ago

any other way without loop, if 1000 indicators I have to update then it produces 1000 query

Felipe Buccioni · Accepted Answer · 2011-10-17 17:56:33Z

2

you really need a id column in your table... because... how i recognize the current row i am? so if you don't want to use a id column you could make array indexes, or dictionaries/associative arrays.

$idx = array(
     'Elevation'                      => 0
    ,'Distance from the sea'          => 1
    ,'Natural Coastal Protection'     => 2
    ,'Water Bodies'                   => 3
    , 'Precipitation'                 => 4
);

foreach($idx as $name => $i) {
     mysql_query("UPDATE geographic_main SET Weight = '".$weights[$i]."' WHERE Indicators = '".$name."'";

}

but anyway is better to use an id because you get more approach or the db engine.

hope this be helpful.. have a nice day

Felipe

answered Oct 17, 2011 at 17:56

Felipe Buccioni

19.9k2 gold badges31 silver badges32 bronze badges

Collectives™ on Stack Overflow

Updating a MySQL table column with php array

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related