If I have a DateRange class, and I want to translate a DateRange into SQL (e.g. some_col >= '2015-3-5' AND some_col <= '2015-3-5'), where should I put the method that does the translating? Nothing I’ve come up so far with seems very good.
Option #1: Put a method in the DateRange class. (Downside: close coupling of the DateRange class and SQL?)
class DateRange {
public function toSQL ($colName) {...}
}
Opton #2: Make a class that only has one method in it?
class DateRangeTranslator {...}
Option #3: a standalone function
function dateRangeToSQLWhereCondition (DateRange $dr, $colName) {...}
I’m leaning towards option #2. But is there a better way?
EDIT:
It’s a bit tedious but here’s what I’ve got now:
class DateRangeSQLBuilder {
public $dateRange;
public function buildWhereCondition ($colName) {...}
}
And you would use it like so:
$dateRange = new DateRange($fromDate, $toDate);
$builder = new DateRangeSQLBuilder($dateRange);
$whereConditions[] = $builder->buildWhereCondition('runDate');
I’d go with the function. Like you said, the first option introduces too much coupling. Option two looks okay but since your class will probably don’t have to maintain state I’d go with option three.
If in doubt, go with the easy/dumb solution ;).
1
tl;dr answer: DON’T.
PHP supports parameterised queries; make the most of their ability both to handle all the nasty implementation details of converting dates between PHP and SQL forms, because it will be quicker to do so and far more solidly tested than rolling your own.
In addition, you automatically get protection from SQL injection attacks that you didn’t have before.
https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php
3