SQL Source Code Select

Quote

canton
Total posts: 76
24 Aug 2012 16:40

Please could you help me by creating a query via the SQL Source Code for the Select field.

CREATE TABLE IF NOT EXISTS #__jcs_user_subscr (

id int(10) unsigned NOT NULL auto_increment,

user_id int(10) unsigned NOT NULL default '0',

subscription_id int(10) unsigned NOT NULL default '0',

ctime datetime NOT NULL default '0000-00-00 00:00:00',

published tinyint(1) unsigned NOT NULL default '1',

extime datetime NOT NULL default '0000-00-00 00:00:00',

price float(10,2) NOT NULL default '0.00',

number varchar(200) NOT NULL default '',

access_count int(10) unsigned NOT NULL default '0',

access_limit int(10) unsigned NOT NULL default '0',

gateway varchar(45) NOT NULL default 'undefined',

gateway_id varchar(200) NOT NULL default '',

created datetime NOT NULL default '0000-00-00 00:00:00',

checked_out int(10) unsigned NOT NULL default '0',

checked_out_time datetime NOT NULL default '0000-00-00 00:00:00',

parent tinyint(1) unsigned default '0',

never_activated tinyint(1) unsigned NOT NULL default '0',

tracking tinyint(1) unsigned NOT NULL default '0',

PRIMARY KEY (id)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

CREATE TABLE IF NOT EXISTS #__users (

id int(11) NOT NULL auto_increment,

name varchar(255) NOT NULL default '',

username varchar(150) NOT NULL default '',

email varchar(100) NOT NULL default '',

password varchar(100) NOT NULL default '',

usertype varchar(25) NOT NULL default '',

block tinyint(4) NOT NULL default '0',

sendEmail tinyint(4) default '0',

registerDate datetime NOT NULL default '0000-00-00 00:00:00',

lastvisitDate datetime NOT NULL default '0000-00-00 00:00:00',

activation varchar(100) NOT NULL default '',

params text NOT NULL,

lastResetTime datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of last password reset',

resetCount int(11) NOT NULL default '0' COMMENT 'Count of password resets since lastResetTime',

PRIMARY KEY (id),

KEY usertype (usertype),

KEY idx_name (name),

KEY idx_block (block),

KEY username (username),

KEY email (email)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=37 ;

----------What im trying to query in my own words----------

I want to list all "users" from "#__users.username"

If #__jcs_user_subscr.subscription_id` = 2

And #__jcs_user_subscr.user_id=#__users.id`;

I hope this makes sense

Last Modified: 02 Mar 2014

Quote

Sergey

Total posts: 13,748
26 Aug 2012 06:25

If make simple query

SELECT id, username AS text 

    FROM jos_users 

    WHERE id IN (

        SELECT user_id 

        FROM jos_jcs_user_subscr 

        WHERE subscription_id = 2);

This is the basic. But if you want only users with unexpired subscription 2 you will have to check subscription status, and time.

SELECT id, username AS text 

    FROM jos_users 

    WHERE id IN (

        SELECT user_id 

        FROM jos_jcs_user_subscr 

        WHERE subscription_id = 2

        AND extime > NOW()

        AND ctime < NOW()

        AND published = 1);

Quote

canton
Total posts: 76
26 Aug 2012 13:49

Thank you it worked perfectly i just had to amend "jos_" to "#__" and then it worked a treat.

Just one question if i wanted to add a line of text to be appended to the list every time, such as.

"Independent"

Is this possible if so how will the sql read now?

I must admit the SQL Source Code Select e.t.c brings on unlimited amount of possibilities and application to problems that need previously needed scripts.

How easy you've made things for use and incredibly powerful you've made Cobalt.

Thanks again Sergey :)

Quote

Sergey

Total posts: 13,748
26 Aug 2012 19:10

Just one question if i wanted to add a line of text to be appended to the list every time, such as.

"Independent"

I have not completely understood what you mean. But may be you mean this

SELECT id, CONCAT(username, ' indepentend') AS text

FROM jos_users ....

Quote

canton
Total posts: 76
27 Aug 2012 00:53

thanks for replying no i mean the list will return as so...

Independent

user 1

user 2

user 3

.....

hope this makes sense

Quote

Sergey

Total posts: 13,748
27 Aug 2012 02:16

I see. I have added new parameter to new select field with next update.

Quote

canton
Total posts: 76
27 Aug 2012 02:19

great thank you Sergey i think many developers will find this useful

Quote

canton
Total posts: 76
27 Aug 2012 02:21

could you give the option to amend to the beginning or end of list for flexibility?

Quote

Sergey

Total posts: 13,748
27 Aug 2012 02:32

That is what I added to new select field. The option to insert -Select Element - value in the select list.

Quote

canton
Total posts: 76
27 Aug 2012 02:40

and i take it this will be added to all fields that have the "SQL Source Code" param

Quote

Sergey

Total posts: 13,748
27 Aug 2012 02:51

Not to all. It is useless in other fields. It only make sense in select field. In checkboxes you may just not select anything just like with radiobuttons and multiple select.

Quote

canton
Total posts: 76
27 Aug 2012 03:04

you right thank you once again :)

Quote

canton
Total posts: 76
30 Aug 2012 13:56

I have updated to the latest cobalt 7 with the new amended Select field "First Element of Select" parm doesn't reflect the property change. So in this case when i changed param to

First Element of Select: Independent

Doesn't reflect the change on the front end within the select list. it defaults to "- Select Element -"

please advise

Quote

Sergey

Total posts: 13,748
30 Aug 2012 21:22

Somehow I have overlooked it. Now in field template we can change first element.

Please update this field and open its /tmpl/input/default.php and in line 18 you will find first element. I did so that new parameter in general settings may set its label.

Quote

canton
Total posts: 76
31 Aug 2012 00:26

I have set the param to

First element of select: Independent

but unfortunately the changes don't take effect on the front end. Please advise.

Quote

Sergey

Total posts: 13,748
31 Aug 2012 03:48

have you changed select input template?

Are we talking about element on the form or something else?

Quote

canton
Total posts: 76
01 Sep 2012 02:50

Mmm for some reason this morning it seems to be working. If i have any issues in the mean time i will tell you other wise thanks for your help :D.

Login (copy)