bopuc/weblog

August 26, 2006

A little SQL help, again, please? ;D

Posted by bopuc at August 26, 2006 10:18 PM

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

Comments

Posted by: Adriaan at August 27, 2006 07:45 AM