When I want to display a profile field of the type "url", I get an error:

user warning: 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 '-url_value, profile_values_profile_companyurl.value AS profile_values_profile' at line 8 query: SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, profile_values_profile_company.value AS profile_values_profile_company_value, profile_values_profile_personfunction.value AS profile_values_profile_personfunction_value, profile_values_profile_firstname.value AS profile_values_profile_firstname_value, profile_values_profile_lastname.value AS profile_values_profile_lastname_value, profile_values_profile_personnal-url.value AS profile_values_profile_personnal-url_value, profile_values_profile_companyurl.value AS profile_values_profile_companyurl_value FROM users users INNER JOIN users_roles users_roles ON users.uid = users_roles.uid LEFT JOIN profile_values profile_values_profile_company ON users.uid = profile_values_profile_company.uid AND profile_values_profile_company.fid = '3' LEFT JOIN profile_values profile_values_profile_personfunction ON users.uid = profile_values_profile_personfunction.uid AND profile_values_profile_personfunction.fid = '5' LEFT JOIN profile_values profile_values_profile_firstname ON users.uid = profile_values_profile_firstname.uid AND profile_values_profile_firstname.fid = '14' LEFT JOIN profile_values profile_values_profile_lastname ON users.uid = profile_values_profile_lastname.uid AND profile_values_profile_lastname.fid = '13' LEFT JOIN profile_values profile_values_profile_personnal-url ON users.uid = profile_values_profile_personnal-url.uid AND profile_values_profile_personnal-url.fid = '1' LEFT JOIN profile_values profile_values_profile_companyurl ON users.uid = profile_values_profile_companyurl.uid AND profile_values_profile_companyurl.fid = '7' WHERE (users_roles.rid = 17) AND (users.uid not in ('1')) LIMIT 0, 10 in /home/controlc/www/sites/all/modules/views/includes/view.inc on line 755.

Thanks for help

Comments

merlinofchaos’s picture

merlinofchaos commented

That's weird. You can't have dashes in profile field names. How did you get a dash in there?

jvieille’s picture

jvieille commented

I suppose it comes from the core Profile Module that lets define attributes to basic user profiles at admin/user/profile
I just defined the field I needed without dashes, Drupal created them.

jvieille’s picture

jvieille commented

New test: I have 2 urls in profiles (Company URL and Personnal URL)
I can get Views displaying normally the first one, but it fails as sson as I select the second one.
Bellow the error message with these 2 fields

user warning: 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 '-url_value FROM users users INNER JOIN users_roles users_roles ON users.uid =' at line 9 query: SELECT users.uid AS uid, users.picture AS users_picture, users.name AS users_name, profile_values_profile_firstname.value AS profile_values_profile_firstname_value, profile_values_profile_lastname.value AS profile_values_profile_lastname_value, profile_values_profile_personfunction.value AS profile_values_profile_personfunction_value, profile_values_profile_company.value AS profile_values_profile_company_value, profile_values_profile_companyurl.value AS profile_values_profile_companyurl_value, profile_values_profile_personnal-url.value AS profile_values_profile_personnal-url_value FROM users users INNER JOIN users_roles users_roles ON users.uid = users_roles.uid LEFT JOIN profile_values profile_values_profile_firstname ON users.uid = profile_values_profile_firstname.uid AND profile_values_profile_firstname.fid = '14' LEFT JOIN profile_values profile_values_profile_lastname ON users.uid = profile_values_profile_lastname.uid AND profile_values_profile_lastname.fid = '13' LEFT JOIN profile_values profile_values_profile_personfunction ON users.uid = profile_values_profile_personfunction.uid AND profile_values_profile_personfunction.fid = '5' LEFT JOIN profile_values profile_values_profile_company ON users.uid = profile_values_profile_company.uid AND profile_values_profile_company.fid = '3' LEFT JOIN profile_values profile_values_profile_companyurl ON users.uid = profile_values_profile_companyurl.uid AND profile_values_profile_companyurl.fid = '7' LEFT JOIN profile_values profile_values_profile_personnal-url ON users.uid = profile_values_profile_personnal-url.uid AND profile_values_profile_personnal-url.fid = '1' WHERE (users_roles.rid = 17) AND (users.uid not in ('1')) LIMIT 0, 10 in /home/controlc/www/sites/all/modules/views/includes/view.inc on line 755.

jvieille’s picture

jvieille commented
Title: SQL error when using a Profile field of "url" type » Profile field should not allow "-" in field form names
Project: Views (for Drupal 7) » Drupal core
Version: 6.x-2.6 » 6.14
Component: profile data » profile.module

I confirm that removing the dash in the profile form label that generates the field name solves the issue.

I think that the "Form name" in the profile fields should prevent the use of a dash. Also, the explaination implicitely allows it and should be modified from
"Spaces or any other special characters except dash (-) and underscore (_) are not allowed."
to
"Spaces or any other special characters except underscore (_) are not allowed.

Status: Active » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.