MySQL Field Type Plugin

11 years 1 month ago #141295 by krileon
Replied by krileon on topic Re:MySQL Field Type Plugin
Everyone might want to review the document subscriber incubator project CB Query Field. Have created a plugin capable of providing a substitution supported query field. It can be displayed anywhere you like. There's no edit display of the field so it will not display in profile edit or registration (this will likely change if you don't want it to show in profile edit you'd need to use CB Privacy). Another tip is using a Delimiter field to display the field on registration (by way of substitutions) as it always renders the profile view, thus you could display it on Registration or Profile Edit.

Post edited by: krileon, at: 2010/09/03 21:33

Post edited by: krileon, at: 2010/09/03 21:34


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

11 years 1 week ago #144208 by richy81
Replied by richy81 on topic Re:MySQL Field Type Plugin
Hi,

I hope this is the right place for my query and someone may be able to help.

I have a price field in my user profile which i want to add 15%. I have created a MySQL field and with the following query:

SELECT SUM(cb_prim1*0.15 + cb_prim1)
FROM jos_comprofiler

However this adds all values in the prim1 column then adds 15%. My SQL is not very good but i beleive i need to group by user_id to reset the calculation, however im not sure how to make the plugin do this. Can someone please let me know if this is possible with this plugin and how to get ths working or at least point me in the right direction.

Thanks

Please Log in to join the conversation.

11 years 1 week ago #144209 by richy81
Replied by richy81 on topic Re:MySQL Field Type Plugin
Hi,

I've spent several days in total over the last week trying to make this work, I post a comment and then stumble on the following SQL query that worked

SELECT cb_prim1 * 0.15 + cb_prim1
FROM jos_comprofiler, jos_users
WHERE jos_comprofiler.id = jos_users.id AND jos_comprofiler.id = 69

May not be the best code but works and thats the main thing :)

Please Log in to join the conversation.

11 years 6 days ago #144405 by kbotnen
Replied by kbotnen on topic Re:MySQL Field Type Plugin
Hi,

Just wanted to point out that sometimes when trying to use the query field (incubator one), it keeps add "rn" to part of my query string.

I dont know why and when, at glance it looks random.

Maybe it would have been an idea to give some example on proper formatting for the input query? jos_comprofiler or #__Comprofiler, and similar.

Like the MySql field from Softforge gives you a example input string if you dont enter anything in the query field. I think thats user friendly.

But! neither of the fields let me use them as a sorting criteria :/ I am trying to sort by an interval (date from - date to), and I manage to get the result out as a printed value in the profile / user list, but I cant manage to sort the list by the value.

-K

Post edited by: kbotnen, at: 2010/10/20 09:37

Please Log in to join the conversation.

10 years 10 months ago #148474 by serjo
Replied by serjo on topic Re:MySQL Field Type Plugin
Hi,
has anyone tested this with CB 1.3.1?
I can't seem to get anything out of this:(

As a starter, all I would like to do is:

SELECT firstname
FROM jos_comprofiler
WHERE viewer_id = {USERID}

Thanks for your help

using:
CB v2.0.x
CBsubs v 4.1.x
Joomla 3.3+

Please Log in to join the conversation.

10 years 9 months ago #149768 by ubertramp
Replied by ubertramp on topic Re:MySQL Field Type Plugin
medaacek wrote:

EDIT:
OK, I have found the solution myself, it is perfect plugin, with a little thinking is capable of what is otherwise difficult to do:

in my case, to get the number of records I used:

SELECT: LENGTH(cb_recordedspeciestotal) - LENGTH(REPLACE(cb_recordedspeciestotal, 'b', ''))
FROM: #__comprofiler
WHERE: user_id = {USERID}

the function in "SELECT" counts the occurence of "b" in the table column.



Hello, this seems to be exactly what Im loking for! Could you please help me defining the plugin for my needs?

In my jos_comprofiler database table there is a field "cb_recordedspecies" that contains all the recorded bird species of the user, the species are substituted by a simple code:

b1|*|b2|*|b3|*|b4|*|b5|*|b6|*|b7|*|b8|*|b9|*|b10

each b is different bird

Now I need to automatically add the field that simply counts the total number of recorded species (i.e. "b" in the table field), it the case of example there would be 10 species recorded by the user - how do I define your plugin?

I tried e.g. this but it did not work (I know nothing about database commands):

SELECT b,count(b)
FROM #__comprofiler.cb_recordedspecies
WHERE user_id={USERID}

Thank you very very much for your help!!!

Post edited by: medaacek, at: 2010/04/22 15:15

Post edited by: medaacek, at: 2010/04/22 19:25


im trying to do a similar thing for my site using this fantastic plugin, did you ever resolve this?

essentially, i have a field (cb_species2010) with 75 different checkboxes - each checkbox is associated with a species of fish and each time someone catches a species they can update their record of what they have caught.

this is output to the user's profile as 'mackerel, cod, pollack etc' and, on looking at the jos_comprofiler table under the 'cb_species2010' column associated with each user, it comes up as 'mackerel|*|cod|*|pollack' etc.

I too want to be able to count the total number of species and put it in a delimiter field with something like 'user xx has caught xx species'.

The problem i am having is being able to count the number of species. Basically, I can't find the right MySQL command to put in the MySQL Field Type Plugin!

If anyone has any ideas itd be great if you could help. Ive spent ages trying to work this one out with no joy!

Many thanks

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.245 seconds

Facebook Twitter LinkedIn