10 years ago
20
Topic
Hi

With the select dynamic fields we often are blocked because we need to write a WHERE statement that takes some variables from other fields.

For example we need to be able to write

SELECT title,id FROM #__com_content WHERE catid=$fields['my_other_field']

But the where statement and even the free query don't seem to accept $fields[]

My only solution is to use a CODE field with a JCckDatabase::loadObjectList( $query ) call. It would be much simpler if we could use the select dynamic field

I also have the solution with the select dynamic cascade.

but can we just use a $field[] syntax in the select dynamic please ?

thanks for your answer!

cyril



Get a Book for SEBLOD
10 years ago
11
Level 1
I must add I know I can hack the /plugins/cck_field/select_dynamic/select_dynamic.php file with some code like

$user =& JFactory::getUser();
$userId = $user->get( 'id' );
$opt_where = str_replace( '[me]', $userId, $opt_where );

and adding 'and created_by=[me]' in the WHERE statement of the plugin
10 years ago
10
Level 2

Thanks Cyril, vvery useful this hack !

Dom.

9 years ago
9
Level 3

Hello,

Is there a better solution by now with the new version of Seblod? I don't like to hack the core. 

Thanks

9 years ago
8
Level 4

Hi

We developped our own plugins with additional SQL filters such as [me] . This is the best approach I see so far.

I don't believe the current select dynamic allows what we are looking for

thanks

cyril

164 Posts
redback
8 years ago
7
Level 5

Made a quick & dirty Field Plugin like described. 

Fieldtype: Dynamic Select Extusrid

Example by own Construction: .... WHERE created_user_id = [userid] ....

I don't know how to upload a zip file. There i renamed it in a PDF. Just replace the filename .pdf through .zip, than you can install.

14 Posts
Antares74
8 years ago
6
Level 6

Hi, i need to use a select with a variable in WHERE statement.

I have read this post, I installed the plugin; but I can not make it work.

In my form I have a field containing a numeric variable; I set the plugin with a free query like this:

 SELECT nr_pratica,id FROM #__cck_store_form_servizi WHERE utente_associato = [variabile2]

Unfortunately I receive an error like this: 

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[variabile2]' at line 1 SQL=SELECT nr_pratica,id FROM #__cck_store_form_servizi WHERE utente_associato = [variabile2]

If I replace the variable with a numerical value everything works.

What am I doing wrong?

Thanks a lot!

8 years ago
5
Level 7

Hi

This is why we developped a Pulsar_select_dynamic that can accept PHP to retrieve variables

cyril

14 Posts
Antares74
8 years ago
4
Level 8

Hi Cyril,

Can you give me some more information?

Where do I get this plugin?

Thanks!

14 Posts
Antares74
8 years ago
3
Level 9

UP

8 years ago
2
Level 10

Hi

you can get it HERE

not sure it works with all Seblod version though

thanks

cyril

8 years ago
0
Level 11

Dear Cyril,

What do we do for Select Dynamic Cascade? You said you had a solution

What I want to do is have WHERE id = $user , the live user.

Ok thanks,

Steviec1

8 years ago
0
Level 11

Dear Cyril,

I tried the plugin but I don't understand how to use the variables in the query. Where do I put the php? How can I call another field or a the logged in user?

Thank you,

8 years ago
1
Level 1

Hi

This version of the select dynamic allows you to add PHP statements inside your free query. Just insert your PHP code with your SQL queries between the [PHP] and [/PHP] tags


cyril

8 years ago
0
Level 2

Thank you,

You need to help me out. Nothing works. Example.

[PHP]

$qwerty = 'stevie';

$opt_where = str_replace( '[me]', $qwerty, $opt_where );

[/PHP]

SELECT school_teachers AS text FROM 123_cck_store_form_teachers WHERE 123_cck_store_form_teachers.teachername= '[me]';

8 years ago
5
Level 1

Ok I see, this is not the way the plugin was designed. The [PHP] tag has to be used the same way you use <php> in a html page.

the value you return has to be sent with echo() or return()


SO the correct syntax would be similar to:

SELECT pet_names AS text FROM yb9_cck_store_form_customers WHERE yb9_cck_store_form_customers.customersdogwalker = [PHP] $qwerty = 'stevie'; echo($qwerty); [/PHP]


cyril



7 years ago
3
Level 2

Hi, I try Pulsarinformatique and Redback plugin and method, but not working. I dont know why, to lazy to find out. 

But I can the job done using "Dynamic Cascade Field" like pulsar mention in first post.

Maybe someone wonder how to achieve it with "Dynamic Cascade", here I go :

basically I make 2 field Dynamic Cascade. Make the first act like 'WHERE' condition, by assign live value to it. I using live value user->id.

then in the second field will be always display the result based on first field which is previously selected by live value current ID.

then make additional CSS to hide the first field.

done.

But will be nice if seblod team can improve "Select Dynamic" to have capabilty read WHERE clause with current other field like firt post explained.

thankyou

248 Posts
Giuse
7 years ago
1
Level 3

+1, it'd be very useful

21 Posts
Roote
7 years ago
0
Level 4

Same here.  Would be useful.

131 Posts
layonill
5 years ago
0
Level 3

can you show me how?

7 Posts
Chris@FM
6 years ago
0
Level 2

Hi Cyril,

i´ve tryed to use your Plugin to make a query in combination with a session value but i always get an sql error... 

"1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[PHP] $session = JFactory::getSession(); $job_type = $session->get('jobtype');' at line 1"

the table and the split are correct.

The session value is there I´ve dumped it.

Maybe I´ve used the wrong syntax?

Could you take an eye on my query? 

[PHP]

$session = JFactory::getSession();

$job_type = $session->get('jobtype');

var_dump($job_type);

[/PHP]

SELECT title FROM #__cck_store_item_cck_fm_jobs WHERE #__cck_store_item_cck_fm_jobs.state = ´1´ AND #__cck_store_item_cck_fm_jobs.job_type = [PHP] $job_type [/PHP];


Thanks in advance for your help.


Best regards Chris

Get a Book for SEBLOD