This seems to work usually and I am always super grateful for any help.
I need two queries to a standard WordPress 1.5.3 database for
1- list of the IDs and names of categories that have been posted to in the last X minutes
2- list of the IDs and names of authors that have posted in the last X minutes
Obviously, my lazy brain just can't wrap itself around the JOINs needed. The first one is more complicated as you need to go through the 'categories' table, the 'post2cat' table and the 'posts' table to check the 'post_date' and compare it to the current time - X minutes.
This is for Global Voices Online. I run a CRON to render JSS files every X minutes and no point in re-burning a file if it isn't updated right? ;)
Pretty please?
UPDATE
Guess i was just tired yesterday. Had some fun figuring out the author one myself this evening:
SELECT DISTINCT
U.ID AS id, CONCAT(U.user_firstname,' ',U.user_lastname) AS name
FROM
users AS U,
posts AS P
WHERE
user_level > 0
AND P.post_author = U.ID
AND P.post_modified >= '2006-08-25'
AND P.post_status = 'publish'
ORDER BY
U.ID
Now I do the categories one.
UPDATE 2 (10 minutes later)
Duh.
SELECT DISTINCT
C.cat_ID AS id, C.cat_name AS name
FROM
categories AS C,
post2cat AS PC,
posts AS P
WHERE
PC.category_id = C.cat_id
AND P.ID = PC.post_id
AND P.post_modified >= '2006-08-25'
AND P.post_status = 'publish'
ORDER BY
C.cat_ID
