MySQL PHP help - SUM(total)

[dips045]

Hi,

I am creating a report generator for my client. They have a form where people can order some samples. The samples ordered goes into a database. (The sample name and date gets inserted into the database )

The report I have created allows my client to search between dates to check how many samples have been ordered within two dates.

I think they mostly check it monthly....

The first month worked fine. When the client searched between two dates the correct amount of samples ordered appeared. However now that we're in the 2nd month - when I search between two dates the number is a lot higher. When I search how many samples have been ordered on one day the number is correct - but is wrong if I search from yesterday to today....

I'm not sure why this is happening. This is my SQL query

("SELECT  samplename, date, SUM(total) FROM tbl_samplesordered WHERE date BETWEEN '$fromdate' AND '$todate' GROUP BY samplename");

The date format is dd/mm/yy

Any ideas what could be going wrong?

[bregent]

>The sample name and date gets inserted into the database

What is the datatype of the date field? If it's a datetime, or timestamp and you don't include a time in your query, it defaults to midnight.

If it is a date, then we need to see some sample data and actual query results.

[dips045]

Hi Bregent,

The datatype is actually varchar!

It should be datetime shouldn't it!?

I couldn't understand timestamp so I've never used it...

would you recomend I change the datatype?

many thanks for your help

[bregent · Accepted solution]

>The datatype is actually varchar!

What was the logic behind selecting varchar?

>It should be datetime shouldn't it!?

It should be some type of date related type. If you need to capture time of day, then make it a datetime. If you only care about the date, make it a date. Note that if it is a datetime, and your queries do not supply the time, it will default to midnight. This is a very important point to consider when writing queries.

>would you recomend I change the datatype?

Absolutely. You should select the datatype that reflects the actually data to be stored.

[dips045]

I'll definitely change it! thanks for your help...

Will I have to start from scratch (as in delete the data) or will changing the datatype solve the problem and work with the data currently in the database?

Datetime is appropriate for my client... at the moment I insert the date in this format "date("d/m/y");"

is this the correct way for Datetime datatype?