[SOLVED] Query field with error on email value

3 years 8 months ago - 3 years 8 months ago #319390 by eciudad
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])

Please Log in to join the conversation.

3 years 8 months ago #319391 by beat
Replied by beat on topic Query field with error on email value

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:

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])

But that this could be further simplified to something like:

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 cfc.id = [user_id]

Beat - Community Builder Team Member

Before posting on forums: Read FAQ thoroughly -- Help us spend more time coding by helping others in this forum, many thanks :)
CB links: Our membership - CBSubs - Templates - Hosting - Forge - Send me a Private Message (PM) only for private/confidential info

Please Log in to join the conversation.

3 years 8 months ago - 3 years 8 months ago #319392 by beat
Replied by beat on topic Query field with error on email value
And that actually using a CB "Custom HTML"-type field with CB substitutions in it like this:
[lastname] [firstname] [middlename] [cb_thirdname]

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.

Beat - Community Builder Team Member

Before posting on forums: Read FAQ thoroughly -- Help us spend more time coding by helping others in this forum, many thanks :)
CB links: Our membership - CBSubs - Templates - Hosting - Forge - Send me a Private Message (PM) only for private/confidential info

Please Log in to join the conversation.

3 years 8 months ago #319397 by eciudad
Replied by eciudad on topic Query field with error on email value
Thank you I will try

Please Log in to join the conversation.

3 years 8 months ago #319405 by eciudad
Replied by eciudad on topic Query field with error on email value
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

Please Log in to join the conversation.

3 years 8 months ago #319406 by eciudad
Replied by eciudad on topic Query field with error on email value
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

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.221 seconds

Facebook Twitter LinkedIn