saseow
Posts: 744
|
| Posted: 10/20/2012, 10:09 PM |
|
I have a vertical menu in an includable page that gets it's data from a table.
I am trying to dynamically change the SQL in the BeforeBuildSelect event so that it gets the data from a different table with no luck.
Anyone have any brilliant ideas why this is not possible and possibly a workaround?
|
 |
 |
bannedone
Posts: 273
|
| Posted: 10/21/2012, 11:47 AM |
|
Hi
You can change the database at run time. However it must be structured the same as the original database is.
That means the field names must be the same and the field types must be the same. This is because it is not easy to change that on the fly unless you change the entire SQL statement to match new field names etc..
Also it must be done in the BeforeExecuteSelect event as the SQL is not fully formed in the event you are using. (*Note: for doing this when updating your table, you must do this in the BeforeExecuteInsert and BeforeExecuteUpdate.)
What you do is explained below.
In the BeforeExecuteSelect Event Custom Code you will add the following code
$Component->ds->SQL=str_replace("original-table-name-","new-table-name",$Component->ds->SQL);
Hope that helps
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
DataDoIT
|
| Posted: 10/21/2012, 6:34 PM |
|
Is not something like $Container->ds->SQL = "SELECT * FROM tbl_menus"
not working for you?
|
|
|
|
saseow
Posts: 744
|
| Posted: 10/21/2012, 7:16 PM |
|
Thank you both for the input.
It seems as though putting the code into the BeforeBuildSelect does not work BUT, if I put it in the BeforeExecuteSelect it works fine. Must be something to do with include pages and menu. I am just not sure.
I used standard code:
$Component->ds->SQL = "SELECT * FROM menu_u_copy";
Thanks Again!!!
|
|
|
|
bannedone
Posts: 273
|
| Posted: 10/21/2012, 7:39 PM |
|
FYI
The reason you cannot modify the SQL in the BeforeBuildSelect is that the SQL has not been generated by CCS yet. You can modify Where and Group etc. methods in BeforeBuildSelect but not the SQL statement there. (*note: You can modify the Where, Group, Order, etc. methods in BeforeExecuteSelect as well if you want to keep all your changes in one place.)
The reason really is you change the SQL method in BeforeBuildSelect. CCS changes it when it goes off and actually builds the Select part of the SQL. Thus you can only change the SQL portion of the command in BeforeExecuteSelect.
Hope that makes sense and helps explain it.
BTW I use the str_replace() method just to be sure it works in all cases and does not break anything, including inserts and updates. (in your case maybe you allow admins to maintain menu tables) But that is just me.
I used this method to build a multi-language CMS system. I would swap out DB tables holding CMS content in this fashion based on the language selected for content display and content maintenance.
Have Fun
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
saseow
Posts: 744
|
| Posted: 10/21/2012, 7:49 PM |
|
Well, you learn something new every day.
Thank you so much for the detailed reply bannedone, I guess this is the first time I actually have had to change tables and, since I always do WHERE and ORDER stuff in the BeforeBuildSelect, I just took it for granted that that was the right place to put it. Now I know better!
Thanks again!!
|
|
|
|
|
