Results 1 to 6 of 6

Thread: Bug in Ad pruning SQL in CP 3.1.6

  1. January 11th, 2012 #1
    mattbye
    mattbye is offline
    Thread Starter
    Junior Member mattbye's Avatar
    Join Date
    Mar 2011
    Location
    California
    Posts
    38
    Thanks
    5
    Thanked 6 Times in 5 Posts

    Bug in Ad pruning SQL in CP 3.1.6

    I believe there is a bug in the SQL statement to select ID's of posts to prune in CP 3.1.6. The existing SQL from theme-cron.php is:

    Code:
    SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE wp_postmeta.meta_key = 'cp_sys_expire_date' AND timediff(STR_TO_DATE(wp_postmeta.meta_value, '%m/%d/%Y %H:%i:%s'), now()) <= 0 AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'ad_listing';
    the problem is the timediff(STR_TO_DATE(wp_postmeta.meta_value, '%m/%d/%Y %H:%i:%s'), now()) <= 0 part. timediff() returns a value in time format, like '-04:05:23' and this value with colons, when compared to integer 0 produces an error.

    I corrected this using a straight ahead magnitude comparison between the expire data and now(). Here is my modified sql for pruning ads:

    Code:
    $qryToString = "SELECT $wpdb->posts.ID FROM $wpdb->posts
        LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id
        WHERE $wpdb->postmeta.meta_key = 'cp_sys_expire_date'
        AND STR_TO_DATE($wpdb->postmeta.meta_value, '%m/%d/%Y %H:%i:%s') <= now()
        AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = '".APP_POST_TYPE."'";
    This code works for me. Do you see any downsides to this modification?
    Reply With Quote Reply With Quote
  2. January 12th, 2012 #2
    Shannon
    Shannon is offline
    Shannon's Avatar
    Join Date
    Mar 2011
    Location
    United States
    Posts
    577
    Thanks
    49
    Thanked 248 Times in 114 Posts
    You must be an AppThemes customer and logged in to view this response. Join today!
    Reply With Quote Reply With Quote
  3. June 2nd, 2013 #3
    jizzy
    jizzy is offline
    jizzy's Avatar
    Join Date
    Jan 2013
    Location
    Italy
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You must be an AppThemes customer and logged in to view this response. Join today!
    Reply With Quote Reply With Quote
  4. June 2nd, 2013 #4
    jizzy
    jizzy is offline
    jizzy's Avatar
    Join Date
    Jan 2013
    Location
    Italy
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You must be an AppThemes customer and logged in to view this response. Join today!
    Reply With Quote Reply With Quote
  5. September 12th, 2013 #5
    gustavorviegas
    gustavorviegas is offline
    Expired Customer gustavorviegas's Avatar
    Join Date
    Aug 2013
    Location
    Brazil
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You must be an AppThemes customer and logged in to view this response. Join today!
    Reply With Quote Reply With Quote
  6. September 12th, 2013 #6
    seoagent
    seoagent is offline
    seoagent's Avatar
    Join Date
    Jul 2012
    Location
    Edmonton, AB, Canada
    Posts
    660
    Thanks
    136
    Thanked 86 Times in 74 Posts
    You must be an AppThemes customer and logged in to view this response. Join today!
    When in doubt: Read, Ask, Do. G+
    Reply With Quote Reply With Quote
« Content disappear | updated to CP 3.6 now this error Warning: Missing argument 2 for wpdb::prepare(), ca »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need SQL query for pruning old ads
    By muhabat in forum Report ClassiPress Bugs
    Replies: 5
    Last Post: August 8th, 2011, 08:23 AM
  2. Ad Pruning Question
    By jschodde in forum ClassiPress General Discussion
    Replies: 0
    Last Post: July 18th, 2009, 03:13 PM