2

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');
1

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

  • You're right - the function is probably most appropriate since there is no state, and I can't foresee other methods being put into such a class. – AmadeusDrZaius Mar 5 '15 at 18:49
2

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

  • Are you sure parameterized queries are powerful enough to represent a range with just one parameter? I agree that the end result should use parameterized queries, but that doesn't change the underlying question posed here. – Sebastian Redl Mar 5 '15 at 15:20
  • 1) I do use parameterized queries, in general. 2) Dates, once broken up into y/m/d, are not vulnerable to SQL injection. I don't understand how this answer relates to my question at all. – AmadeusDrZaius Mar 5 '15 at 18:44
  • If you use parameterised queries in general, why develop an exception specifically for date ranges? Is there some problem passing range_start and range_end as separate parameters? – Julia Hayward Mar 6 '15 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.