MySQL Field Type Plugin

10 years 5 months ago - 10 years 5 months ago #153496 by some1new
Replied by some1new on topic Re:MySQL Field Type Plugin
Still trying to get it to work.
Always get an error 500 on the database when trying to insert a mysqlfield into userlist.
The field is correctly shown in the profile but does not work within a userlist.

I´m getting frustrated and don´t know why the use in the list does not work.

Simply try to make "SELECT usertype FROM #__users WHERE id = {userid}".

There are members in the list.

EDIT: Found the error, some tables in jos_comprofiler_fields were still hardcoded. There´s jos_+++ instead of #__++++. This has to be changed. Don´t know where this came from.

Please Log in to join the conversation.

10 years 5 months ago #154945 by rridderhof
Replied by rridderhof on topic Re:MySQL Field Type Plugin
Hi there,

I am using CB 1.3.1 on a Joomla 1.5.21 with PHP 5.2.9 environment with MySQL Field plugin v1.2.
I have setup some fields that show info from another table depending on a field on comprofiler table.

If I browse through the user profiles, these defined fields are shown and filled with the result of the query, however EVERY profile gives the same results as the first profile retreived despite the fact the query uses an ID to get different items per user.

the query :
SELECT e.tname_2
FROM #__event_table_edit_1 e, #__comprofiler c
WHERE e.tname_0=c.cb_instrumentid

Is this a bug or am I missing something?

Please Log in to join the conversation.

10 years 5 months ago #155302 by learnthrusong
Replied by learnthrusong on topic Re: MySQL Field Type Plugin
Hi there,

Firstly - awesome plugin. This is exactly what I needed.

I am using the plugin to display information such as organisation name, area, and project for my users from a custom database table based on numeric values stored in the users CB Fields.

I therefore require the use of cb field substitutions in the WHERE clause to achieve this however, I am unable to do this. The field simply doesn't appear if I use [cb_fieldname] format for the substitution.

To check that the plugin is working, I input the static ID instead, which returns the desired result so I know that this is a substitution issue and not that the plugin just doesn't work period.

The query I am trying to run is;
SELECT name FROM lts_schools WHERE id = [cb_testpapram]

I have just upgraded CB to 1.4 from 1.3.1 in the hope that this would fix things however this is not the case.

Hope you can help. Thanks in advance,

Gez

Please Log in to join the conversation.

10 years 5 months ago #155312 by softforge
Replied by softforge on topic Re: MySQL Field Type Plugin

rridderhof wrote: If I browse through the user profiles, these defined fields are shown and filled with the result of the query, however EVERY profile gives the same results as the first profile retreived despite the fact the query uses an ID to get different items per user.

the query :
SELECT e.tname_2
FROM #__event_table_edit_1 e, #__comprofiler c
WHERE e.tname_0=c.cb_instrumentid

Is this a bug or am I missing something?


It looks like you need to specify the user to look up in the query. Try something like this:
SELECT e.tname_2
FROM #__event_table_edit_1 e, #__comprofiler c
WHERE e.tname_0=c.cb_instrumentid AND c.id = {USERID}

learnthrusong wrote: Hi there,

Firstly - awesome plugin. This is exactly what I needed.

I am using the plugin to display information such as organisation name, area, and project for my users from a custom database table based on numeric values stored in the users CB Fields.

I therefore require the use of cb field substitutions in the WHERE clause to achieve this however, I am unable to do this. The field simply doesn't appear if I use [cb_fieldname] format for the substitution.

To check that the plugin is working, I input the static ID instead, which returns the desired result so I know that this is a substitution issue and not that the plugin just doesn't work period.

The query I am trying to run is;

SELECT name 
FROM lts_schools 
WHERE id = [cb_testpapram]


Thanks!

Firstly, make sure you have set "Use Substitutions" to "User Fields" (I'm sure you already have). It maybe that the substitution is only applied to the output (I can't remember of the top of my head). Try putting [cb_testpapram] in Add Prefix to test this.

A quick solution I can think of is add a join to the comprofiler table, something like this:
SELECT name 
FROM lts_schools s, jos_comprofiler c
WHERE s.id = c.cb_testpapram AND c.id = {USERID}

Good luck!

If you like our plugins, please consider writing a review on the Joomla Extension Directory or the Community Builder Directory ...
The following user(s) said Thank You: learnthrusong, Cruisemaster

Please Log in to join the conversation.

10 years 5 months ago #155313 by learnthrusong
Replied by learnthrusong on topic Re: MySQL Field Type Plugin
Hi there,

Thanks for the prompt reply!

I have substitutions set to on so will try what you suggested RE: putting the [cb_testpapram] into the prefix.

Failing that, I will try your second suggestion. On this note, could you please further explain what I need to do?

Thanks again,

Gez

Please Log in to join the conversation.

10 years 5 months ago #155316 by learnthrusong
Replied by learnthrusong on topic Re: MySQL Field Type Plugin
Hi Again,

adding '[cb_testpapram]' (without'') to the prefix did not make a difference. Regarding the join you describe below - should I just run this query in my phpmyadmin Qery window to perform the join?

softforge wrote:

rridderhof wrote:
A quick solution I can think of is add a join to the comprofiler table, something like this:

SELECT name 
FROM lts_schools s, jos_comprofiler c
WHERE s.id = c.cb_testpapram AND c.id = {USERID}

Good luck!


Is there anything else you might suggest as to the reason that this isn't working?

I'm going to have a lot of these fields and would prefer not to have to add this join everytime. Could you adapt the plugin for a small fee?

Many thanks,

Gez

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.438 seconds

Facebook Twitter LinkedIn