SQL Query of news items

(Rhulse) #1

I was wondering what attributes you can get at via an SQL query?


At the moment we have a script that check for existing folder names under a particular folder. (To see if a group of stories has been published into folder)



I'd like to get it changed to determine if an existing Headline exists (or GUID SHA1 that we put in the phone field) and if the item was posted within the last 24 hours.



The aim will be to check location for an existing story headline less than 24 hours old and IF it exists update the asset with the new content and change the update time to the new update time. (It must use the headline and not the URL because duplicate titles are OK provided that are more than24 hours apart).



THis is so that stories can be updated by script and retain their URL.



Thought I'd ask here before putting a spec together.



cheers

Richard

(Greg Sherwood) #2

All news item fields are stored in the DB so we can extract any information required. The query would need to join 3 tables to get the attribute values (either headline or phone, whichever you choose) and an additional 2 tables if you need to limit by tree location. So it's not the smallest query, but it is possible to do.

(Rhulse) #3

Thanks Greg. I'll start on a spec and submit something next week.