| (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
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
