[SOLVED] Query field with error on email value

The following SQL script works fine in phpmyadmin, when used in query field and replaced with substitution it does return an error on the email substitution [cfe.cb_cfone_email], like error near '@hotmail.com' . This is the value of a text field entered on screen. Its is obvious that I am missing something and I do not know what.



SELECT cfc.id, CONCAT_WS( ' ', cfc.lastname, cfc.firstname, cfc.middlename, cfc.cb_thirdname) as fullnamedisplay
FROM #__comprofiler as cfc
INNER JOIN #__users as cfu ON cfu.id = cfc.id
WHERE cfu.email in (SELECT [cfe.cb_cfone_email] from #__comprofiler as cfe WHERE cfe.id = [user_id])

eciudad wrote: The following SQL script works fine in phpmyadmin, when used in query field and replaced with substitution it does return an error on the email substitution [cfe.cb_cfone_email], like error near '@hotmail.com' . This is the value of a text field entered on screen. Its is obvious that I am missing something and I do not know what.



SELECT cfc.id, CONCAT_WS( ' ', cfc.lastname, cfc.firstname, cfc.middlename, cfc.cb_thirdname) as fullnamedisplay
FROM #__comprofiler as cfc
INNER JOIN #__users as cfu ON cfu.id = cfc.id
WHERE cfu.email in (SELECT [cfe.cb_cfone_email] from #__comprofiler as cfe WHERE cfe.id = [user_id])

My first guess is that SELECT [cfe.cb_cfone_email] should be SELECT cfe.cb_cfone_email to avoid a substitution on the selected column:


But that this could be further simplified to something like:

And that actually using a CB "Custom HTML"-type field with CB substitutions in it like this:

Could probably completely avoid the query and query field, if the [user_id] corresponds to the viewed user.

Of course, turn HTML editor off if you want just text without any html around it.

Thank you I will try

Thank you. I tried. I used the email just enter on the screen and that it not saved yet to look for the user on that exist in #__users and since the user is already log in and I do not need it and it is irrelevant for the query. This query is a replace it with value instead of substitutions it work fine in phpmyadmin. Here is what are getting:

Scenerio 1:

Query:
SELECT cfc.id, CONCAT_WS( ' ', cfc.lastname, cfc.firstname, cfc.middlename, cfc.cb_thirdname) as fullnamedisplay
FROM #__comprofiler as cfc
INNER JOIN #__users as cfu ON cfu.id = cfc.id
WHERE cfu.email = [cb_cfone_email]

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 '@hotmail.com' at line 1

Scenerio 2:

Query:
SELECT cfc.id, CONCAT_WS( ' ', cfc.lastname, cfc.firstname, cfc.middlename, cfc.cb_thirdname) as fullnamedisplay
FROM #__comprofiler as cfc
INNER JOIN #__users as cfu ON cfu.id = cfc.id
WHERE cfu.email = [cb_cfone_email]

error:

Unknown column 'juancityone@hotmail.com' in 'where clause'

Scenerio 3:
Query:
SELECT cfc.id, CONCAT_WS( ' ', cfc.lastname, cfc.firstname, cfc.middlename, cfc.cb_thirdname) as fullnamedisplay
FROM #__comprofiler as cfc
INNER JOIN #__users as cfu ON cfu.id = cfc.id
WHERE cfu.email = [cb_cfone_email]

error:

nothing


Note: in all instances if I replace [cb_cfone_email] for the value juancityone@hotmail.com, I get the expected result in phpmyadmin, the record exits in #__users and in #__comprofiler. I can see that the [cb_cfone_email] substitution for the field just entered on the screen passes the value or the partial value in one to two of the above scenarios. Does it has to do because the field has "@" or "+"?

Thank you in advance

Sorry error on typing:

Scenario 2:

WHERE cfu.email = `[cb_cfone_email]`

error:

Unknown column 'juancityone@hotmail.com' in 'where clause'


Scenario 3:

WHERE cfu.email = '[cb_cfone_email]'

error:

nothing