Hi,
I have a news and events database and I would like to be able to display the news items on my web page by the most current, say everything in the last month, and also automatically archive the rest so if they are older than a month, they are displayed on the archive page.

At the moment I have a field named STATUS which contains items called CURRENT, ARCHIVE and STORE. This allows me to manually update my pages but it would be more efficient if I could have a stored procedure that would do it automatically.

Thanks in advance!

Stop using the "status" field and filter the news accordingly on the front-end (the PHP/ASP script you're using). you can use a query that will only display items that are from the last 30 days.
<pre id=code><font face=courier size=2 id=code>
select * FROM &lt;news_table&gt; WHERE &lt;news_date&gt; &gt;= DATE_SUB(curdate(), INTERVAL 1 MONTH)
</font id=code></pre id=code>

This query (i know it works) will return the records from the last 1 month (taken from the current server date)

Then, on your archive page you can run the following query:

<pre id=code><font face=courier size=2 id=code>
select * FROM &lt;news_table&gt; WHERE &lt;news_date&gt; &lt; DATE_SUB(curdate(), INTERVAL 1 MONTH)
</font id=code></pre id=code>

And it will return everything before the current month.

Let me know if this helps you, i'm pretty sure it's right.

------------------------
Julio

PHP | MySQL | DWMX

ICQ: 19735247
MSN:

That sounds great - will that mean I won't need a trigger? As everytime the page is loaded the stored procedure will run anyway?

Thanks Poolio!


Tiree

Well, you see- this is not really a stored procedure. It will not update your records in any way. It's a select statement that will only choose the items from the last 30 days, leaving the rest out of the query.

So wherever you're calling the recordset to view the news items, use that query. If you're using a stored procedure to call the news items, then use the query as the procedure. You will then need to call a trigger to execute the procedure, but that's ASP land and i have no idea how it works.

What i've written is only an SQL statement.

------------------------
Julio

PHP | MySQL | DWMX

ICQ: 19735247
MSN:

Thats fine - I have an update and an insert 'thingy'*
so if I use your statement to select it will automatically display all the current ones. Lovely.

Thanks


*I don't do 'technical'!

Tiree.

I'm glad it helps. However i stil ldon't think there is any need to have an updatable 'status' field, as this really serves little purpose.

However you can always have a recurring script that will physically archive (using update) records older than 30 days from curdate() alongisde the query i wrote.

Good luck with it anyway, let me know if you need moe help.

------------------------
Julio

PHP | MySQL | DWMX

ICQ: 19735247
MSN:

Just tried to make this into a stored procedure and I got the error message:
"error 195: 'curdate' is not a recognised function"

Wow! We must have posted almost simultaneously!

I really thought it was going to work. I'm sure I'm doing something stupid.
Was I meant to substitue 'curdate' for anything?

Tiree,

curdate() is a MySQL function for the current date. You need so replace curdate() with whatever function SQL Server uses for that (i assume you're using SQL server?) if you're using Access you can use now() or date() i think.

basicaly you need to replace curdate() with whatever is going to give you today's daye- just make sure that <b>your news_date is in the same format (e.g. yyyy-mm-dd) as the current date function value!</b>

let me know if you need any more help at all.

------------------------
Julio

PHP | MySQL | DWMX

ICQ: 19735247
MSN: