disable/archive data from being searched???

awayne96 · 11-03-2012, 04:37 AM

I'm trying to figure out the best way to disable/archive data from being searched.

I will have members making postings and say after 5 days from when it was originally posted I don't want that posting to be able to be searched anymore but would like it to still be available for the original poster to view from there members view in case they would like to renew the posting if it is still available... or they can delete or archive it to be able to use it again in the future.

I'm guessing I would have a table for the current listings that would also have a row for active or expired... With this it would allow the member to see on there personal postings page that they have x number current and x number expired which would also say ACTIVE or EXPIRED next to each posting.

Then have an archive table (which I already have) for the member to archive the listing which would be under a separate link for them to see those postings they had before

This is very important since not all member will keep there postings up to date and I don't want to be going through deleting old postings especially once I have many members.

I am using mysql, cgi, perl and php.

I look forward to your reply.

Old Pedant · 11-03-2012, 11:34 PM

Ummm...I think you are over-thinking this.

If you include a TIMESTAMP or DATETIME field in your record that records the time the posting was made, it's trivial. Just exclude older postings when listing or searching the postings.

Example: If you had a field whenPosted TIMESTAMP DEFAULT CURRENTTIMESTAMP column in your table, then you would just include that in all appropriate SELECTs:

Code:

SELECT * FROM postings WHERE whenPosted > DATE_SUB( CURDATE(), INTERVAL 5 DAY )

You *could* even use a VIEW to make this easier:

Code:

CREATE VIEW searchableLPostings
AS
SELECT * FROM postings WHERE whenPosted > DATE_SUB( CURDATE(), INTERVAL 5 DAY )

And now for all searches, etc., you use

Code:

SELECT ... FROM searchablePostings WHERE ...

and your searches won't even see any older posts.

awayne96 · 11-06-2012, 09:07 PM

Thanks for the response Old Pedant!

I do have an ADDED and UPDATED in the record...

What you posted will work for the searching purpose... But what I need is when the member that posted an item and lets say after 5 days from the last UPDATED time and date it would become "inactive or expired"

The member would see that on there own listings page where each posting would say "active" or "expired" on that page. If they were to update the listing, it would then say "active" again for another 5 days. If the member decided to "archive" a posting that is either active or expired, they would be able to and that posting would not be searchable by the other members.

I'm guessing that at that point, members would be able to search and the only results that would be displayed would be the "active" posts from members.

Old Pedant · 11-06-2012, 09:26 PM

If I understand you, you want members to see *ALL* their own postings but only "active" postings from other members, right?

It's still trivial.

Code:

SELECT ...
FROM postings
WHERE ( 
        memberid = $currentMemberId
     OR updated > DATE_SUB( CURDATE(), INTERVAL 5 DAY )
     )
...

The OR is all that is needed.

Note how I enclosed the ORed conditions in parentheses. If there are any other conditions in the WHERE you *need* those parentheses. They can only be omitted if there are no other WHERE conditions. I recommend always including them.

Old Pedant · 11-06-2012, 09:29 PM

And if you want it to *SAY* "active" or "expired", that's trivial as well:

Code:

SELECT IF( updated > DATE_SUB( CURDATE(), INTERVAL 5 DAY ) , 'active', 'expired' ) AS postingStatus,
    ... other fields...
FROM postings 
WHERE ( 
        memberid = $currentMemberId 
     OR updated > DATE_SUB( CURDATE(), INTERVAL 5 DAY ) 
      ) 
...

Though you could just as easily do the "IF" test in PHP code if you preferred.