Hello John,

You can run the SQL query below to unpublish all items

update jos_discuss_posts set `published`='0';

That will set all posts to unpublished. I only want to set the posts with 0 replies to unpublished.

Ops, sorry I thought you wanted to set everything to be unpublished. Unfortunately I don't think you can achieve this just by a single sql query. You probably need to run some php codes to update this.

Could you tell me the table and field that contains the number of replies? Or does Easydiscuss total a field? Either way, could you tell me the fields and tables?

Hello John,

You can get the total replies by using this query:

SELECT COUNT(id) AS `replies` FROM `#__discuss_posts` WHERE `parent_id` = '22' AND `answered` = '0' AND `published` = '1'

The parent_id is your discussion id.
Please give it a try.

This statement gives me what I need for a select, but now I need to convert it into an update statement that updated "published" to "0" if replies = 1.

SELECT id, content, COUNT(*) as 'replies' FROM `zt9vo_discuss_posts` GROUP BY parent_id order by replies desc

Any ideas?

Hello John,

Did you want to unpublish any discussion that has only 1 reply? If so, please try to run this SQL statement:

 update zt9vo_discuss_posts as a 

	inner join (select sb.parent_id from zt9vo_discuss_posts as sb where sb.parent_id > 0 group by sb.parent_id having count(sb.parent_id) = 1) as b on a.id = b.parent_id

	set a.published = 0;

Please make a backup of your database before run this.

That did get rid of a lot of them, but I'm still seeing a lot with 0 replies.

I attached the table if that helps.

Hello John,

Did you also want to unpublish discussion with 0 reply? If so, you can try run this SQL:

update zt9vo_discuss_posts as a 

	inner join (select sb.parent_id from zt9vo_discuss_posts as sb where sb.parent_id > 0 group by sb.parent_id having count(sb.parent_id) = 0) as b on a.id = b.parent_id

	set a.published = 0;

By the way, I didn't see the attached table.

I ran it and no rows were affected.

For example, the post with an id of 2720 has no replies but is still posted.

Hello John,

I'm sorry, please try this:

DELETE FROM `zt9vo_discuss_posts` WHERE created = replied AND parent_id = 0

If this doesn't work, you can provide us your backend and phpmyadmin so we can help you identify run other SQL.

That deleted all of the topics except for 3, so I had to restore.

my cpanel can be reached at:
*EDITED

Hello John,

I'm sorry for the previous reply. I accidentally use DELETE instead of UPDATE to published=0. I've update your table to put published=0 for every post that has no reply. Please have a look.

now I only see 2 topics when I go to the site and select the easydiscuss menu. there should be more than a thousand.

Hello John,

It is because the discussion that has no post has been unpublished. When I run the sql statement, it shows that 1000++ discussion with no reply. Means that, based on your requirement, every discussion that has no reply, make it unpublished. All those 1000++ discussion with no reply has been unpublished and you won't see it in the frontend.

I am traveling and dont have computer access but I know there are a lot more than 2 topics with replies.

I am thinking it might be more reliable to create a temp table with id of posts with parent of 0 and count of replies and update posts table to set published 0 for reply count of 0