Error when using field filter on dbjoin

[rackem]

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?

 

[rackem]

*friendly bump*

 

[cheesegrits]

Setting up a test case now.

-- hugh

 

[cheesegrits]

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

 

[rackem]

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

 

[rob]

replace

active = 1

with

{thistable}.active = 1

 

[rackem]

Just tried the replacement Rob. That fixes the error when rendering as a dropdown but the initially reported error still occurs.

 

[rackem]

*friendly bump*

 

[juuser]

I can confirm this!

 

[rackem]

*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?

 

[rackem]

*friendly bump*

 

[rackem]

*friendly bump*

 

[cheesegrits]

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

 

[juuser]

Thanks Hugh, I have PHP 5.3.8.

Unfortunately I have closed intarnet site. Racem, maybe you can give access to your site???

 

[rackem]

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.

 

[juuser]

Friendly bump

 

[rackem]

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.

 

[cheesegrits]

Yeah, I was out of town yesterday afternoon.

I'm around all week ...

-- hugh

 

[rackem]

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*

 

[cheesegrits]

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