Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Mysql script needed with while or for each to manipulate wordpress db

I have a program that is importing drafts and comments into a wordpress mysql database for me.

If I don't publish them within three days, I want the post and related comments automatically deleted.

So I need a script I can run once a day by cron.

I'm already connected to the database. So I need a script that will do something like this (not sure about the order of things).

-----------------------------------
database wp-ck
two tables involved: wp_posts and wp_comments
fields: wp_posts.ID, wp_posts.post_date, wp_posts.status, wp_comments.comment_post_ID


Look up wp_posts.ID of all posts WHERE wp_posts.post_date > three days old AND  wp_posts.status = draft

delete the matching row (getting rid of all posts in draft status that are over 3+ days old)

and

delete all rows in wp_comments WHERE wp_comments.comment_post_ID = wp_posts.ID

----------------------------------

Not sure how to write it... and if there are any wp gurus here, is there anything else I need to do for this to work?

Thanks, Chris

0
St_Aug_Beach_Bum
Asked:
St_Aug_Beach_Bum
  • 3
  • 3
1 Solution
 
ropennerCommented:
There may be more efficient ways of doing this but this way methodically cycles through each entry of more than 3 days old and deletes the corresponding entries.

Two ways of doing the date are given depending if you need it precisely to the hour minute second or not.

<?PHP
$database = ...... ; // you said you have this database connection made already
$three_days_variable =date("Y-m-d H:i:s", strtotime("-3 days"));  // depending on how precise the 3 days needs to be
//$three_days_variable =date("Y-m-d", strtotime("-3 days"));
$result = $database->query("SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.post_date < '$three_days_variable' AND  wp_posts.status = 'draft'");

while (list($id) = $result->fetch_row()) {
        $temp = $database->query("DELETE FROM wp_posts WHERE id=$id");
        $temp = $database->query("DELETE FROM wp_comments WHERE wp_comments_id=$id");
}
?>
0
 
BulgCommented:
or manually run a mysql select statement in your cpanel on the site and just del. those fields when you decide.

SELECT column-name
FROM table-name
WHERE specify what you are looking for (3-days old)

once this is performed and you get the fields that are old just del them

0
 
St_Aug_Beach_BumAuthor Commented:
ropenner,

Can't quite get this to work and I've been playing with it for an hour and just can't figure out what's wrong.

I've got this:

<?

//connect
mysql_connect("localhost","MyName","MyPass");

$database = "wp-ck" ; // you said you have this database connection made already
$three_days_variable =date("Y-m-d H:i:s", strtotime("-3 days"));  // depending on how precise the 3 days needs to be
//$three_days_variable =date("Y-m-d", strtotime("-3 days"));
$result = $database->query("SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.post_date < '$three_days_variable' AND  wp_posts.post_status = 'draft'");

while (list($id) = $result->fetch_row()) {
        $temp = $database->query("DELETE FROM wp_posts WHERE id=$id");
        $temp = $database->query("DELETE FROM wp_comments WHERE wp_comments_id=$id");
}
?>

When I run it, I get:

Call to a member function query() on a non-object in /home/my-site/runDraftCleanup.php on line 9

Bulg, not a cpanel site and would like to have it run on auto (cron)

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ropennerCommented:
the connect should look like:
      $database = mysql_connect('localhost', 'user','pass') || die (mysql_error());
        mysql_select_db("wp-ck",$database) or die( "Unable to select database");

Where user and pass are your personal ones.

change the query to something with error reporting too so you can see if the query works
       $result = $database->query("SELECT......") ||  die (mysql_error());

you may want to comment out the delete lines and print a message instead to test it.

example:

while (list($id) = $result->fetch_row()) {
//        $temp = $database->query("DELETE FROM wp_posts WHERE id=$id");
//        $temp = $database->query("DELETE FROM wp_comments WHERE wp_comments_id=$id");
print "about to delete $id<BR>\n";
}

and print out the query before running that as well so you can see if the date that is produced is the one you want.

So I'd recommend using this code for starters and then uncomment things if the output looks like you want it to.
 
$database = mysql_connect('localhost', 'user','pass') or die (mysql_error());
        mysql_select_db("wp-ck",$database) or die( "Unable to select database");

$three_days_variable =date("Y-m-d H:i:s", strtotime("-3 days"));  
print "$three_days_variable = 3 days ago<BR>";
// depending on how precise the 3 days needs to be
//$three_days_variable =date("Y-m-d", strtotime("-3 days"));
$result = $database->query("SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.post_date < '$three_days_variable' AND  wp_posts.post_status = 'draft'") or die(mysql_error());

while (list($id) = $result->fetch_row()) {
        //$temp = $database->query("DELETE FROM wp_posts WHERE id=$id");
        //$temp = $database->query("DELETE FROM wp_comments WHERE wp_comments_id=$id");
        print "about to delete $id<BR>\n";
}

Open in new window

0
 
St_Aug_Beach_BumAuthor Commented:

I'm running it via ssh right now, since I will be running it in cron later.  I get:

2010-11-20 15:12:39 = 3 days ago<BR>PHP Fatal error:  Call to a member function query() on a non-object in /home/community-knowledge/runDraftCleanup.php on line 9

Line 9 is:

$result = $database->que....


0
 
ropennerCommented:
my apologies, I've been mixing up the way I do queries.  My fault.

it should have been.
<?PHP
$database = mysql_connect('localhost', 'youruser','yourpassword') or die (mysql_error());
mysql_select_db("wp-ck",$database) or die( "Unable to select database");

$three_days_variable =date("Y-m-d H:i:s", strtotime("-3 days"));
print "$three_days_variable is three days ago\n";
$sql = "SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.post_date < '$three_days_variable' AND  wp_posts.post_status = 'draft'") or die(mysql_error());
print "$sql\n";
$result = mysql_query($sql, $database) or die(mysql_error());

while ($id = mysql_fetch_row($result)) {
        //$temp = $database->query("DELETE FROM wp_posts WHERE id=$id");
        //$temp = $database->query("DELETE FROM wp_comments WHERE wp_comments_id=$id");
        print "about to delete {$id[0]}<BR>\n";
}
?>

Open in new window

0
 
St_Aug_Beach_BumAuthor Commented:
Thank you, got it all rolling ;)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now