Johann
Posts: 1
|
Posted: 04/19/2004, 7:41 AM |
|
Hello from Austria
My Config:
WinXp Pro - Sp1 with all updates
CCS 2.2.3.60
MySql 3.23.41
PHP 4.0.6
The Database and PHP runs under LInux
My Problem:
If i try to make a Search in with the DatePicker there are no results, the same querry in PhpMyAdmin give me correct results.
I have 2 Date-Filds in the Search form "DateFrom={s_keyword1} "and "DateTo= {s_keword2}" and this 2 Date-filds are formattet by (yyyy-mm-dd) the DB Date-Format is yyyy-mm-dd HH:nn:ss
Sql querry in CSS:
... WHERE TO_DAYS('{s_keyword1}') between TO_DAYS(tabel1.row1) and TO_DAYS(tabel1.row2) and TO_DAYS('{s_keyword2}') between TO_DAYS(tabel1.row1) and TO_DAYS(tabel1.row2)
Sql querry in phpMyAdmin:
SELECT * FROM `tabel1` WHERE TO_DAYS('2004-06-08') between TO_DAYS(tabel1.row1) and TO_DAYS(tabel1.row2) and TO_DAYS('2004-06-15') between TO_DAYS(tabel1.row1) and TO_DAYS(tabel1.row2)
The CSS Method "Prepare" and "Open":
//Prepare Method @9-337E1A88
function Prepare()
{
$this->wp = new clsSQLParameters($this->ErrorBlock);
$this->wp->Criterion[1] = "TO_DAYS('{s_keyword1}') between TO_DAYS(zimmerf_von) and TO_DAYS(zimmerf_bis )";
$this->wp->Criterion[2] = "TO_DAYS('{s_keyword2}') between TO_DAYS(zimmerf_von) and TO_DAYS(zimmerf_bis )";
$this->Where = $this->wp->opAND(false, $this->wp->Criterion[1], $this->wp->Criterion[2]);
}
//End Prepare Method
//Open Method @9-EB4B35D3
function Open()
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildSelect");
$this->CountSQL = "SELECT COUNT(*) " .
"FROM (erw_betrieb Betrieb INNER JOIN erw_betrieb_zimmerfrei Zimmer ON " .
"Betrieb.id = Zimmer.Id_betrieb) INNER JOIN allg_parameter Param ON " .
"Param.p_nr = Betrieb.betriebs_typ";
$this->SQL = "SELECT name AS Name, preis_von AS PreisAb, preis_bis AS PreisBis, zimmerf_von AS Von, zimmerf_bis AS Bis, p_bez AS BetriebsTyp " .
"FROM (erw_betrieb Betrieb INNER JOIN erw_betrieb_zimmerfrei Zimmer ON " .
"Betrieb.id = Zimmer.Id_betrieb) INNER JOIN allg_parameter Param ON " .
"Param.p_nr = Betrieb.betriebs_typ";
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteSelect");
$this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL, $this->Where, ""), $this);
$this->query(CCBuildSQL($this->SQL, $this->Where, $this->Order));
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteSelect");
$this->MoveToPage($this->AbsolutePage);
}
//End Open Method
I hope someone can help me!
thx
Johann Groder
p.S. sorry about my english!
|
|
|
Anothersledhead
Posts: 44
|
Posted: 04/20/2004, 1:32 PM |
|
Try to simplify things:
Use the following SQL query
SELECT * FROM table1
WHERE to_days <= {s_keyword1}
AND to_days >= {s_keyword2}
I use the same query in what I am doing and it works fine. If you are writing this query manually rather than using one of the builder make sure you define the search variables in query. Hope this helps.
|
|
|
|