Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I need to get a return from a query that provides me with email, username or nicename as well as post title based on author id.

The query is to run once daily via cron and look for posts that have spent a specific live time before they come up for removal (the query is intended to run five days and then three days before post deletion).

so far i have this which works (almost) via running manual queries in phpmyadmin.

SELECT ID, user_email, display_name, user_nicename
FROM wp_users
WHERE ID
IN (SELECT post_author FROM wp_posts WHERE post_type = 'sales' AND HOUR( TIMEDIFF( NOW( ) , post_date_gmt ) ) >=1)  

alls well and good here, it returns success however what i now need to get is also the post_titles of all the posts due for expiry so that it can be added inside the $message part of the wp_mail.

Im going to be honest with you i havent a clue about joining stuff. whether it needs left joins, outside joins or any other kind of join there maybe, the only info i seem to keep getting back from codex is this page

Update

<?php
$emailusers = $wpdb->get_results("SELECT ID, user_email FROM $wpdb->users WHERE ID IN (SELECT post_author FROM $wpdb->posts WHERE post_type = 'sales' AND HOUR( TIMEDIFF( NOW( ) , post_date_gmt ) ) >=1)");

foreach ($emailusers as $user) {
// do wp_mail stuff from here
}
?>

Full working code below for anyone who is interested:

<?php 
require_once 'wp-load.php';

global $wpdb;

$emailusers = $wpdb->get_results("SELECT u.user_nicename, u.user_email, p.post_title,  p.post_date
FROM $wpdb->posts p
INNER JOIN $wpdb->users u ON p.post_author = u.ID
WHERE post_type = 'sales' OR post_type = 'rentals' OR post_type = 'business'
AND HOUR( TIMEDIFF( NOW( ) , post_date_gmt ) ) >=721");


foreach ($emailusers as $user) {

$to = $user->user_email;

$subject = $user->post_title;

$message = 'You have a property listing that will auto expire in 3 days, the property is <strong>' . $user->post_title .'</strong> and was listed on <strong>' . $user->post_date .'</strong>' . "\r\n";


$headers .= "From: -------- <www.------------.com>" . "\r\n";


wp_mail( $user_email, $subject, $message, $headers);

}


?>  

The code up above will notify the author @ 720 hours that they have 3 days remaining before the post is auto deleted.

regards

share|improve this question
    
Any particular reason you need this done with a custom query? Why not just use WP functions? –  Rarst Jun 24 '11 at 8:44
    
Hi Rarst, i will be using WP functions the code i provided was the code i was running through phpmyadmin to ensure that it worked, i have updated my opening post with the crux of the code that i will be using for the final query –  MartinJJ Jun 24 '11 at 8:55
    
what I meant was - why not just use get_posts() and fetch user info and such? Anyway as long as it works for you. –  Rarst Jun 24 '11 at 9:23
    
@Rarst I originally thought that, but a custom SQL is a little less intensive - plus I'm not sure WP_Query has the capability to do the type of date searching that @Martin is trying to achieve. –  TheDeadMedic Jun 24 '11 at 9:31
    
@Rarst: i was under the impression from what i read about a wp theme for classifieds that there maybe was some difficulty in emailing authors prior to post deletion (hence this is why i looked into the wpdb query), i do know that post deletion using wp_cron can only be activated when the post is viewed in the browser after deletion time has passed and this is what i was trying to avoid, hence the emails for author notification that the post will be hard deleted on a specific date and at a specific time unless they renew said post, but i will certainly look into that for the next time.. regards –  MartinJJ Jun 24 '11 at 9:41
add comment

1 Answer

up vote 3 down vote accepted
$emailusers = $wpdb->get_results(
    "SELECT p.ID AS post_id, p.post-title, u.user_email " .
    "FROM $wpdb->posts AS p " .
    "INNER JOIN $wpdb->users AS u ON p.post_author = u.ID " .
    "WHERE p.post_type = 'sales' AND HOUR( TIMEDIFF( NOW( ) , p.post_date_gmt ) ) >=1)"
);

foreach ($emailusers as $user) {
    // do wp_mail stuff from here

    // $emailusers->post_title;
    // $emailusers->user_email;

    // used 'post_id' alias rather than 'ID' so it's clear it's not the author ID
    // $emailusers->post_id;
}
share|improve this answer
    
Brilliant... thanks, whats been confusing me mostly is these kind of things u.ID (notice the u, prefix) well i got what they were for about a minute before seeing you had posted u. for users table p. for posts table when using joins.. may i be so bold as to ask one more on-topic question to help me get this clearer whats the difference between join types?.. ie: INNER JOIN, LEFT JOIN etc –  MartinJJ Jun 24 '11 at 9:18
2  
INNER JOIN returns rows when there is at least one match in both tables, LEFT JOIN returns results so long as there's a match in the 'left' table (even if there aren't any in the 'right' table). RIGHT JOIN the same, in reverse. FULL JOIN returns rows if there is a match in either. –  TheDeadMedic Jun 24 '11 at 9:27
    
And yeah, just to clarify, the prefixes are table aliases, declared with AS right after the table name in the query. Just a byte-saver really, as opposed to having to use the full tablename when referencing fields :) –  TheDeadMedic Jun 24 '11 at 9:29
    
Thanks, favourited this one for future reference when i need to look up my JOINs the next time.. regards to both –  MartinJJ Jun 24 '11 at 9:31
    
I shouldn't take the credit really ;) –  TheDeadMedic Jun 24 '11 at 9:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.