CODEWITHSUNDEEP
phpmysqlarrays
Sabharish
Sabharish

Reputation: 101

Updating a MySQL table column with php array

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

Upvotes: 0

Views: 7564

Answers (3)

Felipe Buccioni
Felipe Buccioni

Reputation: 19668

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

Upvotes: 2

Peter
Peter

Reputation: 16923

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

Upvotes: 3

Clive
Clive

Reputation: 36957

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);
}

Upvotes: 6

Related Questions