Solved

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

Posted on 2010-11-22
7
341 Views
Last Modified: 2012-05-10
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
Comment
Question by:St_Aug_Beach_Bum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:ropenner
ID: 34195235
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
 

Expert Comment

by:Bulg
ID: 34195515
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
 

Author Comment

by:St_Aug_Beach_Bum
ID: 34199605
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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 8

Expert Comment

by:ropenner
ID: 34199922
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
 

Author Comment

by:St_Aug_Beach_Bum
ID: 34200128

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
 
LVL 8

Accepted Solution

by:
ropenner earned 500 total points
ID: 34200260
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
 

Author Closing Comment

by:St_Aug_Beach_Bum
ID: 34202469
Thank you, got it all rolling ;)
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question