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?
How to add zero(0) for data not found in a mysql table.
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: How to add zero(0) for data not found in a mysql table.
What type of field is the`time` field? You should be able to do:
SELECT * FROM timeValue WHERE time>='10:45' AND time<='10:50';
SELECT * FROM timeValue WHERE time>='10:45' AND time<='10:50';
(#10850)
Re: How to add zero(0) for data not found in a mysql table.
Type of time field is Date/time.Christopher wrote:What type of field is the`time` field? You should be able to do:
SELECT * FROM timeValue WHERE time>='10:45' AND time<='10:50';
Yes, your query can get timeValue from 10:45 and 10:50. But look at the database, there is no timeValue for 10:47 and 10:50.
I want that if no timeValue will be found in between 10:45 and 10:50, then there will be '0' for that specific time.
So the resulting array will be: $result = 300,350,0,400,450,0;
How to achieve that?
Regards.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: How to add zero(0) for data not found in a mysql table.
I think it would be easiest to loop through the results one minute at a time in the range and add the zero entries into the array. It might be easiest if you return the data times as timestamps and step 60 seconds an iteration.
(#10850)