asuidhgpauieytpioawet

Ok so I’m trying to do a cus­tom SQL query on the wp_comments data­base table in order to create a list of com­men­ters who’ve either com­men­ted more than once using the same email address or who’ve com­men­ted in the about page, and who have at least one asso­cia­ted URL, pro­du­cing a kind of auto-generated “friends” list, with links, and with the most fre­quent com­men­ters at the top. You can see the really crappy unfor­mat­ted ver­sion I’ve mana­ged beneath the image map in on the right side of the footer.

So far this is what I’ve got:

This does almost pre­ci­sely what I want, but because GROUP BY collap­ses an entire group of rows with the same comment_author_email, lea­ving just the first row, the comment_author_url retrie­ved ends up being the URL the com­ment author ente­red in their first com­ment, not their last, because the whole table by default is sor­ted by comment_ID ascen­ding. Adding comment_ID DESC to the ORDER BY para­me­ter just sorts after everything’s already been grouped/collapsed.

Point is I am not nearly fami­liar enough with SQL to figure this out yet. I think I need to do a sub­query or a self-join, but as far as I can tell all the methods I’ve read for doing that are in SQL syn­tax, not MySQL syn­tax, which see­mingly is sig­ni­fi­cantly dif­fe­rent? I’ll pro­bably even need to do some more com­plex stuff to account for, say, peo­ple who com­ment using mul­ti­ple email addres­ses and mul­ti­ple URLs, but who are iden­ti­fiably the same per­son through over­lap in both.

In the pro­cess, any­way, I think I’ve fami­lia­ri­zed myself enough with $wpdb, and re-familiarized myself enough with SQL, that doing a “recent com­ments” sec­tion won’t be dif­fi­cult at all. They have plu­gins that do that but I hate rel­ying on things whose deve­lop­ment could stop at any moment, I’ve seen too many dead plu­gins ren­de­red incom­pa­ti­ble with recent Word­Press ver­sions to ever want to use them.