THX1138
Posts: 37
|
Posted: 09/29/2012, 5:38 PM |
|
CCS 4.3.0.7676 , MySQL, PhP
Hello all,
Having a relapse of the stupids out here...i have a simple mysql query
to select and rank values prior to display in a grid.
the mysql works on the database, but i get grumpy syntax error messages
from codecharge generated pages when i try to use the updated sql
Here is a simplified version of the query, already codecharge spits at this one
set @rownum:=0;
select @rownum := @rownum+1 as rank, location, datum from snf_data
CCS pages complain of a syntax error at line 1 near ; select,
it looks like i cant get the syntax accepted/acceptable to CCS to allow me
to set the variable in mysql
Been trying changing ' " ` s and commas and semicolons, but to no avail
The ccs generated code shows it is adding the preselect conditions but then blammo.
//Open Method @32-D1298D25
function Open()
{
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildSelect", $this->Parent);
$this->SQL = "set @rownum:=0; SELECT meta, location, yrstart, datum \n" .
"FROM snf_data ";
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteSelect", $this->Parent);
if ($this->CountSQL)
$this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL, $this->Where, ""), $this);
else
$this->RecordsCount = "CCS not counted";
$this->query($this->OptimizeSQL(CCBuildSQL($this->SQL, $this->Where, $this->Order)));
$this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteSelect", $this->Parent);
}
//End Open Method
And no, views arent possible b/c of the @variables and subqueries, and stored procedures seem to be forbidden on host server
Here is the full sql query that works with mysql, for those who like more ..
set @rownum=0;
set @GDPyear=(select max(yrstart) from snf_data where meta = 'GDP');
select rank, yrstart, location, datum, bilyons, meta
from (
SELECT @rownum := @rownum +1 as rank, yrstart, location, datum, format((datum/1000),1) as bilyons, meta
FROM snf_data
where meta = 'GDPabbr' and yrstart = @GDPyear
ORDER BY datum DESC
) as rankedGDP
|
|
|
THX1138
Posts: 37
|
Posted: 09/29/2012, 6:46 PM |
|
SOLVED IT
results table query now starts with select and is happy
moved the mysql variable definition to the before select event for the results table,
custom code (well, i retasked a Dlookup as i can never get my db connection right)
global $DBcbdbconn;
$Page = CCGetParentPage($sender);
$db = new clsDBcbdbconn();
$db->query("SET @rownum = 0");
$db->close();
Put the subselect of year of interest into the table lookup select statement, so custom sql
for select is
SELECT @rownum:=@rownum+1 as rank,location, yrstart, datum, format((datum/1000),1) as bilyons
FROM snf_data
WHERE meta= 'GDPabbr' and yrstart = (SELECT max(yrstart) from snf_data where meta='GDP')
|
|
|
|