Solved

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

Posted on 2010-11-22
7
334 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
  • 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
In order to have all security and back ups taken care of, WordPress users can sign up for services with WP Engine.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The viewer will learn how to dynamically set the form action using jQuery.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now