QUERY problem

retrive data between user input dates.

[rognales]

hye guys.

I neeed help to extract data from the query where i need for generating graph.(jpgraph)

eg : table complaint
date
---------
2007-04-16
2007-04-17
2007-04-17
2007-04-17
2007-04-18
2007-04-18

I would like then to count every day's data( in this case result should be 16-1, 17-3, 18-2)

Result store in different variables or any ways i can pass it to jpgraph ($plot = array($data1,#data2....)

My current query right now would be

SELECT * FROM complaint WHERE time BETWEEN '2007-04-16' AND '2007-04-19';
(I plus 1 end date to include the 18th)

Then i use myqsl_num_rows to retrieve the value. However it return total query of given dates. There must be something wrong with my query.

Of course the two dates above, supposedly referring to user input.

Much help appreciated guys.Thx in advance.

[fischermx]

Most likely it is MySQL which is being buggy.
But anyway, you just have to do:

SELECT Count(*) FROM complaint WHERE time BETWEEN '2007-04-16' AND '2007-04-19';

To count the number of rows and then read the 1-row/1-column dataset in whichever hosting language you're using.

[BananaFish]

The query posted last will just give the number of rows in the entire query, if you want it broken down by date you need the group by clause. Here's an example without any error checking:

$sql="SELECT Count(*) as cnt,time FROM complaint WHERE time BETWEEN '2007-04-16' AND '2007-04-19' group by time order by time";
$data="";
$result=mysql_query($sql);
while($row=mysql_fetch_row($result)){
$data.="$row[1]\t$row[0]\n";
}
echo $data;

[BananaFish]

In the previous example, if you have alot of data, you'd want to have an index on the time field.

[rognales]

thx BananaFish. That sort my problem out.
But theres another question arising. What if i want to display the dates that is empty?
Let's put it into table

date
------
2007-04-10
2007-04-16
2007-04-17
2007-04-17
2007-04-17
2007-04-18
2007-04-18

I want to generate graph using the output of the query. Therefore i need everyday data even if is empty..

Any thought guys?