How to add zero(0) for data not found in a mysql table.
Posted: Sun Sep 09, 2012 2:15 am
Hi there,
Suppose I have a mysql table with following data:
table: timeValue
==================
id II time II Value
================
01 II 10:45 II 300
----------------------
02 II 10:46 II 350
----------------------
03 II 10:48 II 400
----------------------
04 II 10:49 II 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?
Suppose I have a mysql table with following data:
table: timeValue
==================
id II time II Value
================
01 II 10:45 II 300
----------------------
02 II 10:46 II 350
----------------------
03 II 10:48 II 400
----------------------
04 II 10:49 II 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?