1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to increase the post and comment date in a MYSQL database

Discussion in 'PHP & Perl' started by blackhit, Aug 9, 2009.

  1. blackhit

    blackhit Super Moderator Staff Member Jr. VIP Premium Member

    Joined:
    Jan 28, 2008
    Messages:
    2,402
    Likes Received:
    4,251
    Location:
    Dark Side Of The Moon
    This is what I want to do.

    I want the post-dates and the comment-dates in a couple of Wordpress blogs to increase with 1 day every time a cronjob is run.

    So when I post today it will show August 9 as the post date.
    But when I visit the blog tomorrow it will show the post date as August 10.

    So I need some lines of PHP script that I can run once a day with a cronjob that accesses the MYSQL database
    date fields and increases the value with 1.

    Any ideas on this?

    Thanx
     
  2. cyklotrial

    cyklotrial Regular Member

    Joined:
    Oct 13, 2008
    Messages:
    248
    Likes Received:
    82
    Location:
    Wonderland
    Try this:
    PHP:
    $sql_conn mysql_connect('serwer.com''admin''passwd');
    $sql "UPDATE `wp_posts` SET `post_date`=NOW(), `post_date_gmt`=NOW()";
    mysql_close($sql_conn);

    If you post today and run this code 20 August it will setup post date as August 20.
     
    • Thanks Thanks x 1
    Last edited: Aug 9, 2009
  3. blackhit

    blackhit Super Moderator Staff Member Jr. VIP Premium Member

    Joined:
    Jan 28, 2008
    Messages:
    2,402
    Likes Received:
    4,251
    Location:
    Dark Side Of The Moon
    Thanx.

    Is there a way to put a value like 1 or 2 instead of the NOW command?

    So when there's a post on
    Aug 1, Aug 3 and Aug 9

    after running the command with a value of +1 it will show
    Aug 2, Aug 4 and Aug 10.
     
  4. cyklotrial

    cyklotrial Regular Member

    Joined:
    Oct 13, 2008
    Messages:
    248
    Likes Received:
    82
    Location:
    Wonderland
    Try this one:
    PHP:
    <?php
    function change_date($date1)
        {
        
    $date2=strtotime($date1) + (60 60 24);
        return 
    date("Y-m-d H:i:s",$date2);
        }

    $dbuser="root";
    $dbpass="";
    $dbname="blog";
    $handle mysql_connect("localhost"$dbuser$dbpass
        or die(
    "Connection Failure to Database");
    //echo "Connected to database server<br>";
    mysql_select_db($dbname$handle) or die ($dbname " Database not found." $dbuser);
    //echo "Database " .  $database . " is selected<br><br>";



    $query "SELECT * FROM `wp_posts`";
    $run_query mysql_query($query);

    while (
    $row mysql_fetch_row($run_query))
        {
        
    $new_date1=change_date($row[2]);
        
    $new_date2=change_date($row[3]);
        
        
    $query "UPDATE wp_posts SET post_date='$new_date1', post_date_gmt='$new_date2' WHERE ID='$row[0]'";
        
        
    mysql_query($query )or die("Error");
        } 
    mysql_close($handle);
    ?>
    This code will add only 1 day to existing date.
     
    • Thanks Thanks x 1
    Last edited: Aug 9, 2009
  5. blackhit

    blackhit Super Moderator Staff Member Jr. VIP Premium Member

    Joined:
    Jan 28, 2008
    Messages:
    2,402
    Likes Received:
    4,251
    Location:
    Dark Side Of The Moon
    Great, I'm gonna try that...

    Thanks a lot!
     
  6. blackhit

    blackhit Super Moderator Staff Member Jr. VIP Premium Member

    Joined:
    Jan 28, 2008
    Messages:
    2,402
    Likes Received:
    4,251
    Location:
    Dark Side Of The Moon
    It works like a charm....

    Now one more question and then my setup will perfect.

    I tried to increase the dates of the comments as well by inserting this before mysql_close($handle);:

    Code:
    $query = "SELECT * FROM `wp_comments`";
    $run_query = mysql_query($query);
    
    while ($row = mysql_fetch_row($run_query))
        {
        $new_date1=change_date($row[7]);
        $new_date2=change_date($row[8]);
        
        $query = "UPDATE wp_comments SET comment_date='$new_date1', comment_date_gmt='$new_date2' WHERE comment_ID='$row[0]'";
        
        mysql_query($query )or die("Error");
        } 
    It increases the date once but on the second run it changes to Jan 1st 1970.

    Any idea on how to make this work for the comments as well?

    BTW, I'm going to share a useful tip for the bloggers here, for which I needed this code, and hope you don't mind me mentioning you and giving credits for the code.
     
  7. cyklotrial

    cyklotrial Regular Member

    Joined:
    Oct 13, 2008
    Messages:
    248
    Likes Received:
    82
    Location:
    Wonderland
    PHP:
    $query "SELECT * FROM `wp_comments`";
    $run_query mysql_query($query);

    while (
    $row mysql_fetch_row($run_query))
        {
        
    $new_date1=change_date($row[6]);
        
    $new_date2=change_date($row[7]);
        
        
    $query "UPDATE wp_comments SET comment_date='$new_date1', comment_date_gmt='$new_date2' WHERE comment_ID='$row[0]'";
        
        
    mysql_query($query )or die("Error");
        }
    :) In PHP first field in array has number 0 so:
    comment_date - $row[6]
    comment_date_gmt - $row[7]

    regards
    Lukas
     
    • Thanks Thanks x 1
  8. blackhit

    blackhit Super Moderator Staff Member Jr. VIP Premium Member

    Joined:
    Jan 28, 2008
    Messages:
    2,402
    Likes Received:
    4,251
    Location:
    Dark Side Of The Moon
    :eek: I should have looked better at the numbers for the posts while in PHPAdmin.

    I searched a lot for a code to do this and only thing I found where snippets but not the full picture.

    Thanx again, already REP given.