| Using SQL Aggregate Functions [message #1286] |
Thu, 13 March 2008 06:58  |
|
|
Hi Sir Tony,
I was trying to use the framework to get MAX (`student_id`) from the crs_student table using getdata_raw setting $this->sql_select. First, it required using the "GROUP BY", but I still I keep getting unexpected results.
in MySQL --> SELECT MAX (`student_id`) AS last_id FROM `crs_student`
in Radicore
$this->sql_select = "MAX (`student_id`) AS last_id"
$rows=$this->getdata_raw("");
$rows[0]['last_id'] <-- expecting to contain largest student_id
-------
Result is not the largest ID.
How can I use aggregate functions in the getdata series or am I doing things the wrong way and making faulty assumptions?
THanks again.
Melvin
|
|
|
|
| Re: Using SQL Aggregate Functions [message #1289 is a reply to message #1286] |
Thu, 13 March 2008 07:41  |
AJM
Messages: 2369
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
If you look inside the _cm_getInitialData() method of file crs_student.class.inc you will see that I use the following code:
$count = $this->getCount("SELECT max(student_id) FROM $this->tablename");
$fieldarray['student_id'] = $count + 1;
I have also tried the following code with exactly the same result:
$this->sql_select = "MAX(student_id) AS last_id";
$rows=$this->getdata_raw();
$count = $rows[0]['last_id'];
I prefer the first method as it requires less code.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Using SQL Aggregate Functions [message #1296 is a reply to message #1289] |
Tue, 25 March 2008 21:19 |
|
|
While I prefer the same option, unfortunately some cases use keys mixing alpha and trailing numeric. I did try to use the second suggestion but got this...
2008-03-26 01:15:28
Fatal Error: MySQL error: 1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (# 1140).
SQL query: SELECT SQL_CALC_FOUND_ROWS MAX(`student_id`) AS student_id, crs_class.class_name FROM crs_student LEFT JOIN crs_class ON (crs_class.class_id=crs_student.class_id)
Error in line 451 of file 'E:\xampp\htdocs\radicore\includes\dml.mysqli.class.inc'.
Host Info: localhost via TCP/IP
Server Version: 5.0.51
Client Info: 5.0.51, Client Encoding: latin1
Script: /radicore/classroom/student_add.php
I decided to include a $this->sql_groupby = "...." to make it work. but this is just a temporary measure
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
