Replacing NULL value in current row based on 'closest' matching 'earlier' row. (date based table)

Question

I have the following mysql table

+---------------------+------+
| time                | val  |
+---------------------+------+
| 2005-02-03 00:00:00 | 2.11 |
| 2005-02-04 00:00:00 | 2.11 |
| 2005-02-05 00:00:00 | NULL |
| 2005-02-06 00:00:00 | NULL |
| 2005-02-07 00:00:00 | 3.43 |
| 2005-02-08 00:00:00 | NULL |
| 2005-02-09 00:00:00 | NULL |
| 2005-02-10 00:00:00 | 5.66 |
| 2005-02-11 00:00:00 | 5.66 |
| 2005-02-12 00:00:00 | NULL |
+---------------------+------+

I want to create an algorithm (in PHP) that fill the NULL values based on the last non-null value. So the table will become the following

+---------------------+------+
| time                | val  |
+---------------------+------+
| 2005-02-03 00:00:00 | 2.11 |
| 2005-02-04 00:00:00 | 2.11 |
| 2005-02-05 00:00:00 |>2.11 |
| 2005-02-06 00:00:00 |>2.11 |
| 2005-02-07 00:00:00 | 3.43 |
| 2005-02-08 00:00:00 |>3.43 |
| 2005-02-09 00:00:00 |>3.43 |
| 2005-02-10 00:00:00 | 5.66 |
| 2005-02-11 00:00:00 | 5.66 |
| 2005-02-12 00:00:00 |>5.66 |
+---------------------+------+

I'm looking for clues on how to approach this situation. I'm using PHP-Laravel.

There is an SQLFiddle here for 'standard' SQL.

Answer 1

As comments indicate, you should be fixing this when you populate the table. That said, it can be done in PHP or MySQL. Here is one option:

SET @x:=0;
SELECT `time`, IF(val IS NOT NULL, @x:=val, @x) AS val
FROM yourtable
ORDER BY `time`;

Bear in mind that your result will change depending on ordering and WHERE and so on. Use SET @x:=0; to define your default value for cases when first row has NULL value.

If you need to fix the data permanently, rather than for single query, you can update the table with 'correct' values:

SET @x:=0;
UPDATE yourtable SET val=IF(val IS NOT NULL, @x:=val, @x) 
 ORDER BY `time`;