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

blackhit

Elite Member
Joined
Jan 28, 2008
Messages
2,352
Reaction score
4,357
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
 
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.
 
Last edited:
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.
 
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.
blackhit said:
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.
 
Last edited:
Great, I'm gonna try that...

Thanks a lot!
 
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.
 
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
 
:o 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.
 
Back
Top