mysql query design

[kidtangerine]

Hey Guys, I am writing a PHP application for determining fee
percentages based on semi-monthly commissions, and being a neophyte to
application development I cannot determine what is the best way to
query the the mysql db to chop the month in half.

Is there a MYSQL or PHP function to handle this sort of thing?

I tried to use BETWEEN (date) AND (date) in MYSQL which works fine for
the first through the 15, but wouldn't work for the 16-28,29,30,31.

I can write a function to determine the length of days in the month
before I run the query, but I wanted to make sure there wasn't some
handy dandy out there before I added the extra code.


Thanks,
Brad

[Dan Hess]

Off the top of my head I don't know of anything that is already written to do this, so I leave it to others to speak there.  I would say that it's just as easy to write a small function to calculate the middle for you and then build your date from that.

So, are you considering half days or only whole days?  If you're taking into consideration half of a day then all of the below is not even a necessary consideration and you can just take the last day in the month and divide it by 2 to get your middle point.

If you're not doing half days, then I would ask what are you considering the middle of the month?

31 / 2 = 15.5

30 / 2 = 15

29 / 2 = 14.5

28 / 2 = 14

For the month that has 31 days in it what are you considering the middle?  You have two scenarios.  One with 1-15 (15 days) and 16-31 (16 days), and the second 1-16 (16 days) and 17-31 (15 days.)  Same thing for a month with 29 days in it except it would be 1-14 and 15-29 or 1-15 and 16-29.

All of that is to get down to the case that if you're using the first of the two scenarios above, that means your middle of the month only toggles between the 14th and 15th of the month.  So you could have the SQL query do it or you could have PHP do it.  Personally I'd go the route of having the PHP calc it and then just had a straight query to the database.

If you're the first scenario then the function would look like this.

   function middleOfMonth( $date = NULL ){

      return floor( date( 't', isset( $date ) ? strtotime( $date ) : strtotime("now") ) / 2 );

   }

If you're the second scenario, it's only a little different using ceiling instead of floor.

   function middleOfMonth( $date = NULL ){

      return ceiling( date( 't', isset( $date ) ? strtotime( $date ) : strtotime("now") ) / 2 );

   }

Hopefully I've not gone off in an overly complicated consideration of this, but it's how I would approach it.  Maybe someone else will have an even better answer and teach me something.

Thanks,

Daniel H.

Google Voice:  (901) 214-5326

- Copying one is plagiarism, copying many is research.

--
You received this message because you are subscribed to the Google Groups "Memphis PHP" group.
To post to this group, send email to memph...@googlegroups.com.
To unsubscribe from this group, send email to memphis-php...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/memphis-php?hl=en.

[Joe Ferguson]

Would probably be easier just to calculate the last day of the month and then use that for your query.

IE:

$lastday = date('t',strtotime('today')); // 31

$thismonth = date('m'); // 08

$thisyear = date('Y'); // 2011

$lastdayofthismonth = $thismonth . '-' . $lastday . '-' . $thisyear; // 08-31-2011

--
- Joe Ferguson
http://www.midsouthmakers.org
Google Voice - (901) 451-9256

[Brad H]

Thanks Dan and Joe for responding,

The customer wants the first commission check to be based on the 1-15th -regardless of the days in the month, so I can get the rest with between 16th date('t',strtotime('today')). 

I should have read the date function better on php.net. Another thing I could do, but seems wacky, is to find the total commissions for the month, and then calculate the 1-15th subtracting that from the total monthly amount with the difference being the second half of the month.

thanks again,

Brad