September 9th, 2012, 06:28 AM
How to add zero(0) for data, not found in a mysql table
Suppose I have a mysql table with following data:
id ǀǀ time ǀǀ Value
01 ǀǀ 10:45 ǀǀ 300
02 ǀǀ 10:46 ǀǀ 350
03 ǀǀ 10:48 ǀǀ 400
04 ǀǀ 10:49ǀǀ 450
in php script, First I create an array of time. The array will begin from 10:45 and continue up to 10:50:
$time = array(10:45, 10:46, 10:47, 10:48, 10:49, 10:50);
Now I want to extract value of each time from the mysql table (timeValue) by something like this sql:
SELECT * FROM timeValue WHERE time INBETWEEN 10:45 AND 10:50;
Please watch here that in the mysql table, there is no entry for 10:47 and 10:50. But I want a blank value( or Zero/ "0") if there is no value for any time.
So I expect that the returned value will be like this: $data = 300,350,0,400,450,0; (for 10:45, 10:46, 10:47, 10:48, 10:49, 10:50 respectively).
How to create a sql query to do that?
I know it is possible by creating a loop. But I don't want to use loop if any sql query can do that at once.
Do you have any idea?
September 9th, 2012, 08:34 AM
would actually be easy if you were talking about datetime values and not time values
as it is, you need a LEFT OUTER JOIN from the time values to your data table, in order to generate a result row for time values that are missing from the data table
this means you will ahve to store the array of times in a table
it would be easier if they were datetimes, because then you could generate the time array values "on the fly" from a numbers table
September 9th, 2012, 09:34 AM
I think it would not create a big problem, because I am already using time in mysql table in date/time column format. In that case, I have to store data in millisecond such as 10:20:30, and I can sort the time using mysql query as well.
Left join can be implemented easily to solve this problem, but it would be better if it could be resolved in different way. Main reason is that the starting and ending date will be selected by the user by a html form. start date might be 00:00:00 to 23:59:99. So, it would be better if can be done by sql query instead.
By the by, do you have any idea about temporary table? I mean, let userX is sending query to create and store data in a temporary table and userY is sending query to create & store data in the same time. Will the temporary table will be different for userX and userY? In another word, if userX will input any data to a temporary table, will it affect the temporary table of userY if occurred at the same time or those two temporary tables are completely different and separate for each user of a website?
Any better idea will be appreciated.
September 9th, 2012, 01:41 PM
so are you saying that the table actually contains a datetime value for this purpose?
try this... first, create this table --
then run this query --
CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY ) ;
INSERT INTO numbers VALUES (0),(1),(2),(3),...,(11) ;
you can use this for your LEFT OUTER JOIN
SELECT '2012-09-09 09:37' + INTERVAL n HOUR AS thetime
September 10th, 2012, 03:32 AM
I am sorry but could not understand what you said.
Originally Posted by r937
After creating the table what to do?
September 10th, 2012, 07:45 AM
did you run that query i suggested?
Originally Posted by infomamun
this will generate the times you want for your LEFT OUTER JOIN
do you know how a LEFT OUTER JOIN works?
September 12th, 2012, 01:40 AM
No, I did not run the query yet but can understand the way you are trying to solve. Anyway thanks for your reply.
I think, although I don't, the easier way is to create a loop for returning whole time series. At least, it will reduce the load on mysql database.
September 12th, 2012, 06:13 AM
on the contrary, it will increase the load on mysql
Originally Posted by infomamun