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 v开发者_StackOverflow中文版alue for any user?
example1 0 100 1 1 101 1 2 0 1 3 0 ...
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'];
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`
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!
精彩评论