MYSQL, PHP update table

Question

I scanned the internet for two days without any result. I try to update empty cells in a MYSQL table with the same value as the preceding cell:

+-------+      +-------+
|ID |VAL|      |ID |VAL|
+-------+      +-------+
| 1 | 7 |      | 1 | 7 |
| 2 |   |      | 2 | 7 |
| 3 |   |      | 3 | 7 |
| 4 | 4 |      | 4 | 4 |
| 5 | 5 |after:| 5 | 5 |
| 6 |   |      | 6 | 5 |
| 7 |   |      | 7 | 5 |
| 8 | 8 |      | 8 | 8 | 
| 9 | 3 |      | 9 | 3 |
|10 |   |      |10 | 3 |
+-------+      +-------+

you can fetch previous row's val data and use it while inserting next subsequent rows. — Smita Ahinave, Jan 29 '16 at 07:37
or you can use cursor inside procedure, if your data is already inserted in table. — Smita Ahinave, Jan 29 '16 at 07:39
refer this ...May help you ..http://stackoverflow.com/questions/1345065/sql-query-replace-null-value-in-a-row-with-a-value-from-the-previous-known-value — Smita Ahinave, Jan 29 '16 at 07:47

score 0 · Answer 1 · answered Jan 29 '16 at 07:51

This is my solution, hope can help you

$old_datas = [
  [1,7],
  [2,null],
  [3,null],
  [4,4],
  [5,5],
  [6,null],
  [7,null]
]; // get old data from table as array : select * from table where 1 order by id ASC;

// set default pre value
$pre_val = 0;

foreach( $old_datas as $old_data ){
  if( !$old_data['VAL'] ){
    // update table set VAL=$pre_val where id=$old_data['ID']
  }else{
    $pre_val = $old_data['VAL'];
  }
}

score 0 · Answer 2 · answered Jan 29 '16 at 08:53

0

Thanks for your answers, this did finally the trick

UPDATE TABLE
SET VAL = (@n := COALESCE(VAL, @n))
ORDER BY ID

answered Jan 29 '16 at 08:53

Shanghai_ed

7
3

MYSQL, PHP update table

2 Answers2