| (BUG+FIX) SQL error with getData and complex SELECT statements [message #40] |
Wed, 24 May 2006 10:19  |
janalwin
Messages: 17
Registered: May 2006
Location: Groningen
|
Junior Member |
|
|
I have a logdatabase and I needed to create a report grouped by date. So I put together the sql down below:
$inner_sql_select = "count(event) AS eventaantal, event, datumtijd, DATE_FORMAT(datumtijd, '%d-%m-%Y') AS datum, DATE_FORMAT(datumtijd, '%Y-%m-%d') AS datum_groupby";
$inner_sql_from = "log " .
'LEFT JOIN naamnummers ON log.naamnummers_id = naamnummers.naamnummers_id' .
$inner_sql_where = NULL;
$inner_sql_orderby="log.datumtijd DESC";
$inner_sql_groupby="datum_groupby, event ";
This didn't work because when the getData function of mysql.dml.class.inc tries to establish the total number of rows it uses count(*) and than the row 'datum_groupby' which is used in 'groupby' doesn't exist.
I solved it by changing the way to get the total number of rows
I changed the code in this way. (Old code is commented out)
// count the rows that satisfy this query
//$this->query = "SELECT count(*) FROM $from_str $where_str $group_str $having_str";]
//$this->numrows = $this->getCount($dbname, $tablename, $this->query);
// CHANGES BY JADJ
$this->query = "SELECT SQL_CALC_FOUND_ROWS $select_str FROM $from_str $where_str $group_str $having_str LIMIT 1";
if (!$result = mysql_query($this->query, $this->dbconnect)) {
trigger_error($this, E_USER_ERROR);
} // if
// Get num rows.
if (!$result = mysql_query("SELECT FOUND_ROWS()", $this->dbconnect)) {
trigger_error($this, E_USER_ERROR);
}
$total = mysql_fetch_row($result);
$this->numrows=$total[0];
// END adjustment JANALWIN
SQL_CALC_FOUND_ROWS is introduced in mysql 4.0. It makes that FOUND_ROWS() returns all records of the previous query without the LIMIT.
It's an efficient alternative for count(*) and can be used in more circumstances than COUNT(*).
It might be a good idea to adjust the getCount method/function instead od doing it in the getData method. But I'm a bit afraid it might break some other code.
PS Adjustment is done in the code of the sample app that was online before RADICORE was introduced. I looked at the source of RADICORE and it seems to be the same.
Jan Alwin de Jong
Gronigen, The Netherlands
|
|
|
|
|
|
| Re: (BUG+FIX) SQL error with getData and complex SELECT statements [message #42 is a reply to message #40] |
Fri, 26 May 2006 04:56  |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
I have inserted your suggested code into my code base and got it working OK. As well as incorporating it into getCount() I have changed getData() so that I can now get the record count and the data with a single query.
There was one rare situation that required a bit of extra coding - it is possible for user A to have a screen with a LAST PAGE option available, but by the time he presses this link user B has deleted one or more records which means that a page with that number no longer exists. If you issue a SELECT with an OFFSET which is out of range then an empty result set is returned, so I have to adjust the OFFSET value and redo the SELECT in order to retrieve the contents of the latest LAST PAGE.
Try the attached file and tell me if it works OK for you as well.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
| Re: (BUG+FIX) SQL error with getData and complex SELECT statements [message #46 is a reply to message #45] |
Thu, 01 June 2006 06:59 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
If you have created transactions using the code which is in my smaller sample application you should be able to run those transactions under the full framework as well. All the file names and function names are the same, it is just the contents which are different.
What you cannot do is take some of the updated files from my full framework and plug them into the sample framework as there are too many changes - the sample application does not have a menu database nor any audit logging, for example.
You can move your transaction scripts between the two frameworks, but none of the framework code is interchangeable (except for the XSL stylesheets which are identical).
When migrating from the sample framework to the full framework the only code change you need to make is to delete the getFieldSpec_original() method from each table class as that information is now obtained from a separate file (<table>.dict.inc) which is constructed by the Data Dictionary.
I suggest you actually build each file first to check the contents against the original values just in case you need to modify any data in the dictionary before you export it.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
