Search in MySQL Database based on Date Range provided by the user

Question

I'm a novice in PHP & MySQL coding and still working my way through it.. So, little help on this will be much appreciated:

I have a table that keeps record of certain events. Here is how my table looks like:

Id - Event_Name - Event_Date - Event_Charges
---------------------------------------------
1 - Event 1    - 10/10/2010 - $100
2 - Event 2    - 10/31/2010 - $200
3 - Event 3    - 11/12/2010 - $150
4 - Event 4    - 12/01/2010 - $175

The objective here is to List the name of Events and total up the charges for a particular Date range...

My front end form looks like this:

From Date: ____________
To Date: ______________

The user enters these two fields and i'm supposed to List the Events falling in that date range, and total up the charges.

Please note that Event_Date field is not of field type "date" in MySQL.. Should i have the Event_Date field of field type "date".

Answer 1

In order to use date operations, the event_date data type needs to be one of:

• DATETIME
• DATE
• TIMESTAMP

Once that's in place, you can use:

  SELECT t.event_name,
         SUM(t.event_charges) AS total_charges
    FROM YOUR_TABLE t
   WHERE t.event_date BETWEEN STR_TO_DATE($from_date, '%Y-%m-%d')
                          AND STR_TO_DATE($to_date, '%Y-%m-%d')
GROUP BY t.event_name

...which brings up the issue of you needing to enforce a date format. That's why I provided an example using STR_TO_DATE, so the string from the HTML form is converted into a MySQL DATETIME data type for comparison.

Answer 2

Ok, so i did some head banging and this is what i came up with:

SELECT t.event_name, SUM(t.event_charges) AS total_charges FROM YOUR_TABLE t WHERE t.event_date1 BETWEEN STR_TO_DATE($from_date, '%Y-%m-%d') AND STR_TO_DATE($to_date, '%Y-%m-%d') OR t.event_date2 BETWEEN STR_TO_DATE($from_date, '%Y-%m-%d') AND STR_TO_DATE($to_date, '%Y-%m-%d') GROUP BY t.event_name

This works like a charm for my Report! Much thanks!!