Sql Action to publish an item

Please show complete table structure. and I'll suggest you SQL.

Please see below:

Activate SQL

UPDATE `#__jg_campaigns` SET published = 1 WHERE creator_id = [USER_ID]

Deactivate SQL

UPDATE `#__jg_campaigns` SET published = 0 WHERE creator_id = [USER_ID]

But keep in mind that this will puiblisha nd unpublish all campainigns of that user. You can add some limitations. For example only publish compaigns that was created 3 month ago.

UPDATE `#__jg_campaigns` SET published = 1 WHERE creator_id = [USER_ID] AND created > NOW() - INTERVAL 3 MONTH

Or for example only unpublish campaigns that are not yet ended and keep all ended campaigns published for history.

UPDATE `#__jg_campaigns` SET published = 0 WHERE creator_id = [USER_ID] AND end_time < NOW()

There might be other ideas if you give more input. What are those compaigns? How many campaigns user will be able to have? An so on.

Hi Sergey,

I picked this line for Deactivate SQL, thanks.

UPDATE #__jg_campaigns SET published = 0 WHERE creator_id = [USER_ID] AND end_time < NOW()

But i think you mean end_date right ?

Actually those are crowdfunding campaigns; user can have up to Two campaigns at once.

I'd like know how complete parameters above to unpublish campaign also if it received at least one donation ; i think it would not be fine to unpublish campaign if there is not donation yet.

Here is the structure of Donations table :

UPDATE `#__jg_campaigns` AS c
   SET c.published = 0 
 WHERE c.creator_id = [USER_ID] 
 AND c.end_date < NOW()
 AND (SELECT COUNT(*) FROM `#__jg_donations` AS d WHERE d.compaign_id = c.id) > 0

But you will have small problem. Deactivate subscription action is triggered only once. If it does not unpublish compaign, then when it receive donation, it will continue to be published because not deactivation action will be triggered again.

Perhaps, you need something like a cron job to do your task.

narcis But i think you mean end_date right ?

Of course. I type queries by hand without testing it. It is not copy/paste solution. It is an idea.

Perhaps, you need something like a cron job to do your task.

Good idea, thanks.