• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Error when using field filter on dbjoin

Status
Not open for further replies.

rackem

Well-Known Member
I have a dbjoin element in my list that is set up with a field filter. I receive the following error when I enter a search term in that filter field. For this example, I entered "brass".

getData: 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 '\' - \', mps_locations.city, \', \',mps_locations.state) LIKE '%brass%' )' at line 5 SQL=SELECT COUNT(DISTINCT `mps_players`.`id`) AS t FROM mps_players LEFT JOIN `btxz_users` AS `btxz_users` ON `btxz_users`.`id` = `mps_players`.`edit_by` LEFT JOIN `btxz_users` AS `btxz_users_0` ON `btxz_users_0`.`id` = `mps_players`.`owner` LEFT JOIN `mps_players_32_repeat` AS `mps_players_32_repeat` ON `mps_players_32_repeat`.`parent_id` = `mps_players`.`id` LEFT JOIN `mps_locations` AS `mps_locations` ON `mps_locations`.`id` = `mps_players_32_repeat`.`place` WHERE ( CONCAT(mps_locations.name, \' - \', mps_locations.city, \', \',mps_locations.state) LIKE '%brass%' )

Attached are my element settings. It seems to be due to the ' characters in the Concat label as the error disappears if I don't use them. Interestingly, it only happens on my live site and not my local test setup. Here is the page: http://www.mypoolstats.com/player-ratings. The filter in question is the "Where Found".

Any ideas why this occurs?
 

Attachments

  • p1.png
    p1.png
    43.7 KB · Views: 185
  • p2.png
    p2.png
    22.6 KB · Views: 193
  • p3.png
    p3.png
    22.7 KB · Views: 180
Are you sure you are running the latest github?

My test seems to work OK:

WHERE ( CONCAT(fab_profile.last_name, ', ', fab_profile.first_name) LIKE '%cheese%' )

... so no escaping of the single quotes.

-- hugh
 
Yes, it has been this way through many updates from GitHub. I was dealing with some other issues when I first discovered it and am just now reporting it.

Not sure where to start troubleshooting further. Interesting it works for you and on my local setup. ???

It works fine using the autocomplete option. I tried the dropdown option and received this error when loading the list:

filter query error: place Column 'active' in where clause is ambiguous SQL=SELECT DISTINCT(CONCAT(mps_locations.name, ' - ', mps_locations.city, ', ',mps_locations.state)) AS `text`, `mps_players_32_repeat`.`place` AS `value` FROM `mps_players` LEFT JOIN `btxz_users` AS `btxz_users` ON `btxz_users`.`id` = `mps_players`.`edit_by` LEFT JOIN `btxz_users` AS `btxz_users_0` ON `btxz_users_0`.`id` = `mps_players`.`owner` LEFT JOIN `mps_players_32_repeat` AS `mps_players_32_repeat` ON `mps_players_32_repeat`.`parent_id` = `mps_players`.`id` LEFT JOIN `mps_locations` AS `mps_locations` ON `mps_locations`.`id` = `mps_players_32_repeat`.`place` WHERE `mps_players_32_repeat`.`place` IN ('1','2','18','3','44','12','5','17','22','4','37','23','27','25','7','29','6','43','21','13','20','32','50','41','16','19','8','47','49','35','46','24','31','26','28','33','10','34','36','40','48','9','0','11','51') AND active = 1 LIMIT 0, 100
 
Just tried the replacement Rob. That fixes the error when rendering as a dropdown but the initially reported error still occurs.
 
*friendly bump*

The initially reported problem still occurs. Not sure if it matters but the dbjoin element is in a repeated group.

For some reason, a "\" is being added to the query before each single quote. I realize you weren't able to duplicate this initially but I am wondering what sort of settings on my end I could begin to test to try to isolate the problem. Could this be a server setting, PHP or mySQL version, etc?
 
I just tested with join elements in both a non-repeat and repeat group, works, still doesn't put those \ in...

WHERE ( CONCAT(fab_profile.last_name, ', ', fab_profile.first_name) LIKE '%cheese%' AND CONCAT(us_states.name, ' (', us_states.abbr, ')') = 'al' )

... and I'm still struggling to figure out what's going on.

What version of PHP are you running?

I may need to get my hands on your server to track this one down.

-- hugh
 
Thanks Hugh, I have PHP 5.3.8.

Unfortunately I have closed intarnet site. Racem, maybe you can give access to your site???
 
Thanks for looking into this Hugh. I am running PHP 5.3.20. I will catch you on Skype to look at this further. We will keep you posted juuser.
 
Sorry, I got hung up on some other things. I tried to catch Hugh last night but he was probably at a gig, watching the Superbowl, or getting some well deserved rest. Will try again tonight.
 
Update: still trying to catch Hugh on this. As a hobby user, I only have a few hours in the evenings on a few days a week to try and catch him. The queue for Monday and Wednesday outlasted me. Will try again Friday evening. *fingers crossed*
 
Yeah, sorry about that, I passed out last night before getting to you in the queue.

You just pinged me on Skype about ten mins ago, I've responded, and am around for the next couple of hours.

-- hugh
 
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top