query field executes only in profile edit mode

This Query field's query doesn't work for either Profile Edit or View
select 'registerDate' from '#_users' where 'id' = '[user_id]'
not does
select 'registerDate' from 'ove83_users' where 'id' = '[user_id]'

It gets a 1064 error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ove83_users' where 'id' = '461'' at line 1 SQL=select 'registerDate' from 'ove83_users' where 'id' = '461'

SELECT registerdate FROM `ove83_users` WHERE id = '461'
works fine in phpMyAdmin

Where am i going wrong please?

try '#__users' instead of '#_users'

I should have mentioned that I had tried both '#__users' and '#_users' and for the former got 1064 error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''#__users' where 'id' = '461'' at line 1 SQL=select 'registerDate' from '#__users' where 'id' = '461'"

is there some global switch to allow the #__tablename syntax?

Also still need to get the original problem solved, i.e., getting registerDate from the Joomla users table

thanks

Your query will never work due to incorrect quote usage. Please see the below working query.


Review carefully that tables/columns are escaped with ` and raw values escaped with '.

I however am unsure the reason for such a query though as CB already has a core field that displays registration date.

Thank you very much for your, as always, very prompt and helpful reply. We had missed the ` vs. ' distinction (due to poor eyes :silly: )

I assume you were referring to cbactivation as the core field that displays registration date? I have converted users from another system andI need to keep their original date joined from that system, so we are setting the Joomla registerDate field when we load the users. I assummed that cbactivation would be set to the first time the user logged into CB and would overlay the date we set. thanks again.

cbactivation is the users confirmation code. That's generated by CB during registration. registerDate is stored to the Joomla user when the user registers and is displayed via the registerDate field within CB > Field Management. If you want to update it from another system it's better to just do a mass database query to update the users dates to be the same as your previous system and then you're fine from then on. You could also use CB Juice and a well formatted CSV file to import your users into CB.