Ju
|
| Posted: 12/03/2003, 4:51 PM |
|
For my application, I have to conduct a complex SQL query for one of the grids. This query involves a subselect i.e. a SELECT on a nested SELECT.
Unfortunatly, only MYSQL 4.1 and above supports subselects. My web host currently only supports MYSQL 4.05.
One way to get around this problem is to build a temporary table and conduct a SELECT query on that temporary table. This involves two SQL queries: First query (create a temp table); second query (do a SELECT). I tried to modify the Object->ds->SQL in the "Before Execute Select" custom code section but CodeCharge does not let you execute more than one query. I can execute any one of them but not both of them together. Below is a snippet of the code:
$data_sample_points->ds->SQL = "CREATE temporary table table1 SELECT sample_points.Barcode, Description, MAX(Sample_Date) FROM sample_points LEFT JOIN `data` ON sample_points.Barcode = data.Barcode WHERE sample_points.Plant_Id = {Plant_Id} GROUP BY Barcode; Select table1.Barcode, Description, `MAX(Sample_Date)`, SMR, Oil_Hours, Evaluation, Comments FROM table1 LEFT JOIN `data` ON table1.Barcode = data.Barcode AND table1.`MAX(Sample_Date)` = data.Sample_Date";
When I tried to execute the code above, I get the following error message:
MySQL Error: 1064 (You have an error in your SQL syntax near '; Select table1.Barcode, Description, `MAX(Sample_Date)`, SMR, Oil_Hours, Evalua' at line 1)
Obviously the ";" is not accepted but is needed to conduct multiple SQL queries. I have tested the SQL queries above manually and they worked fine.
So I'm stuck. I cannot do a subselect and I cannot modify the Object->ds->SQL to do multiple SQL queries in CodeCharge. I tried to avoid doing a subselect for that grid but has been unsuccessful so far.
|
|
|
 |
stephenk
|
| Posted: 12/04/2003, 12:42 AM |
|
Create a before show event for the page or grid with the following code
<code>
$db = new cls[yourConnectionName]();
$plant_id = CCGetParam("[urlparametername]","[default value]");
$db->query("CREATE temporary table table1 SELECT sample_points.Barcode, ".
"Description, MAX(Sample_Date) FROM sample_points LEFT JOIN `data` ".
"ON sample_points.Barcode = data.Barcode WHERE sample_points.Plant_Id = ".
"$plant_id GROUP BY Barcode)";
unset($db);
</code>
This will create the temporary table and populate it;
- CCGetParam("varname","default") will return the url parameter varname look at the help file for more information
- The second part of the query will be your datasource.
To create the datasource with query builder you may need to have the table in the database which you can drop after publishing the page
|
|
|
|
|
