Skip to main content
D
dips045
Known Participant
October 13, 2011
Answered

MySQL PHP help - SUM(total)

  • October 13, 2011
  • 1 reply
  • 804 views

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?

This topic has been closed for replies.
Correct answer bregent

>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.

1 reply

B
bregent
Participating Frequently
October 13, 2011

>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.

D
dips045Author
Known Participant
October 13, 2011

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

B
bregentCorrect answer
Participating Frequently
October 13, 2011

>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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices