CODEWITHSUNDEEP
mysqlwordpress
Jeffrey Nerona
Jeffrey Nerona

Reputation: 135

mysql additional condition after INNER JOIN, ON

I have this query right now

global $wpdb;
$interval = "1 WEEK";
$now = current_time('mysql');
$top4=$wpdb->get_results('SELECT ID, post_title, post_name from `'.$wpdb->prefix.'popularpostssummary`       
INNER JOIN `'.$wpdb->prefix.'posts` ON `postid`=`ID` 
ORDER BY `pageviews` DESC 
LIMIT 4;', ARRAY_A);

And I want to add the following conditions, what's the proper way of adding them in the code?

WHERE post_type = post
AND last_viewed > DATE_SUB('{$now}', INTERVAL {$interval})

post_type is under '.$wpdb->prefix.'posts while last_viewed is under '.$wpdb->prefix.'popularpostssummary

Upvotes: 0

Views: 102

Answers (1)

J.D. Pace
J.D. Pace

Reputation: 616

global $wpdb;
$interval = "1 WEEK";
$now = current_time('mysql');
$sql = 
    'SELECT p.ID, p.post_title, p.post_name
     FROM `' . $wpdb->prefix . 'popularpostssummary` AS pps
         INNER JOIN `' . $wpdb->prefix . 'posts` AS p ON pps.`postid`= p.`ID`
     WHERE p.post_type = "post"
         AND pps.last_viewed > DATE_SUB("' . $now . '", INTERVAL ' . $interval . ')
     ORDER BY pps.`pageviews` DESC 
     LIMIT 4;';
echo $sql; exit;
$top4 = $wpdb->get_results( $sql, ARRAY_A );

Upvotes: 1

Related Questions