How to add zero(0) for data not found in a mysql table.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

How to add zero(0) for data not found in a mysql table.

Post by infomamun »

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?
User avatar
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.

Post by Christopher »

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';
(#10850)
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: How to add zero(0) for data not found in a mysql table.

Post by infomamun »

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';
Type of time field is Date/time.
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.
User avatar
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.

Post by Christopher »

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)
Post Reply