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

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

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

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

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

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