?
Solved

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

Posted on 2010-11-22
7
Medium Priority
?
344 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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 2000 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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

770 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