add information in mysql

Question

I have a table

id_user, hour, medition  
1 0  100  
1 1  101  
1 14 102  
2 5  108  
2 17 103

How I can complete the inexistent hours with a 0 value for any user?
example

1 0 100  
1 1 101  
1 2 0  
1 3 0  
...

Answer 1

You shouldn't do this, as it's redundant. Only add entries where someone has hours. In your program, just code it so that if a record is not found, then there are zero hours. An example, in PHP:

$row = mysql_fetch_array(mysql_query("select * from myTable where id_user = 1 and hour = 3;"));
if (!$row) $hours = 0;
else $hours = $row['medition'];

Answer 2

build a table hours, populate it with the numbers from 1 to 24, then use this to query with an outer join against your primary table. - replace nulls with 0

edit to add quick example:

SELECT id_user, hour, NVL(medition,0)
FROM `hours` as h
LEFT JOIN `myTable`
    ON `hours`.`hour` = `myTable`.`hour`

Answer 3

Check out my solution to a similar problem regarding dates at;

http://stackoverflow.com/questions/3093924

You can create a table for the hours, as suggested by Randy, instead of the dates that I created, should be easy enough then to modify the query to return the data you need...

Hope it helps!