SQL Plugin in action!

14 years 11 months ago #95084 by krileon
SQL Plugin in action! was created by krileon
Need some queries? Want to know the plugins power? Here's a few that might get you started in the right direction. ;)

SOBI2 - Publish/Unpublish on plan Activation/Deactivation
ACTIVATION: (Publish Listings)
[code:1]
UPDATE `#__sobi2_item` SET `published` = 1 WHERE `owner` = [user_id]
[/code:1]
DEACTIVATION: (Unpublish Listings)
[code:1]
UPDATE `#__sobi2_item` SET `published` = 0 WHERE `owner` = [user_id]
[/code:1]

Mosets Tree - Publish/Unpublish on plan Activation/Deactivation
ACTIVATION: (Publish Listings)
[code:1]
UPDATE `#__mt_links` SET `link_published` = 1 WHERE `user_id` = [user_id]
[/code:1]
DEACTIVATION: (Unpublish Listings)
[code:1]
UPDATE `#__mt_links` SET `link_published` = 0 WHERE `user_id` = [user_id]
[/code:1]

JoomGallery - Publish/Unpublish on plan Activation/Deactivation
ACTIVATION: (Publish Images)
[code:1]
UPDATE `#__joomgallery` SET `published` = 1 WHERE `owner` = [user_id]
[/code:1]
DEACTIVATION: (Unpublish Images)
[code:1]
UPDATE `#__joomgallery` SET `published` = 0 WHERE `owner` = [user_id]
[/code:1]

JoomGallery - Delete on plan Deactivation
DEACTIVATION: (Delete Images)
[code:1]
DELETE * FROM `#__joomgallery` WHERE `owner` = [user_id]
[/code:1]

JoomGallery - Approve/Unapprove on plan Activation/Deactivation
ACTIVATION: (Approve Images)
[code:1]
UPDATE `#__joomgallery` SET `approved` = 1 WHERE `owner` = [user_id]
[/code:1]
DEACTIVATION: (Unapprove Images)
[code:1]
UPDATE `#__joomgallery` SET `approved` = 0 WHERE `owner` = [user_id]
[/code:1]


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.

14 years 11 months ago #95096 by klp29
Replied by klp29 on topic Re:SQL Plugin in action!
Let me add to this:

We cosponsored the Moset Tree "Plugin" and we not only use if for publishing/unpublishing but also for registrering the price on the listing (because we sort after price, so the highest paid ads are on top).

Like this:

[code:1]UPDATE `#__mt_links` SET `price` = 2000.00 WHERE `user_id`
= [user_id][/code:1]

Please Log in to join the conversation.

14 years 11 months ago #96882 by msrc
Replied by msrc on topic Re:SQL Plugin in action!
Hi

I am trying to apply membernumber to our users/members. So when they subscribe they get a member number. The code I used is below. what I would really like is a way to automate the process. Use the SQL feature to save the last membernumber and then use it to assign a new member with a new number by adding one and then saving it again in a last membernumber table. this process works when i run SQL in phpMyAdmin but not with CBsubs. Can someone help?

Code:

UPDATE '#_comprofiler'
SET
'cb_membernumber'=1
WHERE 'user_id'=[user_id]

As you can see at this time I am only trying to add a number to a singe user who subscribed.

Code:

UPDATE '#_comprofiler'
SET
'cb_membernumber'=jos_membernumber.lastnumber+1,
jos_membernumber.lastnumber=jos_membernumber.lastnumber+1
WHERE 'user_id'=[user_id]

As you can see here it would be automated where a new number would be stored as the last number used and the next time that number can be used to create a new number.

Please help.

:unsure:

Please Log in to join the conversation.

14 years 11 months ago #96902 by nant
Replied by nant on topic Re:SQL Plugin in action!
msrc wrote:

Hi

I am trying to apply membernumber to our users/members. So when they subscribe they get a member number. The code I used is below. what I would really like is a way to automate the process. Use the SQL feature to save the last membernumber and then use it to assign a new member with a new number by adding one and then saving it again in a last membernumber table. this process works when i run SQL in phpMyAdmin but not with CBsubs. Can someone help?

Code:

UPDATE '#_comprofiler'
SET
'cb_membernumber'=1
WHERE 'user_id'=[user_id]

As you can see at this time I am only trying to add a number to a singe user who subscribed.

Code:

UPDATE '#_comprofiler'
SET
'cb_membernumber'=jos_membernumber.lastnumber+1,
jos_membernumber.lastnumber=jos_membernumber.lastnumber+1
WHERE 'user_id'=[user_id]

As you can see here it would be automated where a new number would be stored as the last number used and the next time that number can be used to create a new number.

Please help.

:unsure:


Most likely you can use the CB Fields integration plugin to do this - no need to expose yourself to SQL for this task.

Please Log in to join the conversation.

14 years 10 months ago #97318 by msrc
Replied by msrc on topic Re:SQL Plugin in action!
Hi

Thanks for the idea, but how would I use the CB Fields integration to save the last number and then use it to create a new one?

Thanks

Please Log in to join the conversation.

14 years 10 months ago #97344 by msrc
Replied by msrc on topic Re:SQL Plugin in action!
Hi

Forgot to mention that I tried to use the CB Fields, but it doesn't work. All it does is enter the what I put in to the value box, even when I select the Field=Field+Value option.

I am not sure why CB is so restrictive. I have been trying for months to find a way to automate our membership number generation but no luck. It is disappointing at the least that no one has a solution for me. Can't believe we can't accomplish this even which such costs involved in getting this product.

:angry:

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.432 seconds