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