Add information in mysql

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

Answers


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`

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'];

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

Select all months within given date span, including the ones with 0 values

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!


Need Your Help

Authentication headers not sending in HttpWebRequest

httpwebrequest

I am connecting to a service using a HttpWebRequest. In the service logs, there are Authentication errors being logged, even though the information is correct. The vendor who set up the server with...

How to disable child node selection on selecting parent node of a treeview

winforms treeview

Is there any property to disable child node selection when we select the parent node from a treeview