GROUPS ISSUE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

GROUPS ISSUE

Post by mad_phpq »

Code: Select all

SELECT
  md5(concat(st.userid,st.scormid,st.scoid)) as rid,
  u.firstname,
  u.lastname,
  sc.name,
  sco.title,
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(st.element,'::::',`value` order by st.element SEPARATOR '^^^^'),'^^^^',1),'::::',-1) as `status`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(st.element,'::::',`value` order by st.element SEPARATOR '^^^^'),'^^^^',2),'::::',-1),'t0',1) as `time`,
  FROM_UNIXTIME(MIN(st.timemodified),'%d/%m/%Y') As datefirstaccess,
  FROM_UNIXTIME(MAX(st.timemodified),'%d/%m/%Y') As datelastaccess
FROM mdl_scorm_scoes_track st inner join mdl_user u
  on st.userid = u.id
  inner join mdl_scorm sc
  on st.scormid = sc.id
  inner join mdl_scorm_scoes sco
  on st.scoid = sco.id
where datelastaccess > UNIX_TIMESTAMP('25-09-1978')
  and datelastaccess < UNIX_TIMESTAMP('25-09-1978')
group by rid
Returns Unknown Column 'datelastaccess' in 'where clause'

Anyone know where i'm going wrong? Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: GROUPS ISSUE

Post by John Cartwright »

Unknown Column 'datelastaccess' in 'where clause'
My guess is the column 'datelastaccess' does not exist in your table 8O
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Re: GROUPS ISSUE

Post by mad_phpq »

i've renamed it in the SQL above.

Code: Select all

FROM_UNIXTIME(MAX(st.timemodified),'%d/%m/%Y') As datelastaccess
Post Reply