[SOLVED] Query field with error on email value

3 years 8 months ago #319407 by beat
Replied by beat on topic Query field with error on email value
Scenario 3 is the right one regarding query syntax. You need text-quotes around the emai, and in MySQL, text quotes are forward single-quote ' or regular double-quote ". single back-quote ` is used for quoting column and table names, not for text.
stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql

Now, why the result gives nothing may have to do with your query's logic.

To debug your query and see all page SQL queries, in Joomla Global Configuration, System Tab, activate Site Debug, then go to your front-page and you will see Database Queries at bottom, when clicking on that accordion title. And for faulty queries you should see the full query that is faulty.

We really wish to help you, so If you have any further questions, please:
- paste the complete query you typed-in the admin area,
- and the corresponding real query you get in the front-end,
- and tell what you exactly want to achieve, including context.

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 #319410 by eciudad
Replied by eciudad on topic Query field with error on email value
As requested, here are the actual phpmyadmin queries with the actual results. Also, the cb queries with a substitution (value entered in the field at the screen) with the same value used in phpmyadmin queries, which should return the same result as the phymyadmin. It is a very simple sql query. I am using the email to look for the user from the #__users and display on the drop down sql "Father or Parent One"

Thank you

Please Log in to join the conversation.

3 years 8 months ago #319411 by eciudad
Replied by eciudad on topic Query field with error on email value
Note: The result should look like this on CB
Attachments:

Please Log in to join the conversation.

3 years 8 months ago - 3 years 8 months ago #319418 by beat
Replied by beat on topic Query field with error on email value
I just re-tested on a localhost your use-case, and it works as expected.

- Did you also fill-in the 2 first fields parameters "Value Column" and "Label Column" in the next tab "Options" ?
(in your case, first one should be "id" and second one "fullnamedisplay" (both without the double-quotes)

Maybe looking at our demo-site CB Query example and its settings can help too:
Front page: demo.cbdemosites.com/demos/cb-query-field
Admin page (login details below login): demo.cbdemosites.com/administrator/index.php?option=com_comprofiler&view=showField

Here my settings/test-screendumps:

1. Field type: Query (single drop-down, as you seem to wish a drop-down)

2. Query (took yours "scenario 3", just without the 4th name to avoid adding another field):
SELECT cfc.id, CONCAT_WS( ' ', cfu.name, cfc.firstname, cfc.middlename) as fullnamedisplay
FROM #__comprofiler as cfc
INNER JOIN #__users as cfu ON cfu.id = cfc.id
WHERE cfu.email = "[cb_cfone_email]"

3. Options:
Value Column: id
Label Column: fullnamedisplay

4. Screenshots of the CB Query single-dropdown field settings, the result on profile and on profile edit, and the cb_cfone_email CB field setting.

5. EDIT: added screeshot of the query executed on profile (visible at bottom when enabling site debug in joomla configuration, system tab).

EDIT 2: ADDING NOTE: (the email used in this test doesn't exist in real life, just used it to grab another test-user's name from that email))

EDIT 3: there is also our CB Query video-tutorials here: www.youtube.com/playlist?list=PLp0puRITgC7MM9iP9FooKB1qk8f5MLWVp

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 #319438 by eciudad
Replied by eciudad on topic Query field with error on email value
Thank you,

"- Did you also fill-in the 2 first fields parameters "Value Column" and "Label Column" in the next tab "Options" ?
(in your case, first one should be "id" and second one "fullnamedisplay" (both without the double-quotes)"

Yes I did.

"5. EDIT: added screenshot of the query executed on profile (visible at bottom when enabling site debug in joomla configuration, system tab)."

Selected.

I just updated to the latest update from yesterday.

Reviewed tutorial and articles and there no difference on what I am doing.

Will continue testing and looking at the settings along with reviewing your recommendations.

Thank you

Please Log in to join the conversation.

3 years 8 months ago #319503 by eciudad
Replied by eciudad on topic Query field with error on email value
Thank you. It is working now. Did the same again after update from las week and it works. No sure what was the issue.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.245 seconds

Facebook Twitter LinkedIn