开发者

MySql - Select from - Don't Show Duplicate Words - maybe "on duplicate key"?

how can I insert "on duplicate key" in this Code to remove duplicate words? or is there a better method that you know? thank you!!

this is my code:

function sm_list_recent_searches($before = '', $after = '', $count = 20) {
// List the most recent successful searches.
    global $wpdb, $table_prefix;
    $count = intval($count);
    $results = $wpdb->get_results(
        "SELECT `terms`, `datetime`
        FROM `{$table_prefix}searchmeter_recent`
        WHERE 3 < `hits` AND CHAR_LENGTH(`开发者_运维技巧terms`) > 4
        ORDER BY `datetime` DESC
        LIMIT $count");
    if (count($results)) {

        foreach ($results as $result) {
            echo '<a href="'. get_settings('home') . '/search/' . urlencode($result->terms) . '">'. htmlspecialchars($result->terms) .'</a>'.", ";
        }

    }
}


ON DUPLICATE KEY UPDATE is used to cause an insert to update records if they already exist. It's not used for SELECT queries

What you want is to use the GROUP BY clause on your terms column to collapse it to unique values. You can use the MAX aggregate function to get the date of the latest record for those terms.

SELECT `terms`, MAX(`datetime`)
FROM `{$table_prefix}searchmeter_recent`
WHERE 3 < `hits` AND CHAR_LENGTH(`terms`) > 4
GROUP BY terms
ORDER BY `datetime` DESC
LIMIT $count

Make sure you have an index on the terms column, otherwise this will be a very expensive operation, especially if you're using it to make an autocomplete. You might even consider grouping your terms at insert time for best performance.


Have you looked into using the distinct keyword in your query? This shows what it does: http://newsourcemedia.com/blog/mysql-distinct-to-remove-duplicate-listing/

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜