$wpdb选择帖子的日期范围

0down VoteFavorite使用此SQL查询按用户帖子收到的投票数（元键）对用户进行排序。

<?php


                        global $wpdb;


                        $results = $wpdb->get_results ( "
                           SELECT u.display_name as name,
                            ( SELECT SUM(pm.meta_value)
                              FROM wp_posts p, wp_postmeta pm
                              WHERE p.post_author = u.ID
                              AND p.post_status LIKE \'publish\'
                              AND pm.meta_key = \'epicredvote\'
                              AND p.ID = pm.post_id ) as votes
                              FROM wp_users u
                              ORDER BY votes DESC LIMIT 20
                        " );

                        foreach ( $results as $result ) { 


                          echo "{$result->name}: {$result->votes} votes<br>";

                        }
                ?>

以前从未像这样在wordpress中使用过直接SQL查询，它总是wp循环或其变体。

无论如何，这将显示用户名+他们的分数（总计）。

我想做的是把这限制在一个帖子的日期范围内，比如说一周。

我认为可以使用strotime来实现这一点，但我不确定如何实现。

  $date1 = date(\'Y-m-d\', strtotime(\'+1 days\'));
    $date2 = date(\'Y-m-d\', strtotime(\'-8 days\'));

那么要在过去7天内收到帖子？在谷歌上搜索了一下，但没找到多少。

非常感谢您的帮助。

编辑：-由于某种原因无法工作的完整代码。

<?php


                        global $wpdb;

                         $date2 = date(\'Y-m-d\', strtotime(\'-60 days\'));

                         $weekly = $wpdb->get_results ( "
                      SELECT u.display_name as name,
                        ( SELECT
                          CONCAT_WS(\'/\',
                            SUM(CASE WHEN p.post_date < \'$date2\' THEN pm.meta_value ELSE 0 END),
                            SUM(pm.meta_value))
                          FROM wp_posts p, wp_postmeta pm
                          WHERE p.post_author = u.ID
                          AND p.post_status LIKE \'publish\'
                          AND pm.meta_key = \'epicredvote\'
                          AND p.ID = pm.post_id ) as votes
                          FROM wp_users u
                          ORDER BY votes DESC LIMIT 20
                    " );


                        foreach ($weekly as $result) {
    $name = $result->name;
    list( $weekly_vote, $total_vote ) = explode( \'/\', $result->votes ? $result->votes : \'0/0\' );
    // do stuff with $name, $weekly_vote, $total_vote ...eg
    echo  $name, \'<br>\', \' Weekly Vote: \', $weekly_vote,\'<br>\', \' Total Vote: \', $total_vote, \'<br>\', \'<br>\';
}
                    ?>

$results = $wpdb->get_results ( " SELECT u.display_name as name, ( SELECT CONCAT_WS(\'.\', SUM(CASE WHEN p.post_date < \'$date1\' AND p.post_date > \'$date2\' THEN pm.meta_value ELSE 0 END), SUM(pm.meta_value)) FROM wp_posts p, wp_postmeta pm WHERE p.post_author = u.ID AND p.post_status LIKE \'publish\' AND pm.meta_key = \'epicredvote\' AND p.ID = pm.post_id ) as votes FROM wp_users u ORDER BY votes+0 DESC LIMIT 20 " );

foreach ($results as $result) { $name = $result->name; list( $weekly_vote, $total_vote ) = explode( \'.\', $result->votes ? $result->votes : \'0.0\' ); // do stuff with $name, $weekly_vote, $total_vote ...eg echo \'Name: \', $name, \' Weekly Vote: \', $weekly_vote, \' Total Vote: \', $total_vote; }

Hook into $wpdb

有没有办法连接到WPDB实例（$WPDB）？我基本上想要的是在执行所需的查询get之前执行自己的函数。这可能吗？第一个答案的附加评论问题： $query = $wpdb->prepare(\"SELECT * FROM \" . $wpdb->prefix . \"test WHERE type = %s and ip = %s\", $type, $ip_full_log); $fetch = $wpdb->get_results($query, ARRAY_A)

相关推荐

Hook into $wpdb