up vote 0 down vote favorite
1

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

share|improve this question
3  
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 Oct 17 '11 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 Oct 17 '11 at 17:38

3 Answers

up vote 3 down vote accepted

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

share|improve this answer
That worked! Many thanks :) – Sabharish Oct 17 '11 at 17:56
Ellegant solution =D – Felipe Alcacibar Oct 17 '11 at 17:59
up vote 5 down vote

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);
}
share|improve this answer
Isn't there any other way? Coz, the array $weight is dynamic in this case. – Sabharish Oct 17 '11 at 17:42
'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. – Clive Oct 17 '11 at 17:45
Got it. Is it possible to convert the current array keys to the strings as in the table? – Sabharish Oct 17 '11 at 17:51
Not 100% sure what you mean but something like this maybe: $new_weights = array('Elevation' => $weights[0], 'Distance from the sea' => $weights[1], //etc); – Clive Oct 17 '11 at 17:53
@Sabharish , the weight is dynamic but the sequence it's arranged is same as the indicators columns values. – punit Oct 17 '11 at 18:09
show 1 more comment
up vote 2 down vote

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

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.