[FX.php List] Date range and MySQL

[Dale Bengston]

Thanks, Webko! I'm going to see if this works... tomorrow. It's not a  
can of worms for this time of night in my part of the globe.

Dale

PS Hashing two finds could get ugly. The current system has 3 million  
records and counting.

On Jan 23, 2008, at 10:25 PM, Tim 'Webko' Booth wrote:

>>>>
>>>> In FMP, it would look like this:
>>>>
>>>> $findRec->AddDBParam('myDate', $startDate . '...' . $endDate);
>>>>
>>>> How would this translate to MySQL?
>>>>
>>>> Well, the actual SQL would look like:
>>>> select *
>>>> from table
>>>> where date between [start date] and [end date]
>
> Poking around in the FX class, the SQL stuff is handled like:
>
> $currentQuery = "SELECT {$this->selectColumns} FROM {$this->layout} 
> {$whereClause}
>
> And by default, $whereClause is:
>
> $whereClause = ' WHERE ' . $name . " LIKE '" . $value . "%';
>
> We don't want that LIKe in there, so we'll specify eq, to make  
> $whereClause
>
> $whereClause = ' WHERE ' . $name . " = '" . $value . "%';
>
>
> So, I'm guessing something like:
>
> $strRange = "between ".$startDate." and ".$endDate";
>
> findRec->AddDBParam('myDate', $strRange, 'eq');
>
> Although that equals sign being put in there bugs me, and may cause  
> problems...
>
> Or perhaps two finds, one for gte $startDate and the other for lte  
> $endDate?
>
> Cheers
>
> Webko
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list