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
Solved

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

Posted on 2010-11-22
7
338 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Doing something wrong with this PDO Select Statement... 3 18
PHP and JQuery Syntax question 4 26
Wordpress Security 29 48
$_SERVER Variable question 31 26
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses four methods for overlaying images in a container on a web page
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

839 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