1064 You have an error in your SQL syntax;

Hai i have a probem when i visit my site its giving the follwing error

1064
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 ') GROUP BY comments.itemID' at line 11 SQL=SELECT content.id AS id, COUNT(comments.itemID) AS count FROM wo357_k2_items AS content LEFT JOIN wo357_k2_comments AS comments ON comments.itemID = content.id WHERE comments.published AND ( ) GROUP BY comments.itemID ;

This error is ocurring after deleting k2 items came with (installed data) during installation

Does anyone know a solution to this problem? I'm getting a similar error after uninstalling and reinstalling K2. This is the error msg:

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 ') GROUP BY comments.itemID' at line 11 SQL=SELECT content.id AS id, COUNT(comments.itemID) AS count FROM icc_k2_items AS content LEFT JOIN icc_k2_comments AS comments ON comments.itemID = content.id WHERE comments.published AND ( ) GROUP BY comments.itemID ;

Any help on solving this is greatly appreciated.

Thank you.

Check News Show PRO modules data source.
Probably one or more of these modules have data source set as K2 and thus after deleting K2 items, sql query from module is returning null and joomla 3.x showing this error.

Yes, one of the news pro module was referring to a K2 Tag. I have disabled it and it works fine. The SQL error that I got was very confusing..

Thanks,

In joomla 3.x I am afraid all sql errors gives the same error message so it is not very clear. Let us know if you need further help. See you around...

Hi Norman,
I have one another open question posted here:

https://www.gavick.com/forums/game-maga ... 26929.html

it is stopping us from continuing with the site upgrade.. any help for resolving it would be greatly appreciated..

Thanks,

Hi,

I have just tested K2 user registration and I can confirm it is saving in k2_users table.

Just to double check is following setting enabled in Components > K2 > Parameters > Advanced > Enable K2 User Profile.

Alternatively you can enter a profile info anything such as Iamtesting and then search this text in phpmyadmin so you can see which table it is stored in.

See you around...

Hi Norman,
I have tried your tips. Still no resolution. The profile information from the registration page is not getting saved. I also tried to update the profile information from the User manager section of the Site administration and it also does not save the information.

Am I missing something ?

Thanks for your help..

If you can send me following information I can check this out for you. normanuk[at]live.co.uk

CPanel access details or access to PhpMyAdmin.
Ftp details
Admin details.

Also let me know if you have done any modifications to K2 files so in case I need to reinstall the component to test it.
Please include this topic subject and link in your email so I know it is from you.

See you around...

Thanks Norman. I have sent you the required information.

Thanks a lot,

Ok I have tried to register a new account and following is what happened.

1. I can see my new account in K2 users.
2. Same again in Database Tables with PhpMyAdmin.
3. There is no data added for your new fields. See below.

If this is the error you was referring to then please tell me the steps you have added these extra fields to registration page.

What I think is happening, you have added new fields to view files only and they are not getting processed as you also need to add them to few other places.

So If you can tell me how you have added these extra fields then I can do further checks.

See you around...

Hi Norman,
Under Components --> K2 -->Advanced -->Enable User Profile was set to Yes to enable these additional fields.

Kindly let me know if this is not the right thing..

Thanks,

Firstly to answer your main question following is how joomla treats user data.

1. Joomla sees user data as one single task. So all fields are displayed in registration page.
2. Each component or plug-in extends this task and stores information separately in their own tables.
3. If standard joomla fields are used then they are stored in "_users" table.
4. If K2 fields are used then they are stored in "k2_users" table
5. If User Profile Plug-in fields are used then they are stored in "user_profiles" table.

Currently these are caused by bugs which are reported for joomla 3.1.1. Fixes are from joomla bug tracker and as a side note only 1 user has reported that some of his/her users received an emails where it is supposed to be for administrators only. So keep an eye for that please. Perhaps create a normal test user with a separate email account and check it.

For other users this is related to following topic or if you are using joomla 3.1.1 and have problems with registration.
https://www.gavick.com/forums/game-maga ... 26929.html

There were bugs in joomla 3.1.1 where they caused following errors.

1. After clicking register button you had a blank page with no error although submission goes through and data gets stored in database.
2. Your additional fields are coming from User Profile Plug-in which is a Joomla Core plug-in. This plug-in did not store any data due to a bug in code.

Following are fixes for above mentioned bugs.

File: components/com_users/models/registration.php
Line: 105-111 which is below

Code: Select all

         // get all admin users
         $query = $db->getQuery(true);
         $query->select($db->quoteName(array('name', 'email', 'sendEmail', 'id')))
            ->from($db->quoteName('#__users')
            ->where($db->quoteName('sendEmail') . ' = ' . 1));

         $db->setQuery($query);


Replaced with below

Code: Select all

         // get all admin users
         $query = $db->getQuery(true);
         $query->select($db->quoteName(array('name', 'email', 'sendEmail', 'id')))
            ->from($db->quoteName('#__users'))
            ->where($db->quoteName('sendEmail') . ' = ' . 1);

         $db->setQuery($query);


Line: 517 - 523 which is below.

Code: Select all

         // Get all admin users
         $query = $db->getQuery(true);
         $query->select($db->quoteName(array('name', 'email', 'sendEmail')))
            ->from($db->quoteName('#__users')
            ->where($db->quoteName('sendEmail') . ' = ' . 1));

         $db->setQuery($query);


Replaced with below

Code: Select all

         // Get all admin users
         $query = $db->getQuery(true);
         $query->select($db->quoteName(array('name', 'email', 'sendEmail')))
            ->from($db->quoteName('#__users'))
            ->where($db->quoteName('sendEmail') . ' = ' . 1);

         $db->setQuery($query);


File: plugins/user/profile/profile.php
Line: 340 which is below

Code: Select all

               ->where($db->quoteName('userid') . ' = ' . (int) $userId)


Replaced with below

Code: Select all

               ->where($db->quoteName('user_id') . ' = ' . (int) $userId)


I have renamed your edited file:
/templates/gk_game_magazine/html/com_k2/templates/register.php to "register_previous_norman.php" while testing/checking and reuploaded a fresh untouched copy from template download. So current register.php is a default one.
If you want your edited file simply delete current register.php and rename "register_previous_norman.php" back to "register.php".

I have disabled certain fields in User Profile Plugin. If you want them please re-enable them in plugin settings.

Joomla Admin > Extensions > Plug-in Manager > User Profile Plugin.

I have also enabled error logs in your CPanel which will be active for 24 hours and then disable itself for debugging purposes.

See you around...

Thanks Norman. Should I also update the plugins/user/profile/profile.php with the updated code ?

Also, another issue I have is, as soon as an user is signed in, we get an error 500..

Is that another issue we need to fix ?

Thanks again for help us with this. Appreciate it very much.

Best Regards,

Hi Norman,
Also newly registered users are not getting the activation email. Not sure if this is another issue that needs to be looked at..

thanks,

I have already done the changes in your website. I will check user login error. Regarding emails I did receive many emails from registration when I used my email in registration.

Hi Norman,
After registering and logging in, when I go to my account profile and save any information there, it does not save it back to the back end. Also the fields in the registration are different from the ones I see in the profile. How do synchronize them ?

Sorry for all the questions.. but these errors in core joomla 3.1.1 makes me wonder if I should go to Joomla 2.5..

thanks,

Regarding backend fields.

As each component / plugin adds these fields you will have to edit relevant field in each component or plugin.
Also as I see you are not using K2 fields now so I believe you should turn off K2 user profiles.

In your current registration form following fields belongs to Joomla User.

Code: Select all

Name
Username
Email
Confirm email
Password
Verify password


Following fields are from user profile plugin.

Code: Select all

Address 1: *      
City:   (optional)


You can edit all of these fields in User Manager > Selecting user.
Account Details tab and at the end User Profile tab for fields from user profile plug-in.

Hi Norman,
What would be the difference between User and K2 User ? Can the regular user view the K2 items ?

Also, how do I force the registration to use K2 User profile instead of the regular Joomla User ?

Sorry for all the questions..

Thanks,

Hi Norman,
Like how I can control which fields are required / optional for the User profile plugin, how do I do the same for K2 user ? Where do I control which fields are required / optional..

Thanks a lot,

Regarding 500 error I cannot see that. Maybe it was a temporary issue.

Regarding which profile to use, there is no separate profiles, they just extend joomla profile. See my explanation in my main reply.

Example:

If joomla user has 5 standard fields.
then lets say K2 user adds another 4 fields.

Then in total for each user you have 9 fields.

In front-end user will see 9 fields.
In back-end admin will see them separately in each component / plugin.

What you can do is just use Joomla user profile along with a user_profile plug-in. This way they will be all in User manager for admins to edit.

Regarding which fields are optional;
Core joomla fields are required, such as username / password etc.
K2 fields are not configurable.
User Profile Plugin fields can be configured in
Joomla Admin > Extensions > Plug-in Manager > User Profile Plugin.

Although I can see there are still problems with user profile plug-in. This needs further check.
Today in few hours I ll experiment with another plug-in from joomla extensions directory and disable current user profile plug-in to check results.

I ll post back in here.

See you around...

Hi Norman,
Thanks for helping out yesterday. Kindly let me know if you have any further updates on the User Profile Plugin.

Also, one additional question on error 1064. Whenever I add a new mod_news_pro_gk4 module, I get the 1064 error. I had re-installed K2 yesterday and this issue started after that. How do I fix this ?

The datasource for the mod_news_pro_gk4 is a K2 category that exists in the system..

Thanks,

I haven't made any progress on user profile plug-in as checking the code I could not find any further errors in the code.

However following 2 problems remaining using profile plug-in.
Example fields used was address 1 and city.

1. While user edits profile in front-end;
a: Changing first name does change in database and visible in back-end but not in front-end. In front-end still displays old first name.
2. While editing profile in back-end;
a: Extra profile plug-in fields do appear in profile edit screen that is not enabled to show or used in that profile.

So I need more time to check on this.

Regarding new GK4 modules causing an error, I have checked your modules and site right now and cannot see any problems.

See you around...

Hi Norman,
I have disabled those new_pro_show gk5 modules in order to remove the 1064 error. Every time, I create a new_pro_show_gk5 module and link it to a K2 category datasource, I hit this problem.

Kindly let me know how to fix it..

Thanks

Hi Norman,
All the Profile fields are getting inserted in the backend during the user registration process. However, any updates to them from the Front end is not getting back to the back end.

You might see few optional pamaters in my registration pages and those are getting back to the back end during registration process.. nothing gets back during the updates..

Kindly help..

Thanks,

Could you please send your details again as I had to clean my inbox and I have lost your access details.

So I am assuming following issues still remaining.

1. User profile not updating data or updated data not visible in back-end.
2. Using GK5 Modules causes error 1064.

See you around...

Hi Norman,
I have sent you the required information to your personal email. Kindly check.

Thanks a lot..

Please check as user profile is fixed now. User can edit in front-end, Admin can edit in back-end which all works fine in my testing.

Had to disable K2 user profile option as that was the problem you were having. This obviously changes the way page looks.

See you around...

Thanks Norman. So there was no code change.. just disabling K2 User Profile ?

I have few questions:

Since we don't use K2 user profile, then the users will not be created in K2 Users table ?
What will be the impact if they are not created in K2 users table ?

Also, we used Captcha provided by K2 User profile.. is there any other alternative to that to prevent non-humans from doing the registration ?

Kindly let me know,

Thanks,

Check News Show PRO modules data source.
Probably one or more of these modules have data source set as K2 and thus after deleting K2 items, sql query from module is returning null and joomla 3.x showing this error.

Yes, indeed, this was the key. First try to disable "News Show Pro GK5" modules on by one to identify the problematic one. Then look at data source OR in my case "menu assignment". Thanks Norman