MYSQL function into fields list doesn't work

I created a multiple tables connection with CCv4.

However I would like to use the MySQL TRIM function into the Fields List field on the Fields tab because field values into one table (namely Joomla user profiles table) are enclosed with double quotes.
So I put into Fields List the following:

Profile.user_id, User.id, TRIM(BOTH '"' FROM Profile.profile_value) AS Profile.plaats

Unfortunately this doesn't work and it gives an unknown column error.

My query seems to be correct as I checked this into PHPmyadmin.

How to fix this?

Hi Gee,

Sometimes strange things happen with quotes in the CC processing :-( I suggest that you turn on the CC Debugger and see what query is being generated.

Bob

Hi Bob,

I enabled the CC Debugger and it displays the following about the SQL part:

[SQL] => Array
        (
            [0] => SELECT `Product`.*, `Product`.`prod_name` AS `Product.prod_name`, `Product`.`catid` AS `Product.catid`, `Category`.`id` AS `Category.id`, `Category`.`title` AS `Category.title`, `Product`.`prod_amount` AS `Product.prod_amount`, `Product`.`description` AS `Product.description`, `Product`.`prod_image` AS `Product.prod_image`, `User`.`id` AS `User.id`, `User`.`name` AS `User.name`, `Product`.`user_id` AS `Product.user_id`, `Profile`.`user_id` AS `Profile.user_id`, `User`.`id` AS `User.id`, TRIM(`BOTH '"' FROM Profile`.`profile_value`) AS `Profile.plaats` FROM `jos_auction_products` AS `Product` INNER JOIN `jos_categories` AS `Category` ON Category.id=Product.catid INNER JOIN `jos_users` AS `User` ON User.id=Product.user_id INNER JOIN `jos_user_profiles` AS `Profile` ON Profile.user_id=User.id WHERE   Product.published > 0 AND Profile.profile_key='profile.city'  ORDER BY `Product`.`id` LIMIT 0,20
        )

And this is the error message:

Unknown column 'BOTH '"' FROM Profile.profile_value' in 'field list' SQL=SELECT `Product`.*, `Product`.`prod_name` AS `Product.prod_name`, `Product`.`catid` AS `Product.catid`, `Category`.`id` AS `Category.id`, `Category`.`title` AS `Category.title`, `Product`.`prod_amount` AS `Product.prod_amount`, `Product`.`description` AS `Product.description`, `Product`.`prod_image` AS `Product.prod_image`, `User`.`id` AS `User.id`, `User`.`name` AS `User.name`, `Product`.`user_id` AS `Product.user_id`, `Profile`.`user_id` AS `Profile.user_id`, `User`.`id` AS `User.id`, TRIM(`BOTH '"' FROM Profile`.`profile_value`) AS `Profile.plaats` FROM `jos_auction_products` AS `Product` INNER JOIN `jos_categories` AS `Category` ON Category.id=Product.catid INNER JOIN `jos_users` AS `User` ON User.id=Product.user_id INNER JOIN `jos_user_profiles` AS `Profile` ON Profile.user_id=User.id WHERE Product.published > 0 AND Profile.profile_key='profile.city' ORDER BY `Product`.`id` LIMIT 0,20

Hi Gee,

As you can see the quotes get messed up

TRIM(`BOTH '"' FROM Profile`.`profile_value`) AS `Profile.plaats`

I suspect that CC doesn't know MySQL well enough to parse the clause correctly and it is just treating the whole of BOTH '"' FROM Profile as if it were a table name prefix :-(

Barring any clever insight from Max I'd probably approach this by using a custom listing and cleaning the data in the Header (or Body) box.

Bob

Thanks Bob.

I solved this by using the PHP TRIM function into the Body box in stead of using the CC variable:

<?php echo trim($row[Profile][plaats], '"')?>

However it is not the most efficient solution but it works. Still curious if this still can be done with CC by the MySQL SELECT function.