Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Data Dictionary » (BUG+FIX) SQL error with getData and complex SELECT statements
(BUG+FIX) SQL error with getData and complex SELECT statements [message #40] Wed, 24 May 2006 10:19 Go to next message
janalwin is currently offline  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 #41 is a reply to message #40] Wed, 24 May 2006 11:34 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
That looks VERY interesting. I shall examine it more closely to see if it can be implemented without breaking anything.

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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
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.



Re: (BUG+FIX) SQL error with getData and complex SELECT statements [message #45 is a reply to message #42] Thu, 01 June 2006 06:07 Go to previous messageGo to next message
janalwin is currently offline  janalwin
Messages: 17
Registered: May 2006
Location: Groningen
Junior Member
I tested it in my test-setup and it works fine as far as I can tell.

I'll implement it in the real-system next week.

I couldn't use it directly with the old sample-application code. (No auditing support, etc.) So I copied the new getData and getCount method in the old file and removed the logging calls.


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 Go to previous message
AJM is currently offline  AJM
Messages: 2350
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.


Next Topic: Icon Support
Goto Forum:
  


Current Time: Fri Jun 14 16:07:05 EDT 2024

Total time taken to generate the page: 0.09818 seconds