whiterabbitwond
Posts: 28
|
| Posted: 01/23/2008, 7:45 AM |
|
Hi, this is the transcript from my support with Codecharge. I read the forums and just could not see what I was doing wrong. Granted, I am new the actually manipulating the php code as CCS has made me lazy by doing it all for me. 
Well this just shows that learning a few things can save a lot of time.
My app page was a grid where I want the end user to choose the columns they want to see and in any order. Well I know agax can do this, but agax has a hard time saving, and I could not make it work. So I spent a month coding this huge page with panels on every column, and a form to select the panels to be visible... ugly. And it did not allow the columns to be reordered.
I had heard about the dynamically modifying sql event, but could not get it to work. The theory was the let the end user tweak certain parts of the sql (safely through a form) to achieve my result. It would be much cleaner and faster for big tables.
Anyway, the help below was the linchpin I needed to make it all work.
Summary alter grid sql to change output
Description Hi, I have tried for three days to make this work, searched the forums and am at a loss. Any help would be appreciated.
I followed this example: http://docs.codecharge.com/studio32/html/ProgrammingTec.../ModifySQL.html
(although the forums mentioned I should instead be using the BeforeBuildSelect to change the SQL query.
I added an echo before and after the function, both return the query originally attached to the grid, not the new one in the function. As a result the output data is not changing.
gridname: lu_grid
table: lu_session_types
original sql attached to grid: SELECT lu_session_types.id, lu_session_types.value FROM lu_session_types
new sql in function: SELECT lu_session_types.id FROM lu_session_types
I clicked on the lu_grid in design view and clicked AddCustomCode to the BeforeBuildSelect event. (also tryed BeforeExecuteSelect to no avail).
I pasted the following code in:
function lu_grid_DataSource_BeforeExecuteSelect(& $sender) {
global $lu_grid;
$lu_grid->DataSource->SQL = "SELECT 'test' FROM lu_session_types";
}
echo $lu_grid->DataSource->SQL;
and the echo says:
SELECT lu_session_types.id, lu_session_types.value FROM lu_session_types
meaning nothing changed.
Thank you for any insight you may have.
-David
User Helen D.
Status Hold - Proposed Solution
Response David,
The code is correct and in general it should work. Please open the page in CCS Code mode and look for modified code blocks. They have white background and they’re not overwritten while page/project generation that brings to unexpected errors. In this case please open the page in CCS Code mode, select the modified block and remove it. Then regenerate the page.
Regards,
Helen
Response Hi Helen, I am not understanding what you mean. I will attached a zip file of the project and the sql code.
The only white space code in project code is on page: NewPage1_events.php
There is no whitespace on the code in NewPage1.php
And all the white space code is the code intentionally added to modify the sql query the grid has. So not sure why I would delete it.
And the white code in NewPage1_events.php is in BeforeBuildSelect:
//Custom Code @5-2A29BDB7
// -------------------------
echo $lu_grid->DataSource->SQL;
function lu_grid_DataSource_BeforeBuildSelect(& $sender) {
global $lu_grid;
$lu_grid->DataSource->SQL = "SELECT 'v' FROM lu_session_types";
}
echo $lu_grid->DataSource->SQL;
// -------------------------
//End Custom Code
-------------------------------------------------------------
And then in the BeforeExecuteSelect there is this white code:
//Custom Code @6-2A29BDB7
// -------------------------
global $lu_grid;
echo "SQL:" . $lu_grid->DataSource->SQL . "<br>";
echo "ORDER BY:" . $lu_grid->DataSource->Order . "<br>";
echo "WHERE:" . $lu_grid->DataSource->Where . "<br>";
echo $lu_grid->DataSource->SQL;
// -------------------------
//End Custom Code
Posted 1/23/2008 6:59:04 AM
User Kate L.
Status Hold - Proposed Solution
Response Hello David,
Thank you we got your project and looked into it.
The issue was caused by a little incorrect code used in Before Execute Select section, specifically there is no need to dublicate the line:
function lu_grid_DataSource_BeforeExecuteSelect(& $sender)
as it is already specified in the code. You should only add the following code lines:
global $lu_grid;
$lu_grid->DataSource->SQL = "SELECT 'v' FROM lu_session_types";
so that the whole Before Execute Select event code looks like:
function lu_grid_ds_BeforeExecuteSelect(& $sender)
{
$lu_grid_ds_BeforeExecuteSelect = true;
$Component = & $sender;
$Container = & CCGetParentContainer($sender);
global $lu_grid; //Compatibility
//End lu_grid_ds_BeforeExecuteSelect
//Custom Code @5-2A29BDB7
// -------------------------
global $lu_grid;
$lu_grid->DataSource->SQL = "SELECT 'v' FROM lu_session_types";
//echo "Print SQL: ". $lu_grid->DataSource->SQL;
// -------------------------
//End Custom Code
//Close lu_grid_ds_BeforeExecuteSelect @2-851B5E15
return $lu_grid_ds_BeforeExecuteSelect;
}
//End Close lu_grid_ds_BeforeExecuteSelect
Best regards,
Kate.
Posted 1/23/2008 7:20:09 AM
Status Closed - Resolved
Response WOW, you rock! Thank you. If I had known how to do this last year, I could have coded the problem page in a week instead of a month. I was doing a hack to make columns visible selectively, when i could have just tweaked the sql feeding the grid on the fly. Phew. Thanks. 
|
