MYSQL sort by date with maximum date

3 Views

Well I am working with wordpress and I’m trying to sort some posts by the date custom field that contains a date. Well i can sort the results by date, but I only want to show results that have a date until today. I don’t want to get results from a post that has a future date.

This is the code i’m using:

$querystr = "
            SELECT wposts.*
            FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
            WHERE wposts.ID = wpostmeta.post_id
            AND wpostmeta.meta_key = 'date'
            AND wposts.post_status = 'publish'
            AND wposts.post_type = games'
            ORDER BY STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') DESC
            ";

        $pageposts = $wpdb->get_results($querystr, OBJECT);
Post Views: 3

Related posts

Leave a Reply

4 comments

  2. Just add it to your where clause:

    SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
  AND wpostmeta.meta_key = 'date'
  AND wposts.post_status = 'publish'
  AND wposts.post_type = 'games'
  AND STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') < NOW()
ORDER BY STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') ASC

  3. Without seeing your row of data I would think something like this would do the trick:

    $today = "12/16/2011";

$querystr = "SELECT wposts.*
            FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
            WHERE wposts.ID = wpostmeta.post_id
            AND wpostmeta.meta_key = 'date'
            AND wposts.post_status = 'publish'
            AND wposts.post_type = 'games'
            STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') <= $today;
            ORDER BY STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') ASC";

    Where you are generating today’s date in PHP in the same format as %m/%d/%Y