MySQL Field Type Plugin

I have created a MySQL Field Type Plugin for Community Builder 1.2 which queries the database and displays the result of a MySQL statement. You can download it here:

www.softforge.co.uk/resource-area/documentation/cb-mysql-field-plugin

I hope you find this useful, all feedback welcome.

Hello and thank you for this great plugin. But I have a little problem with it if I want to sort a user list based on this field type.

I use this plugin to show the amount of users Mosets Tree favourites in their profile. This works fine, many thanks. Now I created a user list and want to sort it by the amount of users favourites. This is the 500er error message I receive in debugging mode:

JDatabaseMySQL::query: 1054 - Unknown column 'cb_collected' in 'order clause' SQL=SELECT ue.*, u.*, '' AS 'NA' FROM jos_comprofiler ue, jos_users u WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND u.gid IN (18, 23, 24, 25) AND u.`id` = ue.`id` ORDER BY `cb_collected` DESC LIMIT 0, 50

It would be very kind if you could help me with this issue. Thanks in advance,

Camee

Ok, now I know what this means:

It does not actually add a new field to the database

What a pity! I thought, this plugin could be, what I was looking for.

Hi Camee,

Thanks for trying my plugin. My needs for it is purely to display information, I hadn't considered sorting. By the looks of the error message, CB can only sort on a column that actually exists in jos_comprofiler, which makes sense.

I didn't actually want to store the result because my data is constantly changing so I wanted it to be dynamically calculated on each page load.

I'm sure it would be possible to modify the plugin to insert the result of the SQL query into a pre-existing CB field so that it could be used for sorting. It would still be dynamic but you would have an extra INSERT statement each time it is was used so for lists, that would be 1 per user displayed.

I hope this helps.

I'll also add my vote that this is a really great CB plug-in. I 'd love to be able to sort on a calculated field. Would you every consider creating a newer version of the plug-in with the ability to use this field type to sort on in lists?

It would be nice to be able to sort on the calculated field but it does seems that CB can only sort on a 'real' field stored in jos_comprofiler.

The only way to do this would be as I described before but this would considerably increase the load on the system when displaying user lists.

If it becomes essential in the project I am using it for, I will add this feature and release a new version.